Indexes in Oracle

We can say index is a schema object which is used by Oracle server to speed up the retrieval of rows or by using a pointer.

The Input/Output of a disk can be reduced by using a rapid path access method to locate the data much faster then what we get earlier without having an index on our table. These are maintained automatically by Oracle server.

Types of Index in Oracle:

Btree (Binary Tree) index :
Binary implies each item within a tree has two separate options or nodes, which is just an idea. It is a tree like structure upside down where one option needs to have multiple options below it.

Bitmap index:
It means a map of bits, or can be called as two dimensional picture of a square box for ones and zeros.

Function based index:
The indexes which is based on expressions, for example if a function is been applied to a value of a column and the result which we get is called a function based index. These indexes are built from table columns, constants, SQL functions, and user-defined functions.

For example:

<br>CREATE INDEX UPPER_ADDRESS1_IDX<br>ON HZ_LOCATION (UPPER(ADDRESS1));<br>

Index Organized Table:
This is basically an object on a database, it can also be called as a table where the data is physically organized by or sorted by an index.

Cluster:
It is similar to “Index Organized Table”. But there are only two differences i.e. a cluster can be made of a JOIN (i.e. joined more than two tables) and the cluster may not require all the columns of a table in the JOIN.

Bitmap JOIN:
It is a JOIN between a bitmap index and a non-bitmap index.

Domain index:
These are certain specific type of domain, it has specific application like multimedia, large text objects like documents, maps etc…

Index are created by two ways :
Automatically : When we create or define a PRIMARY KEY or UNIQUE KEY constraint on a table a UNIQUE index is automatically created and the name is given to the constraint.

Manually: For faster retrieval of rows or records users can create a non unique index which is nothing but creating the index manually.

Syntax of Index :

<br>&nbsp;&nbsp;&nbsp; CREATE INDEX index_name<br>&nbsp;&nbsp;&nbsp; ON table (column[, column]…);<br>

Index can be created on one or more columns of a table.

Example for creating an Index:

<br>/* Create a table*/<br>SQL&gt; create table temp <br>(id number PRIMARY KEY ,<br>col1 varchar2(10), <br>col2 date); <br>/* create an index to the table created */<br>SQL&gt;CREATE INDEX col1_idx<br>ON temp (col1,col2) ;<br>

From the above example the we can see that we created one index for “col1” which will speed up the query to access “COL1” and “COL2” but it also created another index for column “ID” automatically as we have defined a PRIMARY KEY to “ID” column.

Few things that we should remember like where to create an Index:
# If a column is having a large number of null values and wide range of values.
# If we are using one or more column very frequently in after “WHERE “clause or in a JOIN condition then we should create an index on the same columns.
# If we have a large table and the query that we might be using to retrieve less than 4-5 percent of the total rows then index should be present on the column. 

Scenario where indexes are not preferred to defined:
It is usually not worth creating an Index if the table is small.
The columns which are not often used as a condition in the query.
Most queries are expected to retrieve more than 2 to 4 percent of rows in the table.
The table is updated frequently.

 

How to check if the index is been created on a table or not:

There is a data dictionary view contains the name of the index and its uniqueness i.e. “USER_INDEXES” and “USER_IND_COLUMNS”  is the view which contains the index name , table name and the column name for which the index is been created.
Code to check for indexes on a table:

<br>SELECT uc.index_name,uc.column_name, ui.uniqueness<br>&nbsp;&nbsp;&nbsp;&nbsp; FROM user_indexes ui, user_ind_columns uc<br>&nbsp;&nbsp;&nbsp;&nbsp; WHERE uc.index_name=ui.index_name<br>&nbsp;&nbsp;&nbsp;&nbsp; AND uc.table_name='EMP';<br>

Finally how to remove an index from a table:

For dropping an index we should be the owner or we should have “DROP ANY INDEX” privilege. By using ‘DROP INDEX’ command we can remove the index from the table which will also remove the information from data dictionary.

Advertisements

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