you're reading...

A Short Guide to Oracle SQL [X] – Other Database Objects [Index]

Indexes are optional database objects that (if used properly) can increase the performance of queries. When retrieve data from the table Oracle has two options to do, First reads every row in the table (Full Table scan) or use the index to find the relevant rows quickly.

Full table scan in itself is a resource consuming process whether it is memory wise or I/O wise.

Indexes in oracle are similar to those in the books, help us to provide faster access path to table’s data. It should be noted that while it can improve the performance of data retrieval, it also takes significant time and storage space to maintain the index. Every time you update the table/index column(s), corresponding index must also be updated.

An index can be created on a single column of the table or it can include multiple columns of a table. Indexes based on multiple columns (incl. primary key column) called Composite index or Concatenated index. An index is automatically created on the primary key and unique key but it strongly recommended to create an index of foreign key.

You can create an index using CREATE INDEX command:

CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index
   ON { column_list};
CREATE INDEX emp_idx_01
   ON employees (first_name);
CREATE INDEX emp_idx_name
   ON employees (first_name, last_name);

An index can be modified using ALTER INDEX command.

ALTER INDEX emp_idx_01
   TO emp_idx_fname

Apart from renaming an index ALTER INDEX can be used an index to rebuild, making it (in)visible from optimizer, monitoring its usage (MONITORING USAGE) etc.

You can drop an index using DROP INDEX.

DROP INDEX [schema.] index [FORCE];

DROP INDEX emp_idx_01;

B-Tree Index

The B-tree Index (Balanced Tree) is the most column index used in oracle can be unique, simple, concatenated or non-unique. B-Tree index helpful on high-cardinality columns that is columns having many distinct values. It consists of two types of blocks: Leaf and Branch. Leaf block stores the value with rowid (physical storage location for the data) whereas Branch block contains the key (index column) and address to the another index block.

Unique and Non-Unique Index

Unique index enforce uniqueness among the values in a column, so that no two rows in the table may have the same value in the key column(s). Non-Unique index do not impose these restriction and allows multiple rows to have the same index entry.

Bitmap Index

A bitmap index is typically used on columns with very few distinct values. In a bitmap index, the database stores a bitmap for each index key as bitmaps of 0s and 1s. 1 indicates that the row contains the key value and a 0 indicates that it does not. However, a bitmap index applies locking at the data block level, making it inappropriate for many OLTP applications. Generally a bitmap indexed table is not subject to heavy DML activities (can lead to significant slowing down the transactions).

Bitmap Index is available with Enterprise Edition.

An interesting article about Bitmap Index vs. B-tree Index can be found on OTN.

Bitmap Index vs. B-tree Index: Which and When?

Invisible Index

Starting with Oracle Database 11g you can create Invisible Index, which is ignored by optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE. More on this can be find here.

Function-Based Index

A function-based index is based on an expression. Expression can include one or more functions.

CREATE INDEX emp_idx_func_name
  ON employees (UPPER(last_name));

Function-based indexes can be either a B-tree or a bitmap index and pre-computes the value of the function or expression. This kind of function can be useful queries like this:

  FROM employees
    WHERE UPPER(last_name) like 'S%';

Relevant Privileges

Relevant Views

Doc Links:
Oracle Database Concepts – Indexes
Oracle Database Administrator’s Guide – Managing Indexes



One thought on “A Short Guide to Oracle SQL [X] – Other Database Objects [Index]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: