What does a two-part single index mean?

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 si开发者_如何学Cngle index
3. Are there any more kinds of indexes?

.

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.

I would assume that the author is referring to a composite index when he talks about a "two-part single index". The term "composite index" is a far more common way to refer to an index on multiple columns of a table.

If you have a single composite index on two columns, there is only one index structure that needs to be maintained during an insert so the overhead of index maintenance is not much different than the overhead of maintaining one single-column index.

CREATE TABLE t1 (
  col1 NUMBER,
  col2 NUMBER,
  col3 NUMBER
);

CREATE INDEX t1_composite_idx 
    ON t1( col1, col2 );

On the other hand, if you create separate indexes on each column individually, Oracle has to maintain two separate index structures which does roughly double the amount of index maintenance that is needed

CREATE TABLE t1 (
  col1 NUMBER,
  col2 NUMBER,
  col3 NUMBER
);

CREATE INDEX t1_idx1
    ON t1( col1 );

CREATE INDEX t1_idx2
    ON t1( col2 );

I would be rather leery, however, of the "factor of three" that the author quotes, however. There are a lot of variables that come into play that are not captured by that particular rule of thumb. It's useful to remember that adding indexes imposes potentially substantial costs on insert operations but it's much more useful to measure the actual cost that you are imposing when you are weighing the trade-offs to creating another index.

Are there any more kinds of indexes?

As for your last question-- Oracle has quite a few different types of indexes (particularly if we are counting composite indexes as a different type of index). This answer has been solely dealing with b*-tree indexes which are what people normally mean when they refer to "indexes" without qualifiers. Oracle, however, supports a number of different types of indexes-- b*-tree indexes, bitmap indexes, Text indexes, etc. It creates LOB indexes. It supports user-defined extensible indexes. And within each type of index, there are often dozens of different options. For example, you can create a function-based b*-tree index or a bitmap join index, you can specify custom lexers for an Oracle Text index, or you can define your own index structure for your own custom type.

Since the author does not seem to actually ever define the term, I can only guess that they mean a two-part single index is a composite key comprised of two columns and a single-part single index is an index based on a single column.