Friday, August 19, 2011

Index and Explain Plan

What exactly an index is?

An oracle Index is just like the index we see at the end of each book, which is an alphabetically sorted list of topics, along with the page numbers which tells where they are in the book. Similarly, an Oracle index stores the sorted list of indexed column, along with their rowid. Rowid tells the physical address of the row.

What is an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block.

Logically Oracle finds the data to read by using the following methods:

Full Table Scan (FTS)
Index Lookup (unique & non-unique)