What does a two-part single index mean?

Question

In general, every index on a table slows down INSERTs into the table by a factor of three; two indexes generally make the insert twice as slow as one index. (Yet, a two-part single index is not much worse than a single-part single index).

I got this from the book Oracle 9i Performance Tuning Tips and Techniques by Richard Niemiec (Osborne Oracle Press Series).

What does the following terms mean:

1. Two-part single index
2. Single part single index
3. Are there any more kinds of indexes?

.

Answer 1

By two-part index I presume Rich means a composite index, that is an index built on multiple columns. Like this:

 create index t23_t_idx on t23 (col4, col2);

Whereas a single part index indexes a single column:

create index t23_s_idx on t23(col1);

The indexes created above are b-tree indexes. Oracle has many other types of indexes. For starters, indexes can be unique, in which case they only allow one instance of the given value in the indexed column (or permutation of values for composite columns).

There are also bit-mapped indexes, which impose a much higher performance penalty on DML but which speed up certain types of query; it is rare to come across bitmapped indexes outside of data warehouses.

We can create function-based indexes which allow us to index the results of a deterministic function (i.e. one that is guaranteed to produce the same result for a given input). This is how we can build an index on a date column which ignores the time element:

create index t23_fbi_idx on t23( trunc(col_34));

We can also build domain indexes on text columns. And there are special indexes for partitioned tables.

All of these are covered in more detail in the documentation. Find out more.