10g怎么order by的时候不使用索引了？

Question

刚刚把数据库从817迁移到了10g，发现以前经常使用的语句变慢很多
select * from dxx order by xh desc;
我需要用这个来查询最近的数据，pl/sql developer中用这个语句很方便
一下就可以把最近的若干条数据取出来了
我已经在dxx表的xh字段上建立了索引
817中可以正常使用索引，换成10g之后不再使用索引，需要全表扫描
以前瞬间出来的结果现在需要几十秒。。
有什么办法可以解决这个问题啊

Answer 1

Oracle 10g中可以使用DBMS_ADVISOR的quick_tune获得一个索引建议,你看看这个

Answer 2

表没什么特殊性，是我们用来发短信的表，所以数据量很大
并不是这一张表出了问题，其它表也有类似问题
dxx表结构如下
Name   Type           Nullable 
------ -------------- -------- 
XH     NUMBER(10)              
FSZ    VARCHAR2(20)   Y        
FSHM   VARCHAR2(20)   Y        
JSZ    VARCHAR2(20)   Y        
JSHM   VARCHAR2(20)            
DXNR   VARCHAR2(4000)          
TJSJ   DATE           Y        
FSSJ   DATE           Y        
SJFSSJ DATE           Y        
WBSY   VARCHAR2(15)   Y        
FZH    NUMBER         Y        
BZ     CHAR(5)        Y        
YXJ    NUMBER(3)      Y        
XT     VARCHAR2(20)            
xh字段上有索引
alter table DXX
  add constraint PK_DXX primary key (XH)
select count(*) from dxx 得到结果427万
select * from dxx order by xh desc;取前22条数据需要30秒，没用到索引，需要全表扫描的
select * from dxx where rownum<23 order by xh desc;取前22条数据0.016秒，用到了这个索引
select * from dxx取前22条数据也只需要0.016秒
另外，select * from dxx where rownum<23 order by xh desc这样的脚本是取xh最大的22条数据还是随便取了22条数据之后按xh排序？看了一本书上说是后一种，不过我测试好象是前一种，现在心里很没把握，不知道oracle官方有没有什么说法？另外hongqi162(失踪的月亮)写的东西我看不懂，惭愧惭愧

Answer 3

1
“select * from dxx where rownum<23 order by xh desc;”取前22条数据0.016秒，用到了这个索引
并不是时间短就一定代表用上了索引，这句话总共只需要处理22条记录，当然快。2 “select * from dxx order by xh desc;取前22条数据需要30秒，没用到索引，需要全表扫描的
”
这个不一定的，就算用上索引，也需要处理索引的很多记录3 建议仔细查看执行计划

Answer 4

to:bjt_(bjt)
用没用到索引是我用plain看的，结果也证实了这一点
就拿select * from dxx order by xh desc这一句来说吧
相同的数据，相同的索引，在8i中很快就可以得到结果（＜＜1秒）
在10G中需要几十秒，这个对比是非常明显的
10G运行的机器比8i运行的机器还要好很多
8i运行在5年前买的m80机器上，4cpu12G内存
10G运行在刚刚买的p560上，8cpu16G内存
存储10G这边的也比8i的好很多，快N倍我想问的就是在10g的环境下
select * from dxx order by xh desc
如何可以很快得出结果？就象以前8i一样？
总不至于新机器新系统下10G表现不如8i吧

Answer 5

有没有人知道啊，或者在你自己的10g上试下告诉我是oracle固有的问题还是设置问题？

Answer 6

以前在817上面是rbo的吧.比较一下两边的优化器.
在10G下默认是CBO的.

Answer 7

嗯，默认的优化设置不同，用
select /*+rule */ * from dxx order by xh desc;
就可以了。
还有一个致命的xh上的索引没用，主键才可以！！！！！
归根结底还是oracle没有limit N这样的功能
order by的时候又不使用索引，最多只能用下主键，真要命。

Answer 8

这是因为,在使用index_ffs的时候,oracle本判断你的index列是否存在null如果存在，将不使用index scan, 否则反之．这也就说话了为什么pk using index是被引用的．