Access Paths in Oracle

In this Blog we will discuss about various table access methods in oracle.

Access paths are the methods by which Oracle can get at our data
There are three broad categories of access paths
1.The infamous full scan
2.Index accesses
3.Directed access, by hash or ROWID

Table Full scans

Reads every block in a given segment that was used at some point or other (up to High Water Mark)
Can be most efficient access path for “small” tables
Are a very efficient method to read a lot of data, since it employs multi block read. i.e. can read multiple blocks in a single I/O
Can cause significant physical I/O, particularly on larger tables

Consider putting table into KEEP buffer pool in case frequently used table. Because a full scan put the blocks in the LRU end unlike other access methods.
There is a common misconception that if you have a full scan then avoid it. Please note full scans are not evil always
ROWID access

ROWIDs in Oracle are physical addresses of data. A ROWID contains information about a row regarding the object_id,relative file number, block, and row on the block
Its 18 char long (OOOOOOFFFBBBBBBRRR)
If you have a ROWID, getting to the row is simple. Oracle just reads the file/block and goes to the row on the block as directed by the ROWID
The fastest way to a particular row
Select * from emp where ROWID=‘AAAHbHAABAAAMVqAAB’
Using ROWID ranges, you can parallelize any operations against a single table easily,
just by running four copies of your stored procedure, each working against a different slice of table

Index scans

The most familiar access path
There are different types of index scans
1. Index-Unique Scans
2. Index-Range Scans
3. Index-Skip Scans
4. Index Full Scans
5. Index Fast-Full Scans
6. Index Joins

Index-Unique Scans

Equality predicate on unique or primary key column(s)
Generally considered most efficient access path
Usually not more than 3-4 buffer gets
If table is “small”, FULL TABLE SCAN could be cheaper
Select * from emp where emp_no=1234 ( emp_no is having a primary key index)

Index-Range Scans

Equality predicate on non-unique index, incompletely specified unique index, or range predicate on unique index
When you search from 20 to 300 it searches for 20 (index lookup) and move horizontally until it finds 300
Be careful of the size of the range
Large ranges could amount to huge number of buffer gets
If so, consider a FAST FULL INDEX SCAN or FULL TABLE SCAN
Index range scan can be performed in both directions
Select empno from emp where empno<5000>

Index-Skip Scans

Normally, in order for an index to be used, the columns defined on the leading edge of the index would be referenced in the query
In Oracle 9i, however, this behavior is radically modified. It employs index-skip scan in case optimizer finds it less costly
The database pretends the index is really N little index structure hidden inside one big one
Lets take an example to explain
We have a table T with columns (A,B,C,D,E,F,G,H,I)
We have an index on the table T for columns (A,B,C)
The column A is having three distinct values 1,2,3
We fired the query select * from T where B=1 and C=1

Index Full Scans

Does not read every block in the index structure, contrary to what its name suggests
An index full scan processes all of the leaf blocks of an index, but only enough of the branch blocks to find the first leaf block
Provides ordered output, can be used to avoid sorts for ORDER BY clauses that specify index column order
It uses single block I/O to read the index in order.
Select empno, ename from emp order by empno