In PL/SQL, indexes are used to improve the performance of queries by reducing the number of rows that need to be scanned when retrieving data. The Indexes work like pointers to rows in the table and they can be created on one or more columns of a table to facilitate faster searching.
In this article, We will learn about CREATE INDEX command in PL/SQL explaining its purpose, usage, types and examples to help optimize query performance.
PL/SQL Create Index
- In PL/SQL, an index is a database object that improves the performance of query execution by allowing faster retrieval of records from a table.
- Indexes are particularly useful when dealing with large datasets, as they help the database locate rows without needing to scan the entire table.
Why Use Indexes?
- Faster retrieval: They help retrieve data faster by narrowing down the rows to be scanned.
- Performance improvement: It is useful in the SELECT, JOIN, WHERE and ORDER BY operations.
Indexes are not automatically created on tables they need to be created explicitly for the optimal query performance.
Syntax:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
Explanation:
index_name: The name you want to give to the index.table_name: The name of the table on which the index is being created.column1, column2, ...: The column(s) of the table that you want to index
Important Points
- We can create an index on a single column or multiple columns.
- Once created an index is automatically used by the database engine when optimizing queries.
- Indexes do not change the data in the table but make searching faster.
Types of Indexes
There are different types of the indexes that can be created in the PL/SQL:
- B-tree Index : The default and most commonly used index type. Efficient for the equality and range queries.
- Unique Index: Ensures that the values in the indexed columns are unique.
- Composite Index: An index on the multiple columns.
- Bitmap Index: Used in the environments with the low cardinality.
- Function-based Index: The Index based on the expressions or functions applied to the columns.
- Clustered Index: Typically created on primary keys and sorts the table data to the match the index.
Creating an Index in PL/SQL
Let’s assume we have a table Employees with the columns EmployeeID, FirstName, LastName and DepartmentID. We want to the create an index on the LastName column to the improve search performance when querying based on the last name.
Create the Employees Table
First, create the Employees table with the columns EmployeeID, FirstName, LastName and DepartmentID.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT
);
Insert Sample Data
Now, insert some sample data into the Employees table.
INSERT INTO Employees (FirstName, LastName, DepartmentID)
VALUES
('John', 'Smith', 101),
('Jane', 'Doe', 102),
('Alice', 'Johnson', 103),
('Bob', 'Smith', 104),
('Charlie', 'Brown', 105);
Employees Table:
EmployeeID | FirstName | LastName | DepartmentID |
|---|
1 | John | Smith | 101 |
|---|
2 | Jane | Doe | 102 |
|---|
3 | Alice | Johnson | 103 |
|---|
4 | Bob | Smith | 104 |
|---|
5 | Charlie | Brown | 105 |
|---|
Let's Create an Index on LastName
Now, create an index on the LastName column to the improve search performance when querying by the last name.
CREATE INDEX idx_lastname
ON Employees (LastName);
This creates an index called idx_lastname on the LastName column of the Employees table.
Query Using the Index
We can now run queries that filter by the LastName. The index will improve performance for the queries like this one:
SELECT * FROM Employees
WHERE LastName = 'Smith';
The above query will utilize the idx_lastname index to the quickly find employees with the last name "Smith".
Creating Unique Indexes
A unique index ensures that no two rows have the same value for the indexed column(s). The Unique indexes are often used to enforce the uniqueness of a column such as in the case of the PRIMARY KEY.
Syntax for Creating a Unique Index:
CREATE UNIQUE INDEX idx_unique_employeeid
ON Employees (EmployeeID);
This command ensures that the EmployeeID in the Employees table is unique across the all rows.
Using Composite Indexes
A composite index is created on the multiple columns. It is particularly useful when queries involve filtering based on the multiple columns.
Syntax for Creating a Composite Index:
CREATE INDEX idx_composite
ON Employees (LastName, DepartmentID);
This index helps in queries that filter by both the LastName and DepartmentID.
Example:
SELECT * FROM Employees8
WHERE LastName = 'Smith' AND DepartmentID = 101;
In this case, the composite index idx_composite will be used to the optimize the query.
Dropping an Index
If an index is no longer needed it can be dropped using DROP INDEX command.
Syntax:
DROP INDEX index_name;
Example:
DROP INDEX idx_lastname ON Employees;
This will remove the index idx_lastname from the Employees table.
Advantages of Using Indexes
- Improved Query Performance: The Indexes significantly speed up query execution by the reducing the number of rows scanned.
- Reduced Disk I/O: It Helps in reducing disk I/O especially in the large datasets.
- Efficient Data Access: The Indexes are particularly useful in the queries involving the WHERE clauses and JOIN operations.
Disadvantages of Using Indexes
- Additional Storage: The Indexes take up extra storage space in the database.
- Slower Data Modification: It Inserts, updates and deletes can be slower because the index must also be modified.
- Complexity: The Managing too many indexes can add complexity to the database management and maintenance.
Conclusion
The Indexes play a crucial role in optimizing query performance in the PL/SQL. By creating and managing indexes appropriately we can significantly improve the efficiency of the database operations. However, it's essential to the use indexes judiciously as they come with their own overheads like storage space and maintenance costs. Understanding the types of indexes and when to use them will help we make informed decisions when designing the database.
Similar Reads
PostgreSQL - CREATE INDEX
The PostgreSQL CREATE INDEX statement is essential for improving database performance, allowing faster data retrieval by creating indexes on specified columns. Indexes in PostgreSQL act like pointers, significantly reducing the time required for query processing, especially on large tables. In this
5 min read
PL/SQL Drop Index
In PL/SQL an index is a database object that improves the speed of the data retrieval operations on the table. However, there are situations where we might want to remove an index either to optimize performance or to retrieve disk space. In this article, we will provide a comprehensive guide on how
4 min read
Index in PL/SQL
PL/SQL, Oracle's extension to SQL, combines SQL with procedural programming features like loops, conditionals, and exception handling. It enables developers to create stored procedures, functions, triggers, and other database applications. As a block-structured language, PL/SQL allows seamless integ
5 min read
SQL CREATE INDEX Statement
In SQL, indexes are important for optimizing query performance by speeding up data retrieval operations. The CREATE INDEX statement is used to create indexes in tables, enabling quicker searches and improving database efficiency. In this article, we will explain how to use the SQL CREATE INDEX State
5 min read
PL/SQL CREATE VIEW
PL/SQL CREATE VIEW is a statement used to create a virtual table based on the result of a query. Views in PL/SQL allow users to access and manipulate data stored in one or more underlying tables as if it were a single table. In this article, We will learn about the PL/SQL CREATE VIEW by understandin
3 min read
SQL CREATE DATABASE
Creating a database is one of the fundamental tasks in SQL and is the first step in structuring your data for efficient management. Whether you're a developer or a database administrator, understanding the CREATE DATABASE statement is essential. Understanding how to use this command effectively is c
6 min read
PL/SQL Insert DateTimes
In PL/SQL, handling date and time values is a common requirement, whether we are working with databases that store transaction times, schedule events, or track user activities. Understanding how to insert these values correctly is crucial for accurate data management and retrieval. In this article,
4 min read
PL/SQL INSERT INTO
PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It allows us to write complex queries and scripts that include procedural logic, control structures, and error handling. The INSERT INTO statement in PL/SQL is essential for adding new rows of data to tab
6 min read
PL/SQL Show Indexes
Indexes are crucial in database management for speeding up data retrieval by creating quick access paths to the data. In PL/SQL, indexes can significantly enhance query performance by avoiding full table scans, especially on columns used in search conditions or joins. In this article, we will PL/SQL
7 min read
PL/SQL Unique Index
A PL/SQL Unique Index is a powerful database object used to ensure the uniqueness of values in one or more columns of a table. In Oracle databases, indexes play an important role in enhancing query performance by enabling quick retrieval of rows. The unique index specifically enforces a rule that no
6 min read