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

oracle问题集

ITPUB论坛 - 为什么加上索引速度奇慢无比？
ITPUB论坛 (http://www.itpub.net/index.php)
- Oracle数据库管理 (http://www.itpub.net/forumdisplay.php?forumid=2)
-- 为什么加上索引速度奇慢无比？ (http://www.itpub.net/showthread.php?threadid=46457)


由 jimmylee 于 02-07-31 18:28 发表: 
为什么加上索引速度奇慢无比？
这是一个数据库表的翻页查询，查询指定序号的记录
select * from (select rowid rID, rownum 
rNo,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) T1 where T1.rNo between 
:MoniNoFirst and :MoniNoLast 

表中有两个索引 
1、create index index1 on cstmonilog(moni_time)
2、create index index2 on cstmonilog(moni_area,moni_typeid,moni_flag)

数据库中有99万条记录，当存在index2时，上述语句奇慢无比，去掉index2之后速度反而大大加快。需要说明的是目前的记录的moni_area,moni_typeid,moni_flag都是相同的，就是说目前的99万条记录加不加where语句查询的记录集都是一样的（但将来可能不一定）
下面是有和没有index2时的执行计划，请大佬给解释一下为什么并能给提供一个优化的建议.

没有index2索引时的执行计划
Elapsed: 00:00:33.68

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)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21762 consistent gets
8807 physical reads
0 redo size
2418 bytes sent via SQL*Net to client
454 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

有index2索引时的执行计划

Elapsed: 00:07:474.93

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 SORT (ORDER BY)
3 2 COUNT
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CSTMONILOG'
5 4 INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)




Statistics
----------------------------------------------------------
8 recursive calls
73 db block gets
20227 consistent gets
42208 physical reads
0 redo size
2417 bytes sent via SQL*Net to client
454 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
36 rows processed



由 rejoice999 于 02-07-31 18:41 发表: 
先不说速度问题，你这个翻页对吗？


由 porridge 于 02-07-31 18:49 发表: 
我没看语句，不过这个索引好像应该用bitmap类型？


由 jimmylee 于 02-07-31 18:56 发表: 
  quote:

  最初由 rejoice999 发布
  先不说速度问题，你这个翻页对吗？ 




between and确定新的一页的记录范围。那你说翻页应该如何做？


由 rejoice999 于 02-07-31 19:08 发表: 
你那括号里的查询，是先有ROWNUM才进行ORDER BY的，是你想要的结果么？


由 gongjan 于 02-07-31 19:19 发表: 
我觉得是：你的第二个index 是一个组合index,可以试一试分别在三个列上建立index
__________________
====向前进，向前进，我们的队伍向前进！=======


由 jimmylee 于 02-07-31 20:19 发表: 
  quote:

  最初由 rejoice999 发布
  你那括号里的查询，是先有ROWNUM才进行ORDER BY的，是你想要的结果么？ 



sorry，这个语句确实有问题。那正确的语句是不是应该是这样的:

select * from (select rownum rNo, t1.* from (select rowid rID, 
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) T1)T2 where T2.rNo between 
:MoniNoFirst and :MoniNoLast 

钳套两层select，这样效率是否太低,有没有更好的解决办法？


由 jimmylee 于 02-07-31 20:20 发表: 
  quote:

  最初由 gongjan 发布
  我觉得是：你的第二个index 是一个组合index,可以试一试分别在三个列上建立index 



因为是组合使用才使用组合索引呀。


由 jimmylee 于 02-07-31 20:37 发表: 
  quote:

  最初由 jimmylee 发布

  sorry，这个语句确实有问题。那正确的语句是不是应该是这样的:

  select * from (select rownum rNo, t1.* from (select rowid rID, 
  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) T1)T2 where T2.rNo between 
  :MoniNoFirst and :MoniNoLast 

  钳套两层select，这样效率是否太低,有没有更好的解决办法？ 



可以用row_number来完成
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 cstmonilog where MONI_AREA = :MoniAreaNo and MONI_TYPEID = 
:MoniTypeID and MONI_FLAG = '1' ) T1 where T1.rNo between :MoniNoFirst and 
:MoniNoLast


由 rejoice999 于 02-07-31 20:37 发表: 
用analytic function作吧。
select * from
(
select rowid rID, MONI_TIME,MONI_TYPE,MONI_APERRCODE,MONI_CONSTANT,
SubStr(MONI_USERINFO,1,50) MONI_USERINFO, row_number() over(order by MONI_TIME) 
rn
from cstmonilog where MONI_AREA = :MoniAreaNo and MONI_TYPEID = :MoniTypeID and 
MONI_FLAG = '1'
)
where rn between :MoniNoFirst and :MoniNoLast;



  quote:

  最初由 jimmylee 发布

  sorry，这个语句确实有问题。那正确的语句是不是应该是这样的:

  select * from (select rownum rNo, t1.* from (select rowid rID, 
  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) T1)T2 where T2.rNo between 
  :MoniNoFirst and :MoniNoLast 

  钳套两层select，这样效率是否太低,有没有更好的解决办法？ 




由 jimmylee 于 02-07-31 20:57 发表: 
thanks rejoice999 for your help.
但是速度还是非常慢呀，如何解决呢？


由 biti_rainy 于 02-07-31 21:27 发表: 
你数据量有多大？
order by 是否利用了索引
你的整个查询是否合理的利用了索引
这是最关键的两个因素

