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

oracle问题集

正如楼上某哥说的，你index2不建也罢。另外，从顶楼的贴子看来，执行计划与所建INDEX不一至。第一个应该是用inex1,第二应该用INDEX2。兄弟提个建议，各位如果有条件，请尽量原模原样COPY你在SQLPLUS 
试验的情况。至于你为什么会加了INDEX而慢，你看看你用的是什么optimizer mode 
就可以明白了。从你的语句看，我猜你的DB是8。1。5以上版本，建议采用COST OPTIMIZER。
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 biti_rainy 于 02-08-01 03:40 发表: 
呵呵
反正我以前在50万记录中的测试
翻任意一页，都可以控制在3秒以内
__________________
I love oracle
But i hate IT

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


由 hahaer 于 02-08-01 03:44 发表: 
哥们，你是怎么做的？ my method is limited. I can ensure the sql I supplied can return the 
result very quicky if the MoniNoLast I specified is less than 5000 or a little 
more, but it will become slower and slower when the MoniNoLast get closed to the 
total number of rows in the table.
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 jimmylee 于 02-08-01 03:57 发表: 
Re: 呵呵
  quote:

  最初由 biti_rainy 发布
  反正我以前在50万记录中的测试
  翻任意一页，都可以控制在3秒以内 




能否将你的做法共享出来，大家学学。


由 biti_rainy 于 02-08-01 04:10 发表: 
没什么独特的，呵呵
忘了交代一个前提
那就是：

表的行记录不大，反正肯定不超过100字节
所以记录和索引全部都装在内存里面啦
__________________
I love oracle
But i hate IT

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


由 jimmylee 于 02-08-01 04:13 发表: 
我的表肯定不行，每条记录的长度理论的大小将近8K.


由 jimmylee 于 02-08-01 04:15 发表: 
我测试的结果lwxian的语句速度是最快的。能否给解释一下为什么？


由 biti_rainy 于 02-08-01 04:31 发表: 
你注意他的这个地方
select rownum rno,rowid /*+ INDEX(cstmonilog index2 ) */ rid from 

还要注意在这一层的where条件中的所有字段都包含在索引中！

这是什么意思，表示所有字段和rowid都从索引里面来，而不用进行表扫描
通过索引确定了你要选取的记录的位置
然后再通过rowid去扫描表

因索引中只包含部分字段，内层使用的内存就少了

因为进行表扫描的时候不管你选取所有字段还是一个字段，都是把所有列读进内存的！
__________________
I love oracle
But i hate IT

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


由 biti_rainy 于 02-08-01 04:36 发表: 
这个现象也说明一个问题
那就是你的瓶颈在内存和io上

这样的话，你可以考虑把你的要排序的和内层的wher中字段单独放在一个表中
通过这些小表确定记录集
然后再通过这个结果集去大表中查询

本来想用 IOT的，一样可缓解楼上的所说的问题，但考虑到它限制了其他索引的存在，就算了
__________________
I love oracle
But i hate IT

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


由 jimmylee 于 02-08-01 04:51 发表: 
Re: 你注意他的这个地方
  quote:

  最初由 biti_rainy 发布
  select rownum rno,rowid /*+ INDEX(cstmonilog index2 ) */ rid from 

  还要注意在这一层的where条件中的所有字段都包含在索引中！

  这是什么意思，表示所有字段和rowid都从索引里面来，而不用进行表扫描
  通过索引确定了你要选取的记录的位置
  然后再通过rowid去扫描表

  因索引中只包含部分字段，内层使用的内存就少了

  因为进行表扫描的时候不管你选取所有字段还是一个字段，都是把所有列读进内存的！ 




但是我测试的结果，用index1（moni_time)的结果比index2更快。


由 biti_rainy 于 02-08-01 04:58 发表: 
我只是跟你说理论上怎么来解释这些现象
但事实上
我并没有说他是最好的
因为确实存在一个问题我无法确信：
那就是你order by 的是时间，但where中却没有时间字段
这样让我在考虑这个问题的时候陷入两难境地

如果是我处理这个我问题，我会在索引上做各种组合来测试以确定各种方案中最好的
因为如果排序字段上没有索引，那也将是一件很痛苦的事情
并且我现在不确信能否很好的解决排序与where中的索引字段的矛盾
__________________
I love oracle
But i hate IT

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


由 jimmylee 于 02-08-01 05:00 发表: 
我刚刚测试的结果，lwxian的语句从99万记录中选取35条的速度是1.33秒。下午我执行时没有这么快，可能是数据库的性能的影响。lwxian 
的语句确实使用的索引，有没有索引影响非常之大。我的语句加不加索引结果都一样。
另外我数据库版本8.1.7 for AIX，优化器方式choose。我在sqlplus设定set autotrace on 
后并产生执行计划后，是否优化器采用cbo，造成对速度的影响？


