SQL Index

Index

Index :- Index is a data structure like table.Index store column data(On which index created) with row identifier rowid.  Database indexes are used to increase the query performance.
       
Types of Index:-By structure index are 2 types
    1) B-Tree Index
    2) Bitmap Index
B-Tree Index:-By default B-tree index created when type not mentioned.B-Tree index created on column/columns those cardinality (High degree of distinct value) is very high.

Syntax:-Create index on (,,..);
Example:-create index idx_empno on emp1(empno);

Bitmap Index:-Bitmap index is created on column/columns those cardinality is very low.Just like gender column in a table which have only two distinct values "M" & "F" (very low cardinality).
Syntax:-Create bitmap index on (,,..);
Example:-create index idx_gender on student(gender);

Different type of Indexes:-Below are the list of indexes which are available in Oracle

1) Index Organized Table(IOT):-This Index created when most of the columns of table are used in Primary Key. IOT store complete data of tables as B-tree index structure. This index is created at the time of table creation.

Syntax:- Create table ( datatype(size), datatype(size),..,
                                   constraint primary key (,..))
                                   organization index;
Example:-create table item_details(item_id number,item_name varchar2(20)
                                   constraint pk_item primary key (item_id,item_name))
                                   organization index;

2) Unique Index:-This index is created with "unique" keywork .This is a conjustion for Primary Key and Unique Key constraint.
Syntax:-Create unique index on (,,..);
Example:-create unique index idx_regno on student(regno);

3) Reverse Key Index:-Reverse key index created with "reverse" keyword. useful to balance I/O in an index that has many sequential inserts.

Syntax:-Create unique index on (,,..) reverse;
Example:-create unique index idx_regno on student(regno) reverse;

4) Key Compressed Index :-This index created when the leading column has repeated values. It compresses leaf block entries
Example:- create index cust_idx1 on cust(last_name, first_name) compress 2;

5) Descending Index :-
Example:-create index cust_idx2 on cust(cust_id desc);

6) Function Based Index :-When we are using function frequently in a column in query then we create function based index on that column.
Example:-create index cust_idx3 on cust(upper(last_name));

7) Virtual Index :-We can create virtual index by mentioning "nosegment" clause,It is created for tunning purpose.
Example:-create index cust_idx4 on cust(first_name) nosegment;

8) Invisible Index :-Optimizer does not use the invisible index when retrieving data for a query.It is used for test purpose.
Example:-create index cust_idx5 on cust(last_name) invisible;

Partitioned Table Indexes:- There are 2 type of indexes in Partitioned Table.
        1) Global Partitioned Index
        2) Local Partitioned Index