Databases Reference
In-Depth Information
The B-tree index and other index types are briefly introduced in the following subsections. Where
appropriate we'll indicate where a particular index type is fully discussed in subsequent chapters in this
topic.
B-tree Indexes
We should point out that B-tree indexes are the entire focus of Chapter 2. We introduce them in this
section so that you can juxtapose them with other index types. As mentioned, the default index type in
Oracle is a B-tree index. This index type is very efficient for high cardinality column values. For most
applications, this index type is appropriate.
Without specifying any options, a B-tree is created with the
CREATE INDEX
statement; all you need to
provide is the index name, table name, and column(s).
create index cust_idx2
on cust(first_name);
Unless you have verifiable performance reasons to use a different index type, use a B-tree. Too
often DBAs or developers read about a new indexing feature and assume that the vendor's exaggeration
of a feature matches the actual realized benefits. Always validate your reasons for choosing to
implement a new index type or feature.
There are several subtypes of B-tree indexes.
•
Index-organized table
•
Unique
•
Reverse key
•
Key compressed
Descending
These B-tree subtypes are briefly introduced in the next several subsections.
•
Index-Organized Table
An
index-organized table (IOT) stores the entire contents of the table's row in a B-tree index structure.
An IOT provides fast access for queries that have exact matches and/or range searches on the primary
key.
Even though an IOT is implemented as a B-tree index structure, it is created via the
CREATE
TABLE...ORGANIZATION INDEX
statement. For example,
create table prod_sku
(prod_sku_id number
,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id, sku)
) organization index;
