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

oracle问题集

  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方式如何去做？如何对最终的结果集排序？ 





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.
 
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 biti_rainy 于 02-08-01 18:57 发表: 
:)
还是楼上的仔细 

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

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

oracle文档
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage?remark=tahiti


由 jimmylee 于 02-08-01 18:59 发表: 
Re: 什么如何改进？
  quote:

  最初由 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? 




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 于 02-08-01 19:15 发表: 
我突然发现
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 于 02-08-01 19:39 发表: 
嗯，打断一下，看你们这么起劲
2个问题

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

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

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


由 biti_rainy 于 02-08-01 19:47 发表: 
你如果数据库是816以上版本
请尝试 row_number() over() 这个分析函数

关于这个函数，坛子上我也已经交代过多次了
__________________
I love oracle
But i hate IT

oracle文档
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage?remark=tahiti


由 jimmylee 于 02-08-01 20:38 发表: 
  quote:

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

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



在当前的例子中如何使用呢？
我能想到的是如下两种情况：
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 于 02-08-01 20:40 发表: 
对不起，上边最后一个语句的最后少了一个括号。


由 biti_rainy 于 02-08-01 21:27 发表: 
给你一点建议
事实上
这么多页的翻页，是不太可能有人不停的翻的

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

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

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

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

oracle文档
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage?remark=tahiti


由 lwxian 于 02-08-01 22:10 发表: 
  quote:

  最初由 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指出的错误一样。以上更正对效率又影响，处理时间变长。但应该这样更正。 





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

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


由 jimmylee 于 02-08-01 23:36 发表: 
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 于 02-08-02 01:51 发表: 
感谢各位，特别是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;


由 yyy 于 02-08-02 18:43 发表: 
刚开始，我很不理解，这个语句怎么可能有很好的性能，后来仔细想来得出下面的分析：

上面产生的表b，是取出较少字段的一个“较小”的结果集，而且对于他现在的情况where的三个条件是通过索引还是全表扫描没什么差别，整个语句在两个order by 
中都用到索引。所以，在整个语句中，两个关联表（a、b）中的一个表（b）的记录的数目很少而且每条纪录的大小很小才是性能的关键（当然关联条件是rowid也是关键因素）。

不知我的分析对不对？


由 jimmylee 于 02-08-02 19:59 发表: 
  quote:

  最初由 yyy 发布
  刚开始，我很不理解，这个语句怎么可能有很好的性能，后来仔细想来得出下面的分析：

  上面产生的表b，是取出较少字段的一个“较小”的结果集，而且对于他现在的情况where的三个条件是通过索引还是全表扫描没什么差别，整个语句在两个order 
  by 
  中都用到索引。所以，在整个语句中，两个关联表（a、b）中的一个表（b）的记录的数目很少而且每条纪录的大小很小才是性能的关键（当然关联条件是rowid也是关键因素）。

  不知我的分析对不对？ 




而且用到了rowid索引。


由 hahaer 于 02-08-02 23:28 发表: 
其实这个用ROWID并不是个好主意。参见我的贴子，和我的建议做法。这种情况下应该是用count(stopkey)最棒.
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 yyy 于 02-08-02 23:48 发表: 
你指的是这个吗？：
drop index index1;
drop index index2;

create index index1 on cstmonilog(moni_time) nologing;

set timing on
set autot traceonly

select * from (
select rownum rno, a.*
from 
(select 
MONI_TIME,MONI_TYPE,MONI_APERRCODE,MONI_CONSTANT,SubStr(MONI_USERINFO,1,50) 
MONI_USERINFO 
from cstmonilog 
where MONI_AREA = :MoniAreaNo and MONI_TYPEID = :MoniTypeID and MONI_FLAG = '1' 
order by MONI_TIME) a where rownum <= 20
) where rno >= 5
/

我觉得上面语句最大的问题是最外层select的源可能是一个很大的结果集（比如rownum<=900000）。

还有一点很关键：那个substr运算很影响效率，如果放在最里面，在这种情况下（三个where条件几乎没用），几乎要面对整个表，我想很多时间都用在它上面了。这跟jimmylee的里层只取rowid有很大的差别。

还是请有实验环境的jimmylee说说实验结果（时间）吧。毕竟实践才是检验真理的唯一标准嘛。
顺便请jimmylee实验一下，下面的语句会不会更优化一点：（对你的语句稍微改动了一点点）
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 rowid rid
from cstmonilog 
where MONI_AREA = '9' and MONI_TYPEID = '1' and MONI_FLAG = '1' 
order by MONI_TIME 
) 
where rownum between 900000 and 900035
) b
where a.rowid=b.rid
order by a.moni_time;


另外你说的count(stopkey)是什么？


由 jimmylee 于 02-08-03 00:57 发表: 
  quote:

  最初由 yyy 发布
  你指的是这个吗？：
  where rownum between 900000 and 900035





rownum是不能这样使用的，rownum只支持如下三种情况：
1、rownum=1
2、rownum<number
3、rownum<=number


由 yyy 于 02-08-03 01:28 发表: 
哎呀，低级错误
对了，你对hahaer的语句测试了没有？


由 hahaer 于 02-08-03 02:04 发表: 
是的，我也说了，search more than 5000 （do not ask me why 5000）rows 
以上可能会有问题，而且越到后面会越慢，要是那样，BITY给出了很好的建议，新建一表保存中间结果。等回我有空时会对以上问题作个总结。
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 hahaer 于 02-08-03 08:57 发表: 
I'll emulate the problem discussed here by creating a table T. and the data is 
from all_objects. Just assuming we will browing TABLE T by pages sorted by 
object_id, while rows retrieved out should meet the condition 'object_name < 
'Z', which will ask us to retrieved most rows in table t:

[e-mail]>create table t pctfree 0 nologging
2 as select * from all_objects;

Table created.

[e-mail]>insert /*+append*/ into t select * from t;

6619 rows created.

[e-mail]>commit;

Commit complete.

I repeated the last 2 stats serveral times, so that make the table T large 
enough:

[e-mail]> select count(*) from t;

COUNT(*)
----------
52952

Now, I'll create index on column object_id, and make a query asking for a page 
from 20 through 30:

[e-mail]>create index t_oid_ind on t ( object_id ) pctfree 0 
nologging;

Index created

[e-mail]>col object_name format a30
[e-mail]>col owner format a30
[e-mail]>set autot on
[e-mail]>analyze table t compute satistics for table for all indexes 
for all indexed columns;

Table analyzed.
[e-mail]>l
1 select owner,object_name,object_id
2 from ( select rownum rno,a.*
3 from (select /*+first_rows*/ * from t where object_name < 'Z' order by 
object_id ) a
4 where rownum <= 30
5 )
6* where rno >= 20