How-to: Indexes and Sequences

Unique and Nonunique Indexes

Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns).
For performance reasons, Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table. (Unique integrity constraints are enforced by automatically defining an index.)

You can create many indexes for a table as long as the combination of columns differs for each index.

CREATE INDEX emp_idx1 ON emp (ename, job);
CREATE INDEX emp_idx2 ON emp (job, ename);

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data.

The query optimizer can use an existing index to build another index. This results in a much faster index build.

Index multiple columns

A composite index is an index that you create on multiple columns in a table.
This can speed retrieval of data if the SQL WHERE clause references all (or the leading portion) of the columns in the index. Therefore, the order of the columns used in the definition is important - the most commonly accessed or most selective columns go first.

Rebuilding indexes

Although indexes can be modified with ALTER INDEX abc REBUILD it is a commonly held myth about rebuilding indexes that performance will automatically improve.
By contrast redesigning an index to suit the SQL queries being run will give measurable results.

Function-Based Indexes

You can create indexes on functions and expressions that involve columns in the table being indexed.
A function-based index precomputes the value of the function or expression and stores it in an index (B-tree or bitmap).

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index:
CREATE INDEX uppercase_idx ON emp (UPPER(empname));
can facilitate processing queries such as this:
SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';

To use function-based indexes you must gather optimizer statistics.
(Not compatible with Rule-based optimization.)

If the function is a PL/SQL function or package function, any changes to the function specification will cause the index to be automatically disabled.

How Indexes Are Searched

Index unique scan used when all columns of a unique (B-tree) index are specified with equality conditions.
e.g.
name = 'ALEX'

Index range scan is used when you specify a wildcard or interval (bounded by a start key and/or end key.)
e.g.
name LIKE 'AL%'
order_id BETWEEN 100 AND 120
order_book_date > SYSDATE - 30

Key Compression

Like any form of compression, Key compression can lead to a huge saving in space, letting you store more keys in each index block, which can lead to less I/O and better performance. Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead.

Reverse Key Indexes

Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.
CREATE INDEX i ON t (my_id) REVERSE;

The values 4771, 4772, 4773 in the index are reversed to 1774, 2774, 3774

The more even distribution of "hits" on the various leaf blocks is the RKI's best feature. In a heavy, concurrent insert environment, rather than having everyone wanting access to *the* block, you spread the blocks being
hit and hence reduce the potentially expensive buffer busy waits.

The main disadvantage is the inability to perform index range scans as such values are now distributed all over the place, only fetch-by-key or full-index (table) scans can be performed.

You can specify the keyword NOREVERSE to REBUILD a reverse-key index into one that is not reverse keyed: Indexes. Rebuilding a reverse-key index without the NOREVERSE keyword produces a rebuilt, reverse-key index. You cannot rebuild a normal index as a reverse key index. You must use the CREATE statement instead.

Bitmap Indexes ( Enterprise Edition only.)

In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient. Bitmap indexing is of great benefit to data warehousing applications.

Bitmap indexes are good for:

Low cardinality columns have a small number of distinct values (compared to the number of rows)
e.g. Gender or Marital Status

High cardinality columns have large numbers of distinct values (over 100).

Bitmap indexes include rows that have NULL values, and can dramatically improve the performance of ad hoc queries.

Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.

Unlike traditional a B-tree indexes, Bitmap indexes are typically only a fraction of the size of the
indexed data in the table.

Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons. For example, a salary column that usually appears in WHERE clauses in a comparison to a certain value is better served with a B-tree index.

Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.

The advantages of bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. (See the Oracle concepts manual for an example of this)

Unlike most other types of index, Bitmap indexes include rows that have NULL values. This can be useful for queries such as SELECT COUNT(*) FROM EMP;

You can create bitmap indexes local to a partitioned table (not a global index).

Bitmap Join Indexes

A join index is an index on one table that involves columns of one or more different tables through a join.

Similar to the materialized join view, a bitmap join index precomputes the join and stores it as a database object. The difference is that a materialized join view materializes the join into a table while a bitmap join index materializes the join into a bitmap index. See the Oracle concepts manual for a full example.

Dimensions

Define hierarchical relationships between pairs of columns or column sets. (Typically data warehouse parent-child relationships.)
The columns in a dimension can come either from the same table (denormalized) or from multiple tables (fully or partially normalized).
To define a dimension over columns from multiple tables, connect the tables using the JOIN clause of CREATE DIMENSION HIERARCHY.

Sequences

The sequence generator provides a sequential series of numbers. The sequence generator is especially useful for generating unique sequential ID numbers.

Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back.

A sequence generates a serial list of unique numbers for numeric columns of a database's tables. Sequences simplify application programming by automatically generating unique numerical values for the rows of a single table or multiple tables.
For example, assume two users are simultaneously inserting new employee rows into the EMP table. By using a sequence to generate unique employee numbers for the EMPNO column, neither user has to wait for the other to enter the next available employee number. The sequence automatically generates the correct values for each user.
Sequence numbers are independent of tables, so the same sequence can be used for one or more tables. After creation, a sequence can be accessed by various users to generate actual sequence numbers.

"Mathematicians have tried in vain to this day to discover some order in the sequence of prime numbers, and we have reason to believe that it is a mystery into which the human mind will never penetrate” ~ Leonhard Euler

See also
CREATE INDEX