Tuning Oracle with Indexes

Logical Indexes

Logical indexes cater for logical structure, specifically, referential integrity. Referential integrity implies use of primary and foreign keys (indexes) in order to validate relationships between related entities. Note that foreign keys are generally never used in SQL statements. Foreign key columns are generally parts of composite keys in sub-relations. The composite keys are used in SQL query statements. Thus foreign keys are generally wasted indexes for anything but relational set qualitative testing. Also note that if foreign keys are used then these foreign keys must have indexes created specifically for them. Oracle creates indexes for primary keys in order to check uniqueness. Oracle does not create indexes for foreign keys. If foreign keys are used then indexes must be created specifically for those foreign keys otherwise excessive table-scanning will result. There is much to be said for not using foreign keys and primary keys for the purposes of validating referential integrity in Oracle. And never use triggers. Oracle triggers are not rules but are really for the purposes of messaging as a result of database events. It is best to place referential integrity at the application level, Oracle simply is not very good at dealing with validation of referential integrity. And note that a composite key in a subset relation is not a foreign key, a foreign key is a single column and refers to the single column in the parent table. A composite key in a subset relation allows access to the subset relation in subset primary index order within the order of the primary key of the parent relation, ie. the foreign key. Makes sense doesn't it ? Simply put, avoid use of foreign keys in Oracle, it is one of those things which is there simply because other databases have extensive capabilities in this direction; it is called marketing ! Or even more simply put, a foreign key is completely different to a composite key.

What and How to Index

Indexes can be column and functionally based. Place indexes on columns and composites of columns used frequently in where clauses or in joins. The more unique the value of a column is in a table then the better it will perform as an index. Obviously this is because few values are more effectively found by searching through a smaller index segment. Searching for a column value which is duplicated is simply finding things which are the same, not much point. Use compression of parent columns in composite indexes thus removing duplicated parent set relationship composite index values. Bitmap indexes should be used when indexing very low cardinality column values.

Columns which are altered often are not effective as indexes. In general if entity-relational structure is correct there should be few indexes placed onto non-primary index comlum composites or single columns.

Functions placed in where clauses negate the use of non-function-based indexes entirely thus causing overhead producing table-scans.

Put indexes onto foreign key definitions if foreign keys are created. This will minimise share locking on the parent table and table-scanning on the child table.

Note that every index created on a table increases overhead for insert, update and delete activitiy. If a table has three indexes then every database changing DML statement results in four DML statements.

The order of and index must be matched by the sequence of checks in a join or where clause. If this is not the case then the index will be completely ignored and a table-scan will result.

Regeneration and Coalescing of Indexes

Indexes subjected to constant DML update activity can become skewed and thus less efficient. Oracle standard indexes use a binary tree type structure. This means that when a value is searched for within the tree a series of comparisions are made in order to depth-first traverse down through the tree until the appropriate value is found. Constant updating of the binary tree can cause the tree to become more heavily loaded in some parts. Thus some parts of the tree require more intensive searching which can be largely fruitless. Thus indexes can be rebuilt where the binary tree can be regenerated from scratch. This is a function which should performed periodically in order to retain efficient indexes. Coalescing of indexes is a more physical form of maintenance where physical space chunks are defragmented.


Also use index compression when building indexes with the create index command where repeated values in composite indexes are not duplicated thus saving storage space and increasing access speed.

Forcing Usage of and non-Usage of Indexes

Force usage of indexes by matching where clause ordering sequences to that of the sequences of indexes. Use of EXPLAIN PLAN can help to determine optimiser indexing usage choices. Obviously the simplest way to avoid index usage is to simple use a poorly organised where clause. Also the FULL hint can force the optimiser to do a full table-scan regardless of available indexes. Use the INDEX, INDEX_COMBINE and the AND_EQUAL hints which force optimisation using specified indexes.

Types of Indexes

Standard BTree Indexes

Btree indexes are the default indexes in Oracle. A BTree index is a binary tree. A binary tree is very efficient for searching through based on range scans and is loses much less in efficiency as a result of data changes in comparison to other types of indexes.

Bitmap Indexes

A bitmap stores the location of data rows based sequences of 0's and 1's. As a result a bitmap index requires much less storage than a BTree. However, bitmap indexes become innefficient as a result of data row chanages. Bitmap indexes are applicable to read-only type environments.

Composite bitmap indexes can be created. However, separate bitmap indexes can be created on each of the separate columns in a composite key thus giving flexibility for use in ADHOC SQL environments. Note that bitmaps are most useful for low cardinality (values mostly the same) columns.

Once again note that bitmap indexes are only really applicable to read-only environments. For constantly changing data use compressed binary indexes to save storage space. Of course indexes can always be regenerated but typically read-only environments involve very large tables thus index regeneration can be very time consuming. Bitmap indexes are absolutely not appropriate for OLTP type applications and objects.

On the contrary bitmap indexes are appropriate for tables with bulk inserts and updates such as SQL*Loader import and post-import processing tables. This is because index entries are batch deferred until completion of DML statements. Appropriate settings of the parameters SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE are important.

Function-Based Indexes

A function based index is a index created containing values which are the results of an expression applied to each row of a table. Use function based indexes whenever possible. Placement of functions into SQL statements, such as in where clauses, will cause a full table scan regardless.

CREATE INDEX index1 on table1 (column1 * column2);

The following statement would access the table using the index thus allowing exact block hits and not causing full table scans.

SELECT * FROM table1 WHERE (column1 * column2) > 100;

Note that the QUERY_REWRITE_ENABLED = TRUE session parameter must be set to enable the use of function based indexes in relation to matching index values against column based expressions.

Domain Indexes

A domain index maps to a user defined indextype and is utilised with the Oracle objects extension.

Clusters and Index Organised Tables

In short clusters and index organised tables are intended for more or less read-only, static type data tables where index and data spaces are stored together physically, ie. data row values are stored within the index binary tree of the index or the data row values are actually stored into an index type structure. Data access is very fast as long as the data is not changed. Changes result is serious amounts of overflow. Overflow is basically additional data which is stored outside of the index structure and thus subsequent searching for this newly added data will be prohibitively slow.


A cluster literally clusters the index and data values together and is intended for use with join operations against relatively static data. Note that clusters are intended for OLTP read-only type tables where full table-scans are not required.

Hash Clusters

A hash clsuter is simply a cluster which is indexed using a hashing algorithm. Has clusters are more efficient than standard clusters and are even more appropriate to read only type data.

Index Organised Tables

Index organised tables are much like a cluster except that the index and data items are stored together for a single table whereas the real intention of a cluster is that of joined tables.