oracleworld

Index is a database object, which speeds up access to the data. There are different types of index which the user can create.

CREATE [UNIQUE] INDEX index_name

ON table_name (col_name1 [ASC| DESC], col_name2,);

1) A table can have any number of indices.

2) An index can be on a single column on a single column or on multiple columns. Up to 32 columns can be included in one index.

3) Updating of all indices is handled by Oracle.

4) UNIQUE ensures that all rows in a table are unique.

5) Index is ascending by default.

6) ORACLE decides when to use the index while accessing the data.

7) Removal of an index does not affect the table on which it is based.

When you create a primary or a unique constraint on the table, a unique index is automatically created for you. The name of the constraint will be used for the index name.

Example:

CREATE INDEX emp_sal_index ON emp (sal);

To allow only unique values in ename field, the create statement will be,

CREATE UNIQUE INDEX emp_ename_unidex ON emp (ename);

Bitmap Index:

Regular index discussed earlier does not store columns that contain NULL. Bitmap index allows columns to store NULL. Such indexes are useful for some types of SQL statements like –

  SELECT COUNT (*) FROM emp;

   SELECT COUNT (*) FROM emp WHERE comm. Is NULL;

A bitmap index can have a maximum of 30 columns.

Example:

CREATE BITMAP INDEX emp_idx ON emp (comm.);

Function Based Index:

To create such index the user should have Query Rewrite privilege or the system parameter Query Rewrite privilege or the system parameter QUERY_REWRITE_ENABLE should be set to TRUE.

Example:

CREATE INDEX sales_margin_index ON sales (revenue – cost)

Such index can be very useful when you perform query where you compare revenue – cost.

CREATE INDEX uppercase_idx ON emp (UPPER (empname));

Trackback URI | Comments RSS