Indexes – Oracle PL/SQL Tutorial

An index is a database structure that can be used to improve the performance of SQL queries. An PL/SQL index is a schema object that has the role to provide direct and fast access without reading the entire table.

Indexes can be created on one or more columns in a table, and they can be used to retrieve data from the database more quickly. Indexes are created explicitly or automatically.

Syntax

To create an index, the syntax is:

CREATE INDEX index_name 
ON table_name (column1, column2, ...);

When using PL/SQL indexes you must consider the following tips:

Create indexes after inserting table data and index on the correct tables / columns, that are most used in queries.
Order index columns for performance and limit the number of indexes for each table.
Assign index size and set storage parameters.
Indexes are created in the default tablespace of the schema, you can specify the tablespace for each index.
Drop indexes that are no longer useful or no longer needed.

Types of indexes

There are several different types of indexes that can be created in Oracle PL/SQL, including:

– B-tree indexes
– Bitmap indexes
– Partitioned indexes
– Function-based indexes
– Domain indexes

B-tree indexes

B-tree indexes – By default, Oracle Database creates B-tree indexes(normal indexes).

CREATE INDEX index_name
ON table_name(column_name);

Bitmap indexes

The Bitmap indexes store the rowids column value in bits.

CREATE BITMAP INDEX bitmap_index_name 
   ON table_name(column_name)
   TABLESPACE tbs_1
   LOCAL(PARTITION ix_p1 TABLESPACE tbs_02,
         PARTITION ix_p2,
         PARTITION ix_p3 TABLESPACE tbs_03);

Partitioned indexes

Partitioned indexes are partitions that store an entry for each value that appears in the indexed column of the table.

CREATE INDEX index_name ON table_name (column_name)
   GLOBAL PARTITION BY RANGE (column_name)
      (PARTITION p1 VALUES LESS THAN (100),
       PARTITION p2 VALUES LESS THAN (200),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

Function-based indexes

Function-based indexes are based on expressions. You can build queries that evaluate the value returned by an expression.

CREATE INDEX index_name
ON table_name(UPPER(column_name));

Domain indexes

A domain index is an instance of an index which is accessed by routines supplied by an indextype.

Benefits to use indexes

Oracle PL/SQL indexes provide several benefits that contribute to improved performance and efficiency in database operations. Here are some key advantages of using indexes in Oracle PL/SQL:

Faster Data Retrieval: Indexes allow for faster data retrieval by providing a quick path to locate specific rows in a table. When a query includes a condition in the WHERE clause that matches an indexed column, Oracle can use the index to locate the relevant rows more efficiently, reducing the need for a full table scan.

Improved Query Performance: Indexes significantly enhance the performance of SELECT queries, as they enable the database engine to locate and retrieve specific rows without scanning the entire table. This is particularly beneficial when dealing with large datasets, as it minimizes the amount of data that needs to be processed.

Enhanced Joins: In cases where multiple tables are joined in a query, indexes on the join columns can improve the performance of join operations. Indexes help Oracle efficiently locate and match rows from different tables, reducing the overall execution time of the query.

Constraint Enforcement: Indexes can be used to enforce uniqueness and primary key constraints. This ensures that the database maintains data integrity by preventing the insertion of duplicate or null values into columns that have these constraints.

Reduced I/O Operations: Well-designed indexes can minimize the number of I/O operations required to satisfy a query. This is achieved by allowing Oracle to quickly navigate through the index structure to locate the desired rows, reducing the amount of data that needs to be read from disk.

Space Efficiency: Indexes can also contribute to saving storage space by providing a more compact structure for certain types of data, such as B-tree indexes. This can be beneficial in terms of both storage costs and cache utilization.

Optimized Range Queries: Range queries, which involve a range of values, can benefit significantly from indexes. Oracle can efficiently identify and retrieve the relevant rows using the index structure, leading to improved performance for queries with range conditions.

In summary, Oracle PL/SQL indexes play a crucial role in optimizing database performance by facilitating faster data retrieval, improving query execution, and enhancing the overall efficiency of data manipulation operations. However, it’s essential to carefully design and maintain indexes to ensure they align with the specific needs and characteristics of the database and the queries being executed.