由 biti_rainy 于 02-08-01 05:01 发表: 
需要补充一点的是
因为较早版本的一个查询中，在一个表中，oracle将只选择一个索引

不知道在9i中由于跳跃式扫描的索引的特性的存在
会否解决你的这个问题
__________________
I love oracle
But i hate IT

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


由 jimmylee 于 02-08-01 05:09 发表: 
Re: 我只是跟你说理论上怎么来解释这些现象
  quote:

  最初由 biti_rainy 发布
  但事实上
  我并没有说他是最好的
  因为确实存在一个问题我无法确信：
  那就是你order by 的是时间，但where中却没有时间字段
  这样让我在考虑这个问题的时候陷入两难境地

  如果是我处理这个我问题，我会在索引上做各种组合来测试以确定各种方案中最好的
  因为如果排序字段上没有索引，那也将是一件很痛苦的事情
  并且我现在不确信能否很好的解决排序与where中的索引字段的矛盾 



非常感谢你的回答，在这种情况下建什么样的索引确实是非常为难的事情。你认为可能会用到那些字段的索引？
另外需要说明的是lwxian 
的算法的速度与搜索行（rownum)的区间位置关系较大，如行数靠前速度非常快，如果行号靠后则速度明显下降，响应时间基本与行的位置成正比，不知道怎么改进。但我认为lwxian的算法确实是比较好的算法之一，尽管看起来怎么都不象是好的算法。


由 jimmylee 于 02-08-01 05:11 发表: 
Re: 需要补充一点的是
  quote:

  最初由 biti_rainy 发布
  因为一个查询中，在一个表中，oracle将只选择一个索引

  不知道在9i中由于跳跃式扫描的索引的特性的存在
  会否解决你的这个问题 



对于钳套查询不能有一个以上的索引吗？


由 lwxian 于 02-08-01 05:25 发表: 
按你目前的数据分布情况，同一条件的几百万，这样每一查询返回数据量太大！
要提高速度，建议在表结构设计上下功夫！

刚才我那条ＳＱＬ也有一些问题，就是分页越到后面越慢！因为越到后面，要求读取的
数据越多！
__________________
^^AHA^^


由 biti_rainy 于 02-08-01 05:27 发表: 
给你一点建议：
把你的这个查询拆开

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

或者：利用这个集合做驱动表跟表做表连接（不使用in）
__________________
I love oracle
But i hate IT

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


由 lwxian 于 02-08-01 05:35 发表: 
Re: Re: 你注意他的这个地方
  quote:

  最初由 jimmylee 发布


  但是我测试的结果，用index1（moni_time)的结果比index2更快。 





因为你目前r的where条件相当于没有，所有数据都是相同的！
所有第二个索引中前面三个字段等于没有用处，白白占用多余空间！
__________________
^^AHA^^


由 biti_rainy 于 02-08-01 16:34 发表: 
按照我上面说的拆开查询的建议
你使用游标试一下
？
__________________
I love oracle
But i hate IT

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


由 yanwang 于 02-08-01 17:12 发表: 
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 于 02-08-01 17:47 发表: 
Re: 给你一点建议：
  quote:

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

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

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




我按照你的建议，将语句改为：
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 于 02-08-01 17:49 发表: 
  quote:

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




是的，用cbo挺费神的。


由 biti_rainy 于 02-08-01 17:53 发表: 
o
关键在于这个：
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 或者 索引去确定记录）
__________________
I love oracle
But i hate IT

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


由 jimmylee 于 02-08-01 18:08 发表: 
是的，如何改进呢?


由 hahaer 于 02-08-01 18:13 发表: 
哎，连hints的位置都不对。跟别说~~~~~~~~~~
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 biti_rainy 于 02-08-01 18:19 发表: 
什么如何改进？
你先测试了
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?
__________________
I love oracle
But i hate IT

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


由 biti_rainy 于 02-08-01 18:33 发表: 
呵呵
事实上我确实没有去核实 hints 这样写是否是正确的
因为别人写了，而主人又说见着了效果，那我姑且就认为是对的了

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

也可以参考 IOT 部分内容

最根本的目的，就是确定你翻页的记录的时候，不把所有内容扫入内存
__________________
I love oracle
But i hate IT

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


由 hahaer 于 02-08-01 18:47 发表: 
Re: Re: 给你一点建议：


  quote:

  最初由 jimmylee 发布


  我按照你的建议，将语句改为：
  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'