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;
=======
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>
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>
Example: create index ix1 on Employee(eid,ename);
-----------------
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;
![]() | ![]() |
No comments:
Post a Comment