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) Desending 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

SQL Cluster Table

Cluster Table

Cluster:-Cluster is a method by which we can store data of two or more tables the basis of cluster key

Cluster Key :-Cluster key is a column/columns by which the tables are usually joined. 
Example:- 
1) Create a cluster :- 
SQL>create cluster emp_dept (deptno number(2));

Cluster Index:-Index created on cluster is called cluster index.
Example:-
SQL>create index cls_idx on cluster emp_dept;

2) Creating tables in the cluster:-
SQL>create table dept12 (deptno number(2),
                    name varchar2(20),
                    loc varchar2(20))
                    cluster emp_dept (deptno);

SQL>create table emp12 (empno number(5),
            name varchar2(20),
            sal number(10,2),
            deptno number(2)) 
            cluster emp_dept (deptno) ; 

Drop a Cluster:-   a) If there is no table attached with cluster then we can drop cluster by below way      
SQL>drop cluster emp_dept;

b) If Tables attached with cluster then we can drop cluster with following way

SQL>drop cluster emp_dept including tables; --if tables attached with cluster

Data Dictionary for Cluster :-Below is the Data Dictionary used for getting the information of cluster
SQL>select * from user_clusters;