Oracle Indexes

Indexes are optional data structures associated with Database tables. Indexes are logically & physically independent of the data in the associated table. They are independent database objects created to reduce disk I/O & speed up execution of SQL statements on a table. We can create many indexes on a table given that each index points to different column (in case of a single column) or unique combination of columns from a table.

All Indexes offer complementary performance functionalities based on their structure:

B-Tree indexes : B-Tree Indexes are stored as a conventional binary data structure in the database. Each branch node contains a key prefix to make a direction between two keys. Each Leaf Node is at same depth from the branch node and contain complete key value for each row & ROWID.

Features :

• All leaf blocks of the tree are at same depth, resulting same performance for retrieval of any record from the indexed table.
• B-Trees are stay balanced automatically.
• Excellent performance is achieved for wide range of queries and are generally suitable for both small & large sized tables without performance degradation.

Syntax :

CREATE INDEX last_name_idx ON students (last_name);

Bitmap indexes : Bitmap Indexes are space efficient indexes that represent trade-off between Disk I/O and CPU usage. A bitmap value is stored for each key value instead of a list of ROWIDs. Each bitmap corresponds to a possible ROWID. A mapping function returns actual ROWID at runtime in the form of a Row-ID list, and these ROWID values are directly used to access the data row. So, there is a trade-off between space (using compressed indexes) and CPU usage (for processing decompression).

Features :

• Bitmap indexes are generally used in data warehouses for executing bulk data operations.
• They are space efficient, but need higher CPU usage in comparison to B-Tree Indexes.
• Bitmap indexes are not suitable for OLTP applications with large number of concurrent operations modifying individual rows of data. They are efficient in querying large data.
• Bitmap indexes are not useful when performing comparison operations, instead use them for logical operations on data (viz. AND, OR, NOT) or equality queries.

Syntax :

CREATE BITMAP INDEX last_name_idx ON students (last_name);

Bitmap Join indexes : As the name suggests, Bitmap join indexes are bitmap indexes defined using an equi-join condition between two or more tables. In bitmap-join index, indexed values comes from one table (Fact table) but bitmaps point to another table (Dimension table). Usually implemented in a data warehousing environment, they have similar features to bitmap indexes. Additionally, following considerations must be taken into account :

• Bitmap-join indexes take much more time to build than a conventional bitmap index for a join is performed and bitmap value is generated.
• Bitmap-join indexes should be applied on tables frequently used in tandem (using joins) with each other.

Syntax :

CREATE BITMAP INDEX last_name_idx ON students (math.marks) WHERE students.pk = math.fk;

Index-Organized Tables : An index organized table has a storage organization that is different from a conventional (heap-organized) table whose data is stored as an unordered collection. Data for an index organized table is stored in a B-Tree index structure in a primary key sorted manner. Along with the primary key column data, it stores the values for the non-key columns as well.

Features :

• Access to rows is defined using logical ROWID.
• Full-Index returns all rows.
• Avoid additional block fetch for accessing non-key columns’ data.

Syntax :

CREATE TABLE students (
rollno number,
first_name varchar2(20),
last_name varchar2(20),
CONSTRAINT pk_stud_iot_index PRIMARY KEY (rollno) )
ORGANIZATION index
TABLESPACE spc_example_ts_01
PCTHRESHOLD 20 INCLUDING last_name;

Function based indexes : Function based indexes are based on functions, expressions that involve one or more columns in the table being indexed. A value of the function/expression is calculated and stored as the value for the index. They can be stored either as a B-Tree index or a bitmap index.

Features :

• Function based indexes are beneficial for evaluating queries that involve functions in WHERE clauses. Example :

SELECT * FROM students WHERE substr(stud_name, 1, 5) = "PETER";

• It’s easy & provides immediate values for expressions.
• It provides additional functionality with little cost, if expensive functions/expressions need to be evaluated regularly from the indexed table.

Syntax :

CREATE INDEX fb_last_name_idx on students (UPPER(last_name));