INDEXING

Index: Index is an object that contains value for each entry of the record in the table object used for faster searching and sorting of data.

Oracle assigns a unique identification number to every record in the database called “ROWID” – Row Identification. It contains the physical address/location of a record in the Database (Hard disk).

SQL> select  ROWID from <Table_name>

By default when ever we search for records with the “WHERE” clause, Oracle looks for the desired records sequentially. This takes considerable time for the retrieval of records, there fore, oracle provides as object called “INDEX” which is used to speedup the searching records.

Syntax for creating Index:

a. Creating index on single column:

SQL> create index <index_name> on <table_name>(<column_name>);

Ex:

SQL> create index emp_ind on emp (empid);

b. Creating index on multiple columns:

SQL> create index <index_name> on <table_name>(<column_1,column_2,……..>);

Ex:

SQL> create index stucourse_ind on student (stuid,course);

c. Removing index:

SQL> drop index <index_name>;

Ex:

SQL> drop index stucourse_ind;

Types of indexes:

i.                    Non-unique: The values in column may repeat in various records.

SQL> create index stu_ind1 on student(course);

Ex: multiple students can have same course or may be different

ii.                  Unique: duplicate values can not exists in different records of the same table.

iii.                B-Tree Index: B-tree should be used when a column has large number of duplicate values. There will be several colleges, there fore we will create the B-tree index for college column.

iv.                Bitmap indexes: Bitmap index should be used when a column has few duplicate values.

SQL> create bitmap index stu_gender on student(gender);

Data Dictionary tables for indexs:

SQL> select index_name,index_type from user_indexes;

SQL>select index_name,table_name,column_name from user_ind_columns;