Oracle database index types
What are the different types of indexes in Oracle databases? Please give examples for all indexes.
There are several types of indexes available in Oracle all designed for different circumstances:
- b*tree indexes - the most common type (especially in OLTP environments) and the default type
- b*tree cluster indexes - for clusters
- hash cluster indexes - for hash clusters
- reverse key indexes - useful in Oracle Real Application Cluster (RAC) applications
- bitmap indexes - common in datawarehouse applications
- partitioned indexes - also useful for datawarehouse applications
- function-based indexes
- index organised tables
- domain indexes
Let's look at these Oracle index types in a little more detail.
B*tree stands for balanced tree. This means that the height of the index is the same for all values thereby ensuring that retrieving the data for any one value takes approximately the same amount of time as for any other value. Oracle b*tree indexes are best used when each value has a high cardinality (low number of occurrences)for example primary key indexes or unique indexes. One important point to note is that NULL values are not indexed. They are the most common type of index in OLTP systems.
B*Tree Cluster Indexes
These are B*tree index defined for clusters. Clusters are two or more tables with one or more common columns and are usually accessed together (via a join). CREATE INDEX product_orders_ix ON CLUSTER product_orders;
Hash Cluster Indexes
In a hash cluster rows that have the same hash key value (generated by a hash function) are stored together in the Oracle database. Hash clusters are equivalent to indexed clusters, except the index key is replaced with a hash function. This also means that here is no separate index as the hash is the index. CREATE CLUSTER emp_dept_cluster (dept_id NUMBER) HASHKEYS 50;
Reverse Key Indexes
These are typically used in Oracle Real Application Cluster (RAC) applications. In this type of index the bytes of each of the indexed columns are reversed (but the column order is maintained). This is useful when new data is always inserted at one end of the index as occurs when using a sequence as it ensures new index values are created evenly across the leaf blocks preventing the index from becoming unbalanced which may in turn affect performance.CREATE INDEX emp_ix ON emp(emp_id) REVERSE;
These are commonly used in datawarehouse applications for tables
with no updates and whose columns have low cardinality (few distinct values). In this type of index, Oracle stores a bitmap for each distinct value in the index with 1 bit for each row in the table. These bitmaps are expensive to maintain and are therefore not suitable for applications which make a lot of writes to the data as for example an OLTP system would.
As an example, consider a car manufacturer which records information about cars sold including the colour of each car. Each particular colour (eg. "Artic White") is likely to occur many times in the SALES table and would be therefore suitable for a bitmap index.CREATE BITMAP INDEX car_col ON cars(colour) REVERSE;
Partitioned Indexes are also useful in Oracle datawarehouse applications where there is a large amount of data that is partitioned by a particular dimension such as time.
Partition indexes can either be created as local partitioned indexes or global partitioned indexes. Local partitioned indexes means that the index is partitioned on the same columns and with the same number of partitions as the table. For global partitioned indexes the partitioning is user defined and is not the same as the underlying table.
Refer to the create index statement in the Oracle SQL language reference
Oracle Function-based Indexes
As the name suggests these are indexes created on the result of a function modifying a column value. For exampleCREATE INDEX upp_ename ON emp(UPPER(ename));
The function must be deterministic (always return the same value for the same inputs).
Index Organized Tables
In an index-organized table all the data is stored in the Oracle database in a B*tree index structure defined on the table's primary key. This is ideal when related pieces of data must be stored together or data must be physically stored in a specific order. Index-organized tables are often used for information retrieval, spatial and OLAP applications.
Oracle Domain Indexes
These indexes are created by user-defined indexing routines and enable the user to define his or her own indexes on custom data types (domains) such as pictures, maps or fingerprints for example. These type of index require in-depth knowledge about the data and how it will be accessed.
For more help with SQL see our Oracle SQL tutorials
and the Oracle Database Concepts
manual has a good overview of indexes.
If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training
page for more information.