Lesson 28 : Index

Index

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table.  And it provides direct, fast access to rows.  Actually, it is used for faster retrieval of records from tables.

Here are different types of indexes,

• Normal indexes. (By default, Oracle Database creates B-tree indexes.)

• Bitmap indexes, which store rowids associated with a key value as a bitmap.

• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.

• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

• Domain indexes, which are instances of an application-specific index of type indextype.

CREATE INDEX :

Syntax :

CREATE  [UNIQUE] I NDEX <index_name>  ON table_name (column1, column2, ... column_n)   [ COMPUTE STATISTICS ];

UNIQUE  denotes that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS  is to  collect statistics during the creation of the index.  The statistics are then used by the optimizer to choose the plan.

Examples :

SQL> create index  empnameidx on emp(empname);

Dropping index

Syntax :

Drop index <indexname>

Example :

SQL> drop index empnameidx;

Rename index :

Syntax :

ALTER INDEX <existing indexname>   RENAME TO <new name>

Example :

SQL> alter index empnameidx  rename to empname_idx;