indexes in oracle

---

INDEXES:
=======
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.Indexes are classified into two types.

                 1) Unique Index
                 2) Non-Unique Index


Unique Index:
---------------
        If we apply primary key constraint or unique constraint on any column in table oracle internally maintains one of the index mechanism is called unique index.

Non-Unique Index:
--------------------
            If we apply the index mechanism on any particular column in the table manually those indexes can be called as non-unique index.
          Once we apply the index mechanism oracle internally maintains or creates a separate table called index table and which maintains a chain relationship between index table and regular base table.

--> Non-Unique indexes are again divided into two types.

                    1) Simple Index
                    2) Composit Index

Simple Index:
--------------
            Creating index on only one column those indexes can be called as simple index.

Syntax   : create index <index_name> on <table_name>(Column_Name);
Example: create index ix on Employee(eid);

NOTE : we can’t apply the non-unique index on unique index.

Composite Index :
--------------------
Creating index on more than one column simultaneously then those indexes can be called as composite index.

Syntax   : create index <index_name> on (List of Columns);
Example: create index ix1 on Employee(eid,ename);

List Of Indexes:
-----------------
Syntax: select * from user_indexes;

List Of indexes on a particular table:
----------------------------------------
Syntax: select index_name from user_indexs where table_name=’EMPLOYEE’;

Syntax to drop the Index:
---------------------------
Syntax   : drop index <index_name>;
Example:drop index ix1;

---