我有一个分页的应用，用降序排序的时候效率不是很好。请看：
[php]
suk@ORACLE9I> create index idx_item_update_date on item(update_date ,item_id);
索引已创建。
suk@ORACLE9I> analyze table item compute statistics for table for all indexes for all indexed columns;
表已分析。
suk@ORACLE9I> select/*+ first_rows */ * from (select* from item where item_type='p' and item_category='mp3播放器' and update_date is not null orde
r by update_date,item_id) where rownum select/*+ first_rows */ * from (select* from item where item_type='p' and item_category='mp3播放器' and update_date is not null orde
r by update_date desc,item_id desc) where rownum select * from (select /*+ index_desc(item idx_item_update_date) */ * from item where item_type='p' and item_c
pdate_date is not null order by update_date desc,item_id desc) where rownum drop index idx_item_update_date;
索引已丢弃。
suk@ORACLE9I>create index idx_item_update_date on item(update_date desc,item_id desc);
索引已创建。
suk@ORACLE9I> analyze table item compute statistics for table for all indexes for all indexed columns;
表已分析。
suk@ORACLE9I> select * from (select /*+ index(item idx_item_update_date) */ * from item where item_type='p' and item_category='mp
_date is not null order by update_date desc,item_id desc) where rownum<=20;
已选择20行。
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=67867 Card=20 Bytes=
3537947)
10 COUNT (STOPKEY)
21 VIEW (Cost=67867 Card=721 Bytes=3537947)
32 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=67867 Ca
rd=721 Bytes=339591)
43 INDEX (FULL SCAN) OF 'IDX_ITEM_UPDATE_DATE' (NON-UNI
QUE) (Cost=804 Card=214008)
Statistics
----------------------------------------------------------
96recursive calls
0db block gets
22357consistent gets
0physical reads
0redo size
6472bytes sent via SQL*Net to client
514bytes received via SQL*Net from client
3SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
20rows processed
--效果依然不理想
..
[/php]
降序索引本质是一个函数索引，但这个效率也太低了
对于这种情况如果做才能更高效呢？
|