可能的话
trace 一把，tkprof 看看慢在哪里
__________________
I love oracle
But i hate IT

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


由 lwxian 于 02-07-31 21:58 发表: 
Re: 为什么加上索引速度奇慢无比？
  quote:

  最初由 jimmylee 发布
  需要说明的是目前的记录的moni_area,moni_typeid,moni_flag都是相同的，就是说目前的99万条记录加不加where语句查询的记录集都是一样的（但将来可能不一定）
  [/B]





这不相当于全表扫描？当然慢了，建议建立如下索引：
create index index2 on cstmonilog(moni_area,moni_typeid,moni_flag,moni_time)
)


因为你看一下你的统计：
1 sorts (disk)
前面一个是没有sorts (disk)


SQL改为：
select rowid rID, 
MONI_TIME,MONI_TYPE,MONI_APERRCODE,MONI_CONSTANT,SubStr(MONI_USERINFO,1,50) 
MONI_USERINFO 

from cstmonilog 
where rowid IN 
(select rid from (select rownum rno,rowid /*+ INDEX(cstmonilog index2 ) */ rid 
from cstmonilog where MONI_AREA = :MoniAreaNo and MONI_TYPEID = :MoniTypeID and 
MONI_FLAG = '1' and rownum <:MoniNoLast 
order by MONI_TIME) where rNo >=:MoniNoFirst )
order by MONI_TIME;

__________________
^^AHA^^


由 jimmylee 于 02-07-31 22:01 发表: 
99万条记录，order by 确实利用了索引，但如果用row_number没有用索引，why?


由 DeepBlue 于 02-07-31 22:16 发表: 
影响了优化器的判断,优化器根据索引的存在选择了一条他认为好,实际上更坏的执行路径
__________________
====================
革命有理, 灌水无罪! 
====================


由 yafeng 于 02-07-31 22:30 发表: 
-------------------------------------------------------------------
需要说明的是目前的记录的moni_area,moni_typeid,moni_flag都是相同的，就是说目前的99万条记录加不加where语句查询的记录集都是一样的（但将来可能不一定）
-------------------------------------------------------------------
这样子还有必要建索引？即便将来有不同，索引的区分度也太低。
__________________
菜鸟就是说菜绿色的鸟。
也可以理解成笨鸟等等。
叫我菜鸟笨鸟都行。
但是别说我鸟。。


由 jimmylee 于 02-07-31 22:34 发表: 
Re: Re: 为什么加上索引速度奇慢无比？
  quote:

  最初由 lwxian 发布



  这不相当于全表扫描？当然慢了，建议建立如下索引：
  create index index2 on cstmonilog(moni_area,moni_typeid,moni_flag,moni_time)
  )


  因为你看一下你的统计：
  1 sorts (disk)
  前面一个是没有sorts (disk) 




加上这样的索引，执行计划显示使用index2而不用index1，但速度比index1慢多了，一个是33秒，一个是167秒。看来目前只是moni_time域使用了索引。


由 jimmylee 于 02-08-01 01:45 发表: 
  quote:

  最初由 DeepBlue 发布
  影响了优化器的判断,优化器根据索引的存在选择了一条他认为好,实际上更坏的执行路径 



如何解决？


由 lwxian 于 02-08-01 01:45 发表: 
Re: Re: Re: 为什么加上索引速度奇慢无比？
  quote:

  最初由 jimmylee 发布


  加上这样的索引，执行计划显示使用index2而不用index1，但速度比index1慢多了，一个是33秒，一个是167秒。看来目前只是moni_time域使用了索引。 






你有用我的SQL测试吗？
__________________
^^AHA^^


由 jimmylee 于 02-08-01 02:48 发表: 
Re: Re: Re: Re: 为什么加上索引速度奇慢无比？
  quote:

  最初由 lwxian 发布



  你有用我的SQL测试吗？ 



是的，我试了一下，速度确实比以前快了不少，以前是2分多，你的做法是1分多，基本提高了一倍。但速度还是有些慢。


由 hahaer 于 02-08-01 03:15 发表: 
Re: Re: 为什么加上索引速度奇慢无比？
  quote:

  最初由 lwxian 发布



  这不相当于全表扫描？当然慢了，建议建立如下索引：
  create index index2 on cstmonilog(moni_area,moni_typeid,moni_flag,moni_time)
  )


  因为你看一下你的统计：
  1 sorts (disk)
  前面一个是没有sorts (disk)


  SQL改为：
  select rowid rID, 
  MONI_TIME,MONI_TYPE,MONI_APERRCODE,MONI_CONSTANT,SubStr(MONI_USERINFO,1,50) 
  MONI_USERINFO 

  from cstmonilog 
  where rowid IN 
  (select rid from (select rownum rno,rowid /*+ INDEX(cstmonilog index2 ) */ rid 
  from cstmonilog where MONI_AREA = :MoniAreaNo and MONI_TYPEID = :MoniTypeID 
  and MONI_FLAG = '1' and rownum <:MoniNoLast 
  order by MONI_TIME) where rNo >=:MoniNoFirst )
  order by MONI_TIME;




这个语句出来的结果对吗？ 要是对的，我想是你运气不错


__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 hahaer 于 02-08-01 03:25 发表: 
Re: Re: Re: Re: Re: 为什么加上索引速度奇慢无比？
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
/

保证你以秒级速度运行。