What and when oracle use Full Table Scans in explain plans

Full Table Scans
-------------------------------
•Full table scan reads all rows from a table.

•After scanning all rows then it filters out those that do not meet the selection criteria.

•Actually full table scan scans all blocks in the table that are under the high water mark. The high water mark indicates the amount of used space, or space that had been formatted to receive data.

•During full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently.

•Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. This is because full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.

In the following section it is said when optimizer decides full table scans.

When the Optimizer Uses Full Table Scans
-----------------------------------------------------
1)Lack of Index: If the query is unable to use any existing indexes, then it uses a full table scan. For example, if there is a function used on the indexed column in the query, the optimizer is unable to use the index and instead uses a full table scan.

2)Large Amount of Data: If the optimizer thinks that the query will access most of the blocks in the table, then it uses a full table scan, even though indexes might be available.

3)Small Table: If a table contains less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, which can be read in a single I/O call, then a full table scan might be cheaper than an index range scan, regardless of the fraction of tables being accessed or indexes present.

4)High Degree of Parallelism:A high degree of parallelism for a table skews the optimizer toward full table scans over range scans. Examine the DEGREE column in ALL_TABLES for the table to determine the degree of parallelism.

To perform full table scan use, FULL hints, like
SQL> select /*+ FULL(TEST_xplan)*/ * from test_xplan where a=1;


Related Documents:
------------------------------
How does one prevent Oracle from using index
Use of Hint to use index