Data retrieval Methods in oracle

Full Table Scan (FTS):

In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it.FTS uses multiblock i/o to read the blocks from disk.

Example FTS explain plan:

SQL> explain plan for select * from dual;

Query Plan

-----------------------------------------

SELECT STATEMENT     [CHOOSE] Cost=

  TABLE ACCESS FULL DUAL

Index lookup:

Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.

There are 4 methods of index lookup:

index unique scan

index range scan

index full scan

index fast full scan

Index unique scan: -

Method for looking up a single key value via a unique index. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index

SQL> explain plan for

select empno,ename from emp where empno=10;

Query Plan

------------------------------------

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANALYZED]

    INDEX UNIQUE SCAN EMP_I1

Index range scan: -

Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between)

SQL> explain plan for select empno,ename from emp

where empno > 7876 order by empno;

Query Plan

-------------------------------------------------------

SELECT STATEMENT   [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANALYZED]

  INDEX RANGE SCAN EMP_I1 [ANALYZED]

Index Full Scan: -

We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

SQL> explain plan for select empno,ename

     from big_emp order by empno,ename;

Query Plan

------------------------------------------------------------

SELECT STATEMENT   [CHOOSE] Cost=26

  INDEX FULL SCAN BE_IX [ANALYZED]

Index Fast Full Scan: -

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query.A fast full scan accesses the data in the index itself, without accessing the table.

SQL> explain plan for select empno,ename from big_emp;

Query Plan

------------------------------------------

SELECT STATEMENT   [CHOOSE] Cost=1

  INDEX FAST FULL SCAN BE_IX [ANALYZED]

RowID: -

This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid

SQL> explain plan for select * from dept where rowid = ':x';

Query Plan

------------------------------------

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID DEPT [ANALYZED]

Enjoy:-)