Oracle Indexes - Expert Indexing in Oracle Database 11g - page 7

 Note See Chapter 4 for implementation details regarding an index-organized table.
Unique Indexes
When creating a B-tree index you can define it to be a unique index. In this regard it acts like a unique
key constraint. When inserting into the corresponding table, the unique index will guarantee that any
non-null values inserted into the table are distinct. For this reason, unique indexes are commonly used
in conjunction with primary key and unique key constraints (see Chapter 2 for complete details).
A unique index is specified via the CREATE UNIQUE INDEX statement.
create unique index cust_uidx1
on cust(last_name, first_name);
 Note See Chapter 2 for a complete discussion on the advantages and disadvantages to creating a unique
index versus allowing Oracle to automatically create the index when defining a primary key or unique key
constraint.
Reverse Key Indexes
Reverse key indexes are useful to balance I/O in an index that has many sequential inserts. These
indexes can perform better in scenarios where you need a way to evenly distribute index data that would
otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid
having I/O concentrated in one physical disk location within the index during large inserts of sequential
values. This type of index is discussed further in Chapter 5.
A reverse key index is specified with the REVERSE clause, like so:
create index cust_ridx1
on cust(cust_id) reverse;
 Note You can't specify REVERSE for a bitmap index or an index-organized table. Also, a reverse key index can't
be of type descending.
Key Compressed Indexes
A key compressed index is useful in reducing the storage and I/O requirements of concatenated indexes
where the leading column is often repeated. Use the COMPRESS N clause to create a compressed index.
create index cust_cidx_1
on cust(last_name, first_name) compress 2;