Index in Oracle

Index:-
Index is a schema object. Index is a performance-tuning method. Using Index retrieval of data from database is fast.

Syntax:-
CREATE INDEX index_name
ON table_name(column_name1,column_name2,column_name3,…);

Rename an INDEX:-
Alter INDEX old_index_name
RENAME TO new_index_name;

Drop an INDEX:-
DROP INDEX index_name;

Type of Index:-

1. Normal Index(B-Tree Index)
2. Bitmap Index
3. Function-based Index

Normal Index:-
It is also called B-TREE Index, By Default B-TREE index is created. It manage data in tree form.

Bitmap Index:-
Bitmap index put on that column which contain low cardinality. Low Cardinality means more duplicate data and less unique data.

Function-Based Index:-
The index expression can be an arithmetic expression or an expression that contains a SQL function, PL/SQL function, package function.

Example of Normal Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(vendor_id);

Example of Bitmap Index:-

CREATE BITMAP INDEX xxc05_emp_gender
ON xxc05_employees(gender);

Example of Functional Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(UPPER(vendor_name));

Advertisements