Oracle Indexes and Constraints-10



A table dependent object. (Root — Branch –Leaf)

An index is an optional structure, associated with a table or table cluster.

Index properties:


Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.

When we move the table without index it will be unusable. A direct path load against a table or partition.


Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.


B tree index — the default and the most common.

Options for B tree  { Index-organized tables – IOT ,Cluster index table, Composite index, Unique index, Non unique index, Descending indexes, Reverse key indexes }

Normal Index (Default) IOT Cluster index Table
Optional IOT must contain a primary key No
Yes An IOT cannot be in a cluster Yes
Can modify Cannot modify an IOT, Only ALTER TABLE Can modify
Unique columns Small tables can be good candidates for IOTs Common columns of more table
It stores only an index key column, in a separate storage IOT indexes store all the columns of the table, no separate storage needed. It stores only index key column, no separate storage needed.
It stores as a logical order IOT stores as PK values order will be faster It stores a block, not point rows
No Secondary indexes Can be created No
 Unlimited Max columns 1000, Max PK column 32, Max index portion 255  Maximum 32 columns & cluster Index table

IOT – Initially it stores primary key & logical rowid as same order, when updates the PK might change but not logical. As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.

Index-organized tables – An index-organized table differs from a heap-organized because the data is itself the index. (SQL aka cluster index). An INCLUDING clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.

Normal primary key indexes, which store only the columns included in its definition, IOT indexes store all the columns of the table.


Cluster index Table – It is a table cluster that uses an index to locate data. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.


CREATE CLUSTER employees_departments_cluster (department_id NUMBER(4)) SIZE 512;

CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;

CREATE TABLE employees ( … )  CLUSTER employees_departments_cluster (department_id);

CREATE TABLE departments ( … ) CLUSTER employees_departments_cluster (department_id);


Heap Table Index Table
The rowid uniquely identifies a row Primary key uniquely identifies a row.
Can be stored in a table cluster with other tables. Cannot be stored in a table cluster.
Can contain LOB & LONG columns. Can contain LOB columns but not LONG columns.

A cluster is simply a method for storing more then 1 tables same column on the same block. The cluster key value is the value of the cluster key columns for a particular row.

Reverse key indexes — most useful for Oracle RAC and emp id: EE100.  The bytes of the index key are reversed.

Bitmap index and bitmap join indexes— compact; work best for columns with a small set of values.

In a bitmap index, each index key stores pointers to multiple rows. It’s designed for data warehousing.

Function based index — contain the precomputed value of a function ()/expression.

A function-based index can be either a B-tree or a bitmap index.

Application domain indexes — This type of index is created by a user for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file.

It’s mostly used for Unstructured Data – Image, video etc..


Difference between B tree and Bitmap

B tree Bit Map
High cardinality column (Low duplicate) Low cardinality column (High duplicate)
And, OR, Not will not be useful And, OR, Not will be used
By default all indexes b tree


B tree: Mostly created for unique columns/values

Sql> Create index <ind name> on <table> (col1) ;

Bit Map: – Mostly created for duplicate columns/values

Sql> Create bitmap index <ind name> on <table> (col1) ;

Composite Index: If you specify more than one columns

Sql> Create index <ind name> on <table> (col1,col2..) ;

Unique Index:  Used for unique columns

Sql> Create unique index <ind name> on <table> (col1) ;

Reverse key Index: Mostly created for values has character and numeric values

Ex: Empid – AAA10… Normal index will serach all records, but revese index will search in reverse direction and improve the search.

Sql> Create index <ind name> on <table> (col1) reverse;

Function Based Index: Mostly created for arithmetic operations and predefined function.

Sql> Create index <ind name> on <table> (salary+ (salary0.05));


How to  find the  index is currently used or not

Sql> Alter index <ind name> monitoring usage;

Sql> Select * from v$object_usage where index_name =’index name’;

Sql> Alter index <ind name> no monitoring usage;


Query to  find the  index is in the  database and present users

Sql> Select * from dba_indexes; — It will show all indexes

Sql> Select * from user_indexes; — present users indexes

Sql> Select * from dba_ind_columns; — It will show all indexes columns

Sql> Select * from user_ind_columns; — present users columns


Drop index

Sql> Drop index <ind name>;

Sql> Drop index <ind name> cascade;



All are same as SQL.


Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.


Specifies that values in the columns must be unique. It will allow NULL.


Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.

Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.


Specifies a wide range of rules for values in the table.

Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

5 + 5 =