Couple of index goodies for 8i

Making bitmap indexes quicker

The first of these is actually a TABLE enhancement. To ensure that bitmap indexes are kept small (which typically makes them faster) you want to maiximise their compression. A new table clause allows you to limit the number of records per block in a table. To quote from the 8i documentation


determines whether Oracle restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as small (compressed) as possible.



instructs Oracle to calculate the largest number of records in any block in the table, and limit future inserts so that no block can contain more than that number of records. Restriction: You cannot specify MINIMIZE for an empty table.

Smaller B-tree indexes

(I think) some early versions of Oracle supported key compression but it was later removed. If you have an index with many columns then often a large number of the leading columns are repeated. These are all stored in an index. To quote from the 8i documentation:

Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.

Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys per index block while improving performance.

Key compression can be useful in the following situations:

You can enable key compression using the COMPRESS clause.You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry. For example, the following statement will compress away duplicate occurrences of a key in the index leaf block.

CREATE INDEX  emp_ename (ename)