ORACLE INDEXES

                                  ORACLE INDEXES

• Introduction.
• Removing index.
•  Creating index on multiple columns.
• Types of index.
• Advantage of index
• Disadvantage of index.
•  Data dictionary table for index.

·         

·       

Introduction:-

Index is an object which can be defined as the order list of value of a column of combination of column used for faster searching and sorting of data.

Oracle assign a unique identification number to every record in the database called ROWID. It contain the physical address/location of a record in the database.

ROWID is a pseudo-column associated with each and every record of a table and is represented by radix values.

To speed up the retrieval of record for job column we will create an index.

Where ind_emp_job is user define name for index object.

Removing index:-

To remove the index use for DDL statement for drop index.

SQL> drop index ind_emp_job;

Index dropped.

Creating index on multiple columns:-

SQL> create index ind_emp_ename_job on emp(ename,job);

Index created.

Types of index:-

·         Non-unique index

·         Unique index

·         B-tree

·         Bitmap index

Index are classified on two parameters.

·         Depending on data values: unique/non-unique.

·         Depending on data structure being employee for indexing: b-tree/bitmap.

Non-unique index:-

The non-unique index created the values in job column may repeated.

SQL> create index ind_emp_job on emp(job);

Index created.

Unique index:-

The unique record means that the duplicate record can’t exit in different record of the same table.

SQL> create unique index ind_unique_emp_empno on emp(empno);

Index created.

Notes:-

Oracle automatically create unique index for the primary key column and unique constraints columns. That why we can’t insert duplicate values.

B-tree index:-

Oracle uses the b-tree structure by default for index.

The b-tree index should be used when a columns has a large number of distinct values.

Bitmap index:-

The bitmap index is used for columns having a few distinct values.

Sql> create bitmap index ind_bitmap_emp_gender on emp(gender);

Index created.

Situation when oracle uses index:-

a)      The WHERE clause of the SLECT statement use the index columns.

b)      The ORDER BY clause of the SLECT statement use the index columns.

c)      When two tables are join on the index columns.

Situation when oracle does not use index:-

a)      The SELECT statement does not contain the WHERE OR ORDER BY clause.

b)      The WHERE clause contains a columns which is not index.

c)      The ORDER BY clause contains a columns which is not indexed.

Advantage of index:-

a)      Faster retrieval of record using index column in the WHERE clause.

b)      Faster retrieval and sorting of record using indexed columns in the ORDER BY clause.

c)      Faster retrieval of record when joining table on the index columns.

Disadvantage of index:-

a)      The DML operation like INSERT, UPDATE, DELETE consume more time for index table as index processing must be done for each record.

b)      Index is a separate organization/structure which is maintains for each and every index created.

c)      When a DML operation is performed then the change are reflected in the base table as well as the index structure is modified to incorporate the new change.

Data dictionary tables for indexes:-

The data dictionary USER_INDEXES and USER_IND_COLUMNS are the data dictionary tables containing information.

select index_name,index_type,table_name from user_indexes where table_name='EMP';

now we want to the column on which index are have been created.

select index_name,table_name,column_name from user_ind_columns where table_name='EMP';