下面的语句,使用了hint
select * from ( select /*+ FIRST_ROWS(10) */ record_id from yx_zhp order by create_date desc ) where rownum < 10 但速度变得非常慢,不用反倒会快些。谁能告诉我为什么呢?谢谢了!
select * from ( select /*+ FIRST_ROWS(10) */ record_id from yx_zhp order by create_date desc ) where rownum < 10 但速度变得非常慢,不用反倒会快些。谁能告诉我为什么呢?谢谢了!
yx_zhp 表很大,我只想取出时间最新的前10个即可。所以用了 order by create_date desc ,
本希望oracle 能够找到最新的10个日期就可以了。
可是反倒慢了。
能不使用hint就尽量不要使用hint,有时使用反而效率很差。
LZ所遇到的就是这样一个例子!
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=9 B
ytes=130) 1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=1 Card=10 Bytes=130)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'YX_ZHP' (Cost=826 Ca
rd=369848 Bytes=8136656) 4 3 INDEX (FULL SCAN DESCENDING) OF 'YX_ZHP_IX' (NON-UNI
QUE) (Cost=26 Card=369848)
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
495 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed上面的是用hint的计划和统计。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=9 Bytes=480
8024) 1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=826 Card=369848 Bytes=4808024)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'YX_ZHP' (Cost=826 Ca
rd=369848 Bytes=8136656) 4 3 INDEX (FULL SCAN DESCENDING) OF 'YX_ZHP_IX' (NON-UNI
QUE) (Cost=26 Card=369848)