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.