----------------------------------------------------------------------------------------
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、