Indexes | Oracle DBA Tutorial pdf

Indexes

Oracle has mainly 2 types of indexes, B+ Tree and Bitmap.

Creating index

Normal B tree index
CREATE INDEX index_name ON table_name (column_names) TABLESPACE app_indx
Bitmap index
CREATE BITMAP INDEX emp_gender_idx ON employee (sex) TABLESPACE app_indx
Reverse key index
CREATE INDEX index_name ON table_name (column_names) REVERSE
Function based index
CREATE INDEX index_name ON UPPER(product (prodname))
To use function based index, set following parameters in initialization file
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
Cost based optimizer must be used.

Index Oraganized Table (IOT)

Create the table normally, with ORGANIZATION INDEX keyword. It is suitable when data access is mostly thru primary key. In IOT, rows are physically stored in sorted order of the primary key.
Create table MANUFACTURER
(
MFDNO NUMBER not null,
MFDNAME VARCHAR2(200) not null,
ADDRESS VARCHAR2(200),
CITY VARCHAR2(50),
STATE CHAR(2),
COUNTRY VARCHAR2(100),
POSTCODE VARCHAR2(10),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(100),
USERNAME VARCHAR2(20) default USER,
DATESTAMP DATE default SYSDATE
) tablespace SUPERMARKET_DATA
ORAGANIZATION INDEX
OVERFLOW TABLESPACE ovfl_tblsp
INCLUDING address
PCTTHRESHOLD 25
MAPPING TABLE 

Rebuilding index

ALTER INDEX pk_customer REBUILD ONLINE DROP INDEX pk_customer
You can also move index to a different table space using ALTER INDEX
index_name TABLESPACE new_table_space command.

 Monitoring index usage

ALTER INDEX index MONITORING USAGE
The V$OBJECT_USAGE view populated with index usage information.
ALTER INDEX index NOMONITORING USAGE

 Index related data dictionary views

DBA_INDEXES
DBA_IND_COLUMNS