优化器何时采用全表扫描

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值