Indexing in Oracle

Ever wondered what Indexing actually does :)

An index is a performance tuning mechanism that allows faster retreival of records.

Searching an indexed table is always faster than a normal table since indexing keeps the records sorted (B-Tree).

In normal search oracle has to scan the entire table, but in an indexed table there is no need to scan the entire table since the records are already sorted.

Index on Single column ->

CREATE INDEX PIndex ON TableName (ColumnName)

Index on multiple columns->

CREATE INDEX PIndex ON TableName (ColumnOne, ColumnTwo)

Following query fetches only the user defined Indexes->

SELECT

i.INDEX_NAME, i.TABLE_NAME, ic.COLUMN_NAME, ic.COLUMN_POSITION, UNIQUENESS

FROM

USER_INDEXES i JOIN USER_IND_COLUMNS ic ON i.INDEX_NAME = ic.INDEX_NAME

WHERE

i.TABLE_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='CITI_AMWS_CLUSTER_162');

Above you need to enter OWNER = schemaName.

To fetch all available Indexes use this ->

SELECT * FROM ALL_INDEXES;

Following DDL statement can be used to rebuild in Indexes.

ALTER INDEX index_name REBUILD;


Drop Index ->

DROP INDEX index_name;