优化器何时采用全表扫描

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

When the Optimizer Uses Full Table Scans

The optimizer uses a full table scan in any of the following cases:

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 as in Example 1-5.

Example 1-5 Full Table Scan

SELECT last_name, first_name
  FROM employees
 WHERE UPPER(last_name) LIKE :b1

If you need to use the index for case-independent searches, then either do not permit mixed-case data in the search columns or create a function-based index, such as UPPER(last_name), on the search column.

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

以上来自于oracle官方文档，链接

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#35891

全表扫描产生于以下任意情况

1、当查询缺少索引时

包括查询不使用索引，或者索引列上使用了函数。

例：索引列上使用round 函数

SQL> select id,content from td_question_content where round(id,0)=88104；
已用时间:  00: 00: 11.05
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7815 Card=3718 Bytes
          =6625476)

   1    0   TABLE ACCESS (FULL) OF 'TD_QUESTION_CONTENT' (Cost=7815 Ca
          rd=3718 Bytes=6625476)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      81261  consistent gets
      81217  physical reads
          0  redo size
       1182  bytes sent via SQL*Net to client
        796  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

另外如查询时只查询索引列，则以上情况不成立。

例：

SQL> select id from td_question_content where round(id,0)=88104;
已用时间:  00: 00: 00.01
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=3718 Bytes=1
          4872)

   1    0   INDEX (FAST FULL SCAN) OF 'INDEX_QUESTION_CONTENT_TEMP' (N
          ON-UNIQUE) (Cost=83 Card=3718 Bytes=14872)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        830  consistent gets
          0  physical reads
          0  redo size
        377  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

由上可看出，使用的是索引全局快速扫描。

2、