一个SQL 走全表扫描的原因，求解？

foxhuntwang

表 CC_EVENT  其中  EVENT_ID 是其主键，主键索引 PK_CC_EVENT，无其他索引
SQL语句 ：

SELECT EVENT_ID
           FROM (SELECT EVENT_ID
                   FROM CC_EVENT
                  WHERE STATE = 'A'
                  ORDER BY EVENT_ID)
          WHERE ROWNUM < 3
它的执行计划为：


在这个SQL 外再加一层查询：

SELECT T1.EVENT_ID,
        CC.EVENT_FORMAT_ID,
        CC.SUBS_ID,
        CC.PREFIX,
        CC.ACC_NBR,
        CC.CREATED_DATE,
        CC.STATE,
        CC.STATE_DATE,
        CC.EVENT_PARAM,
        CC.COMMENTS,
        CC.SP_ID
   FROM (SELECT EVENT_ID
           FROM (SELECT EVENT_ID
                   FROM CC_EVENT
                  WHERE STATE = 'A'
                  ORDER BY EVENT_ID)
          WHERE ROWNUM < 3) T1,
        CC_EVENT CC WHERE T1.EVENT_ID = CC.EVENT_ID

SQL 执行计划为：


疑惑的地方 ，为什么外面加了一层后，SELECT EVENT_ID
           FROM (SELECT EVENT_ID
                   FROM CC_EVENT
                  WHERE STATE = 'A'
                  ORDER BY EVENT_ID)
          WHERE ROWNUM < 3  不走索引了， 而是走了全表扫描 还多做了一步 排序操作。十分不解，求解。

