INDEX

INDEXES

Index is typically a listing of keywords accompanied by the location of information on a subject. We can create indexes explicitly to speed up SQL statement execution on a table. The index points directly to the location of the rows containing the value.

WHY INDEXES?

Indexes are most useful on larger tables, on columns that are likely to appear in where clauses as simple equality. 

TYPES

1     Unique index

2     Non-unique index

3     Btree index

4     Bitmap index

5     Composite index

6     Reverse key index

7     Function-based index

8     Descending index

9     Domain index

10  Object index

11  Cluster index

12  Text index

13  Index organized table

UNIQUE INDEX

Unique indexes guarantee that no two rows of a table have duplicate values in the columns that define the index. Unique index is automatically created when primary key or unique constraint is created.

Ex:

     SQL> create unique index stud_ind on student(sno);

NON-UNIQUE INDEX

Non-Unique indexes do not impose the above restriction on the column values.

Ex:

     SQL> create index stud_ind on student(sno);

BTREE INDEX or ASCENDING INDEX

The default type of index used in an oracle database is the btree index. A btree index is designed to provide both rapid access to individual rows and quick access to groups of rows within a range. The btree index does this by performing a succession of value comparisons. Each comparison eliminates many of the rows.

Ex:

     SQL> create index stud_ind on student(sno);

BITMAP INDEX

This can be used for low cardinality columns: that is columns in which the number of distinct values is small when compared to the number of the rows in the table.

Ex:

     SQL> create bitmap index stud_ind on student(sex);

COMPOSITE INDEX

A composite index also called a concatenated index is an index created on multiple columns of a table. Columns in a composite index can appear in any order and need not be adjacent columns of the table. 

Ex:

     SQL> create bitmap index stud_ind on student(sno, sname);

REVERSE KEY INDEX

A reverse key index when compared to standard index, reverses each byte of the column being indexed while keeping the column order. When the column is indexed in reverse mode then the column values will be stored in an index in different blocks as the starting value differs. Such an arrangement can help avoid performance degradations in indexes where modifications to the index are concentrated on a small set of blocks.

Ex:

     SQL> create index stud_ind on student(sno, reverse);

We can rebuild a reverse key index into normal index using the noreverse keyword.

Ex:

     SQL> alter index stud_ind rebuild noreverse;

FUNCTION BASED INDEX

This will use result of the function as key instead of using column as the value for the key.

Ex:

     SQL> create index stud_ind on student(upper(sname));

DESCENDING INDEX

The order used by B-tree indexes has been ascending order. You can categorize data in B-tree index in descending order as well. This feature can be useful in applications where sorting operations are required.

Ex:

     SQL> create index stud_ind on student(sno desc);