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

jimmylee

最初由 biti_rainy 发布
[B]你先测试了
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? [/B]

sorry，我只是比较着急又不知道怎么办。非常感谢你的解答。
我试了一下，速度主要慢在上述语句中，基本上占了90%以上的时间。现在的情况是这样的，MONI_AREA 、MONI_TYPEID 、 MONI_FLAG 目前的测试数据都是一样的，有没有索引都是一样的，有了索引更慢，目前我只建立了moni_time的索引，以上语句的执行速度是9秒多，如果去掉where语句中的moni_area,moni_typeid,moni_flag速度变为3秒多。按照你的做法create index index2 on cstmonilog(moni_time,moni_area,moni_typeid,moni_flag).执行速度还是9秒多。执行计划中的最后一步都是INDEX (FULL SCAN) OF 'INDEX1' (NON-UNIQUE)或INDEX (FULL SCAN) OF 'INDEX2' (NON-UNIQUE)。应该如何建索引确实是问题的关键。

jimmylee

我突然发现
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
有问题，应改为
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'
order by MONI_TIME) where rno<990035
rNo >=990000
否则又和rejoice999指出的错误一样。以上更正对效率又影响，处理时间变长。但应该这样更正。

4pal

嗯，打断一下，看你们这么起劲
2个问题

1）各位怎么没有分析一下 楼主的 执行计划？ 我没怎么弄懂

2） 各位后来写的sql语句都用上了hint，我想 作为dba的朋友，肯定是不赞成这么做的，你们是不是有意在 回避  oracle的rbo cbo？

其实，这2个问题同一个用意，就是优化器的运行，各位分析一下？

biti_rainy

请尝试  row_number()  over()  这个分析函数

关于这个函数，坛子上我也已经交代过多次了

jimmylee

最初由 biti_rainy 发布
[B]请尝试  row_number()  over()  这个分析函数

关于这个函数，坛子上我也已经交代过多次了 [/B]

在当前的例子中如何使用呢？
我能想到的是如下两种情况：
select * from
(
select rowid rID, row_number() over(order by MONI_TIME) rno,MONI_TIME,MONI_TYPE,MONI_APERRCODE,MONI_CONSTANT,SubStr(MONI_USERINFO,1,50) MONI_USERINFO from cstmonilog2 where MONI_AREA = :MoniAreaNo and MONI_TYPEID = :MoniTypeID and MONI_FLAG = '1'
) T1
where T1.rNo between 900000 and 900035

select rowid rid,   MONI_TIME,MONI_TYPE,MONI_APERRCODE,MONI_CONSTANT,
SubStr(MONI_USERINFO,1,50) MONI_USERINFO
from cstmonilog2
where rowid in
(
select rid from (select row_number() over(order by moni_time) rno,rowid rid
from cstmonilog2 where MONI_AREA = '9' and MONI_TYPEID = '1' and MONI_FLAG = '1'
) where rno between 900000 and 900035

但效率都很低。能否列出在当前情况下如何使用row_number() over()。

jimmylee

对不起，上边最后一个语句的最后少了一个括号。

biti_rainy

事实上
这么多页的翻页，是不太可能有人不停的翻的

如果结果集这么大，那肯定是比较极端的情况
你可以在前台捕获页数来计算，如果在记录靠后部分则降排序取(m,n)
若靠前则升序取靠前的

如果是中间的页数那是没有办法的，但总比一种排序规则好啊

BTW:关于row_number()  over() 的使用
以及所有需要注意的问题
大家已经讨论的差不多了
关键在于你自己组合测试了

1：利用良好的索引
2：避免内层过大的io，可以考虑拆表或者只扫描索引字段，或者iot
3：避免使用in，考虑表连接，表连接考虑索引字段来连接

lwxian

最初由 jimmylee 发布
[B]我突然发现
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
有问题，应改为
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'
order by MONI_TIME) where rno<990035
rNo >=990000
否则又和rejoice999指出的错误一样。以上更正对效率又影响，处理时间变长。但应该这样更正。 [/B]

这种做法应该没有问题，前提四我那个索引，因为索引已经按时间排序！我的类似应用采用这种方式也没有问题！

不知jimmylee测试没有，是不是正确！

jimmylee

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

现在我才明白，加问号的地方是并不矛盾的，order b y 是为了使用索引。

jimmylee

感谢各位，特别是biti_rainy、lwxian、hahaer和rejoice999等的热心
帮助，经过大家帮助，我将sql语句改为如下，创建moni_time字段的索引，显示的结果正确，且响应的效率在100万条的数据中的大约在10秒中左右,10万条在2~3秒，基本满足了需要。再一次对大家的帮助表示感谢！I Love ITPUB!

create index ind_cstmonilog_monitime  on cstmonilog(moni_time);
select a.rowid , a.MONI_TIME,a.MONI_TYPE,a.MONI_APERRCODE,a.MONI_CONSTANT,
SubStr(a.MONI_USERINFO,1,50) MONI_USERINFO
from cstmonilog a ,
(
  select rid from
  (
    select rownum rno,rowid rid from
    (
          select rowid
      from cstmonilog
      where MONI_AREA = '9' and MONI_TYPEID = '1' and MONI_FLAG = '1'
      order by MONI_TIME
    )
  )
  where rNo between 900000 and 900035
) b
where a.rowid=b.rid
order by a.moni_time;