Maclean Liu(刘相兵

1.

SELECT T1.EVENT_ID,
        CC.EVENT_FORMAT_ID,
        CC.SUBS_ID,
        CC.PREFIX,
        CC.ACC_NBR,
        CC.CREATED_DATE,
        CC.STATE,
        CC.STATE_DATE,
        CC.EVENT_PARAM,
        CC.COMMENTS,
        CC.SP_ID
   FROM (SELECT /*+ unnest */  EVENT_ID
           FROM (SELECT EVENT_ID
                   FROM CC_EVENT
                  WHERE STATE = 'A'
                  ORDER BY EVENT_ID)
          WHERE ROWNUM < 3) T1,
        CC_EVENT CC WHERE T1.EVENT_ID = CC.EVENT_ID

给出上述语句的执行计划


2. 给出 你前后2个语句的10053 level 1 trace

foxhuntwang

Maclean Liu(刘相兵 发表于 2013-3-20 23:31
1.

SELECT T1.EVENT_ID,

1：刘大 加了 hint 后 执行计划没有改变 和没加是一样的。

2： trc 文件 ： gtsc_ora_27656280.txt (60.31 KB, 下载次数: 1203)

2013-3-21 11:17 上传

点击文件名下载附件

  是  第一个SQL 的10053 trc

       gtsc_ora_23396524.txt (16.19 KB, 下载次数: 1472)

2013-3-21 11:17 上传

点击文件名下载附件

是 加了 一层查询后的 10053 trc 文件

warmbreeze

几乎所有的记录STATE = 'A'
第一个sql的index full scan只需要读前面几个block就可以满足rownum<3了

warmbreeze

这应该是stopkey优化的结果
貌似有表连接以后就不能用类似的stopkey优化了，就是第二个sql的table acces full

foxhuntwang

warmbreeze 发表于 2013-3-21 12:20
这应该是stopkey优化的结果
貌似有表连接以后就不能用类似的stopkey优化了，就是第二个sql的table acces fu ...

EVENT_ID  有主键索引啊，按道理 不应该走 全表扫描再排序啊，索引本身就是排序好的。走全表扫描 再排序 不是 多此一举吗？

dla001

foxhuntwang 发表于 2013-3-21 13:51
EVENT_ID  有主键索引啊，按道理 不应该走 全表扫描再排序啊，索引本身就是排序好的。走全表扫描 再排序  ...

这么想是错的：
例子：
表有很多数据,索引是按event_id主键排序的。表中只有小部分数据是=‘A’（假设2条）。
如果使用索引，那么它要从索引读一条，然后回表，看是否为A，反复做这个操作，在所有的索引记录都扫描完毕，并且回表后，最后才知道结果。如果是这样话，还不如直接全表扫描后，排序来的快。

foxhuntwang

dla001 发表于 2013-3-21 14:08
这么想是错的：
例子：
表有很多数据,索引是按event_id主键排序的。表中只有小部分数据是=‘A’（假设2条 ...

恩，这个解释比较合理，但是为啥 第一个SQL 就走索引呢 ？ 由于我是做测试的 ，所以所有记录的 STATE 都是 A 。

icnm

问题出在sort order by stopkey上面，建议把rownum提出来

dla001

foxhuntwang 发表于 2013-3-21 14:37
恩，这个解释比较合理，但是为啥 第一个SQL 就走索引呢 ？ 由于我是做测试的 ，所以所有记录的 STATE 都 ...

对于这个SQL来看，无论是使用索引还是不使用索引，都不会感到奇怪。
这种stopkey的查询，oracle已经很做的很好了，但不代表它完美。
在我们用的环境中(11.2.0.3.3)，一个很长的stopkey的查询，就出现了因CFB而导致
oracle重新生成了一个烂的执行计划。 对于这种SQL，人为干预是有好处的。

对于你的这个SQL，一般我们会做如下处理。
建立 (state,event_id)索引
或使用hint使子查询强制走索引

bjup

加个索引提示，可以让其走索引
SELECT
t1.object_id, cc.owner, cc.object_name, cc.object_type, cc.last_ddl_time, cc.status, cc.generated
  FROM (SELECT object_id
          FROM (SELECT /*+ index(obj ind_object_id)*/object_id
                  FROM obj
                 WHERE status = 'VALID'
                 ORDER BY object_id)
         WHERE ROWNUM < 3) T1,
       obj CC
WHERE T1.object_id = CC.object_id

warmbreeze

虽然计划里是table access full , 但是实际的consistent read 要比"table access full"小很多，还是走了rownum优化

Maclean Liu(刘相兵

执行计划A 你说的全表扫描 成本cost 500多
你加了HINT的 查询 成本1000多

这是CBo为什么选择 执行计划 A。  CBO是怎么算成本的又是一回事了， 未必成本低的是 好的执行计划，但CBO就是根据成本去计算的。


请给出 这2个执行计划的 运行时间和 逻辑读、物理读等对比。

Stone

在这个SQL 外再加一层查询：

SELECT T1.EVENT_ID,
        CC.EVENT_FORMAT_ID,
        CC.SUBS_ID,
        CC.PREFIX,
        CC.ACC_NBR,
        CC.CREATED_DATE,
        CC.STATE,
        CC.STATE_DATE,
        CC.EVENT_PARAM,
        CC.COMMENTS,
        CC.SP_ID
   FROM (SELECT EVENT_ID
           FROM (SELECT EVENT_ID
                   FROM CC_EVENT
                  WHERE STATE = 'A'
                  ORDER BY EVENT_ID)
          WHERE ROWNUM < 3) T1,
        CC_EVENT CC WHERE T1.EVENT_ID = CC.EVENT_ID

如果不影响业务的话，这个查询是不是可以改下比较好呐 :)

1. SELECT *
   
2.         FROM   (SELECT event_id
   
3.                        event_format_id,
   
4.                        subs_id,
   
5.                        prefix,
   
6.                        acc_nbr,
   
7.                        created_date,
   
8.                        state,
   
9.                        state_date,
   
10.                        event_param,
    
11.                        comments,
    
12.                        sp_id
    
13.                 FROM   cc_event
    
14.                 WHERE  state = 'A'
    
15.                 ORDER  BY event_id)
    
16.         WHERE  ROWNUM < 3;

复制代码

另外关于“sort order by stopkey”，我的理解：应该不是影响因素，而是在全表扫描下Oracle一种更快的处理Top N选择。详细的解释可以参考：http://betteratoracle.com/posts/18

SORT ORDER BY STOPKEY

In the top N query explain plan, I highlighted the sort operation changing from 'SORT ORDER BY' to 'SORT ORDER BY STOPKEY'. This is a special sort operation that Oracle uses to make top N queries more efficient.

When you add a rownum filter, Oracle knows the query will return at most N rows, it doesn't need to do a full sort of the data, it only needs to remember the top (or bottom) N results of the sort.

Conceptually, Oracle will allocate N slots for the results.

For each row read, it will check if it sorts higher than the N results it is holding, if it does the record in the lowest position gets pushed out, and the new record is slotted into the correct space. This is much more efficient than sorting potentially millions of rows when only 10 are needed.

foxhuntwang

dla001 发表于 2013-3-21 15:03
对于这个SQL来看，无论是使用索引还是不使用索引，都不会感到奇怪。
这种stopkey的查询，oracle已经很做 ...

恩，我测试了一下，在 EVENT_ID,STATE 上建索引后，执行计划都走索引了，还是 STATE='A' 这个条件 影响的