Wednesday, July 27, 2011

Detail Overview on INDEX in Oracle

In this article, we will discuss about several topics on index in oracle database. The topics are as follows:

What is Index and what are their types?
How to create Index?
How to alter index?
How to rebuild index?
How to handle index corruption?

What is index: An index in oracle database is a schema that contains an entry for each value that appears in the indexed columns of the table. It offers facility to fast access of the rows.

Types of Index
: There are five types of index in oracle database, which are given below:
  1. Normal index
  2. Bitmap index
  3. Partitioned index
  4. Function Based index
  5. Domain index
How to Create: You can created an index in oracle database by “Create Index” command. The syntax is given below:
CREATE INDEX index_name ON table_name(column_name) TABLESPACE index_tbs;

Example: CREATE INDEX stellar_employee_01 ON employee (empCode) TABLESPACE index_tbs;

In the above syntax, we have created an index stellar_employee_01 on the empCode column of employee table.

How to Alter Index: You can alter index in the oracle database by given below command.
ALTER INDEX Index_name;

Example: ALTER INDEX stellar_employee_01;

How to Rebuild Index: You can rebuild index anytime whenever you need. The command for rebuilding index in Oracle database in follow:

Example: ALTER INDEX stellar_employee_01 REBUILD;

How to Handle Index Corruption: Corruption in the index of oracle database can happen anytime. This can happen due to several reasons; some of them are virus attack, human errors, power failure, simultaneous access, and many more. An expert oracle database administrator is always ready to handle corruption. You can restore corrupted index from the updated backup, if you don't have any recent backup then the best and effective solution is to recover oracle database index by any third party oracle recovery software. There is so many oracle recovery applications available in the market but the most recommended software by expert database administrators is Stellar Phoenix Oracle Recovery.

Note: You can do several other action on the index of oracle database that will describe in next article.

No comments:

Post a Comment