What does a two-digit index mean?

Question

In general, each index on a table slows down INSERT by a factor of three; two indexes usually insert twice slowly, like one index. (However, a two-part unit index is not much worse than a single unit index).

I got this from Richard Nimetz's Osborne Oracle Press Series, "Oracle 9i Performance Tips and Methods.

What do the following terms mean:

• Two-Part Unit Index
• Single part index
• Are there any other types of indexes?

.

Answer 1

From the two-part index, I assume that Rich stands for a composite index, which is an index built on multiple columns. Like this:

create index t23_t_idx on t23 (col4, col2); 

While the index of one part indexes one column:

 create index t23_s_idx on t23(col1); 

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

There are also bitmapped indexes that impose much lower performance on DML but speed up certain types of queries; raster indexes outside data warehouses are rare.

We can create functional indexes that allow us to index the results of a deterministic function (i.e., guaranteed to get the same result for a given input). Here's how we can create an index on a date column that ignores a time element:

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

We can also create domain indexes in text columns. And there are special indexes for partitioned tables.

All this is described in more detail in the documentation. Find out more .

Answer 2

I would suggest that the author refers to a composite index when it talks about a "two-part single index." The term composite index is a much more common way of accessing an index on multiple columns of a table.

If you have one composite index in two columns, only one index structure should be supported during insertion, so the overhead of maintaining the index is not much different from the overhead of maintaining one index with one column.

 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 for each column separately, Oracle should support two separate index structures, which is about twice the amount of index maintenance required.

 CREATE TABLE t1 ( col1 NUMBER, col2 NUMBER, col3 NUMBER ); CREATE INDEX t1_idx1 ON t1( col1 ); CREATE INDEX t1_idx2 ON t1( col2 ); 

However, I would be quite careful about the "factor of three" quoted by the author. There are many variables that come into play that are not captured by this particular rule. It’s useful to remember that adding indexes imposes potentially significant costs on insert operations, but it’s much more useful to measure the actual costs you incur when you weight the trade-offs to create another index.

Are there any other types of indexes?

As for your last question, Oracle has quite a few different types of indexes (especially if we count composite indexes as different types of indexes). This answer was exclusively about b * -tree indexes, which people usually mean when they refer to "indexes" without qualifiers. However, Oracle supports a number of different types of indexes: b * -tree indexes, bitmap indexes, text indexes, etc. It creates LOB indexes. It supports custom extensible indexes. And in each type of index, there are often many different options. For example, you can create a b * -tree index based on functions or a raster join index, you can specify custom lexers for the Oracle Text index, or you can define your own index structure for your own type.

Answer 3

Since the author apparently never defined this term, I can only assume that they mean that two separate indexes are a composite key consisting of two columns, and a one-part index is an index based on a single column.