为什么加上索引速度奇慢无比？

yanwang

From your explain plan, I can tell that you did not analyze your index since there's no cost.  It used RBO, not CBO.  

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 COUNT
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CSTMONILOG'
4 3 INDEX (FULL SCAN) OF 'INDEX_CSTMOINILOG_MONITIME' (N
ON-UNIQUE)

jimmylee

最初由 biti_rainy 发布
[B]把你的这个查询拆开

利用他的这个办法把 rowid  的集合（这个集合可以顺便把索引字段一起取出来）取出来，比如30条
不使用in
1：
然后用rowid做条件循环30次取30条记录
2：或者循环，利用取出的索引字段然后循环取30条记录，根据索引条件

或者：利用这个集合做驱动表跟表做表连接（不使用in） [/B]

我按照你的建议，将语句改为：
select a.rowid , a.MONI_TIME,a.MONI_TYPE,a.MONI_APERRCODE,a.MONI_CONSTANT,SubStr(a.MONI_USERINFO,1,50) MONI_USERINFO
from cstmonilog2 a ,(select rid from (select rownum rno,rowid /*+ INDEX(cstmonilog2 index1 ) */ rid
  from cstmonilog2 where MONI_AREA = '9' and MONI_TYPEID = '1' and MONI_FLAG = '1'
  and rownum <990035
  order by MONI_TIME) where rNo >=990000) b
  where a.rowid=b.rid
  order by a.moni_time
不知道是否正确。这个语句与lwxian的执行的时间基本一样，相互差别不超过20ms。所以没有明显的改善。另外你说的cursor方式如何去做？如何对最终的结果集排序？

jimmylee

最初由 yanwang 发布
[B]From your explain plan, I can tell that you did not analyze your index since there's no cost.  It used RBO, not CBO.  
[/B]

是的，用cbo挺费神的。

biti_rainy

关键在于这个：
select rid from (select rownum rno,rowid /*+ INDEX(cstmonilog2 index1 ) */ rid
from cstmonilog2 where MONI_AREA = '9' and MONI_TYPEID = '1' and MONI_FLAG = '1'
and rownum <990035
order by MONI_TIME) where rNo >=990000

这一步的时间很重要
如果这个时间长，那就没有办法了！
在这一步里面去调整索引和一些东西，如果这个结果返回的快，那么就有戏

因为毕竟这里返回的结果集很有限
然后根据这个结果集为游标，用这个结果集里面的有限的值（rowid  或者  索引字段）

然后循环单条查询基表（这个时候可通过rowid 或者 索引去确定记录）

jimmylee

是的，如何改进呢?

哎，连hints的位置都不对。跟别说~~~~~~~~~~

biti_rainy

你先测试了
select rid from (select rownum rno,rowid /*+ INDEX(cstmonilog2 index1 ) */ rid
from cstmonilog2 where MONI_AREA = '9' and MONI_TYPEID = '1' and MONI_FLAG = '1'
and rownum <990035
order by MONI_TIME) where rNo >=990000
很慢？
如果这个很慢
MONI_AREA  的不同值有多少？
如果不同值很少
建议把时间字段放在索引的最前面试一下

把信息交代清楚一点，不要交互次数太多,  ok?

biti_rainy

事实上我确实没有去核实  hints 这样写是否是正确的
因为别人写了，而主人又说见着了效果，那我姑且就认为是对的了

如果这样的话
可以考虑把该表分开存储，有关索引和排序部分的拆成小的存储

也可以参考 IOT 部分内容

最根本的目的，就是确定你翻页的记录的时候，不把所有内容扫入内存

最初由 jimmylee 发布
[B]

我按照你的建议，将语句改为：
select a.rowid , a.MONI_TIME,a.MONI_TYPE,a.MONI_APERRCODE,a.MONI_CONSTANT,
        SubStr(a.MONI_USERINFO,1,50) MONI_USERINFO
   from cstmonilog2 a ,
       (select rid from ( select rownum rno,rowid /*+ INDEX(cstmonilog2 index1 ) */ rid
                            from cstmonilog2
                            where MONI_AREA = '9'
                              and MONI_TYPEID = '1' and MONI_FLAG = '1'                                
                              and rownum <990035                                              -- 1
                              order by MONI_TIME )                                            -- 1  
          where rNo >=990000) b
  where a.rowid=b.rid
  order by a.moni_time
不知道是否正确。这个语句与lwxian的执行的时间基本一样，相互差别不超过20ms。所以没有明显的改善。另外你说的cursor方式如何去做？如何对最终的结果集排序？ [/B]

The Statement given above has 4 problems at least:
1. the resultset is not what you want:  the line mared with '--1' shows that the statement will cut down the result set to 990035 before order them. the resultset from this statement is uncertain. It will be diffrent according the indexes used or something else. Is it you want? I doubt.
2. the hints location is not proper. it should only be setted after select/update/insert directly except push_predicates. However, in this problem, it will use the index1 even you don't have hints in the subquery.
3. this statement added more logic while it actually not need. NESTED LOOP
4. why don't u use CBO. it should work much smarter in you problem.


but I aggree this statement has improved, it at lease omit the unnessary unique operation when using IN.

biti_rainy

还是楼上的仔细  

我根本就没有注意sql的逻辑上的问题
只是个人根本不关心这个问题，而只是关心了怎么才能提高速度

事实上使用 rownum 肯定需要三层的 inline view
只有使用 row_number()  over() 才能在两层里面解决问题，但只有816以上版本才支持
我想我只是建议了思路，所有的sql都是从原文copy过来的