What is index and its use?How to create unique index?And Types of Indexes

INDEXES:

• Just like a book index helps us in finding the chapter and page number, an oracle index speeds up access time to the rows.

• Indexes are optional structures associated with tables.

• An index is also a schema object.

• We can drop an index without dropping the table it indexes.

• An index can be created explicitly or automatically.

• When we drop a table the corresponding indexes of the table are dropped.

• A unique index gets created when we create a unique key or primary key in a table definition.  
•  The name of the index is the name of the constraint.

• Indexes can be unique or non-unique.  Unique indexes guarantee that no two rows of a table have duplicate values in the columns that define the index.  Non-unique indexes do not impose this restriction on the column values.

• The presences of more number of indexes on a table decreases the performance of DML statements, because Oracle must make changes to the indexes associated with the table

Syntax:

create index <index_name> on <table_name> (column_names_separated_by_comma);

An index on a table can be created for one column or more than one column [composite index]

Examples:

 create index my_index on emp(empno); [default NONUNIQUE]

Index created.

SQL> desc user_indexes               [use this dd VIEW]

How to create unique index:

create unique index my_indx on emp(ename);

Index created.                                            

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

INDEX_NAME                     INDEX_TYPE

------------------------------ -----------

MY_INDEX                       NORMAL

MY_INDX                        NORMAL

drop index my_index;

Index dropped.

drop index my_indx;

Index dropped.

create index my_indx on emp(empno,ename);

Index created.

Index created with above statement will be used to retrieve data, when the where clause has either

·         Both empno,ename or

·         empno alone. 

But, not when ename alone is used. 

So it creates the index based on the first column.

Types of Indexes:

1.Reverse key indexes

2. Bitmap Indexes

3. Index-Organized Tables

Bitmap index: When we have low cardinality columns [columns in which number of bitmap values is small compared to the number of rows].  Ex: yes and no.  Here we can use the bit map index.  For example:

If the values in a column are repeated more than a hundred times is a candidate for bitmap index.  On a table with one million rows, a column with 10,000 distinct values is a candidate for a bitmap index.

create bitmap index <name_of_index> on <table_name>(column_name);

Reverse Key Indexes:  Creating a reverse key index, when compared to a standard index, reverses each byte of the column being indexed while keeping the column order.  This can be used in some situations where there can be performance degradation.  Using the reverse key index can avoid such performance degradations.

Index-Organized Tables:  An index-organized table differs from a regular table in that the data for the table is held in its associated index.  Changes to the table data, such as adding new rows, updating rows, or deleting rows, result only in updating the index.  The index-organized table is like a regular table with an index on one or more of its columns.  But instead of maintaining two separate storages for the table and the index, the database system only maintains a single index, which contains both the encoded key value and the associated column values for the corresponding row.  Instead of having the row ROWID as the second element of the index entry, the actual data of the row is stored in the index.

We can create a index organized table using create table command with ORGANIZATION INDEX clause.

create table myemp_index(empno number(5) primary key,ename varchar2(12)) ORGANIZATION INDEX;

Table created.

The primary key is a must for creating index-organized table.

INSERT INTO MYEMP_INDEX(SELECT EMPNO,ENAME FROM EMP);

14 rows created.

SELECT * FROM MYEMP_INDEX;

    EMPNO ENAME

--------- ------------

     7369 SMITH

     7499 ALLEN

     7521 WARD

     7566 JONES

     7654 MARTIN

     7698 BLAKE

     7782 CLARK

     7788 SCOTT

     7839 KING

     7844 TURNER

     7876 ADAMS

     7900 JAMES

     7902 FORD

     7934 MILLER

14 rows selected.

Also read about indexing in Datawarehouse