Select * from student where rownum < 6 order by student_id .
Solution
select * from (select * from student order by student_id desc) e where rownum < 6;
Another points suggested by user Guy Lambregts
Since I was involved in performance issues regarding top n queries recently I would like to remark
1. The importance of an index on the sort column created with desc attribute.
(create index emp_idx on emp (sal desc) )
2. The importance of optimizer_mode first_rows_n
3. As suggested here above the rank and the dense rank functions can be used as well, execution plan is often better with a WINDOW SORT PUSHED RANK in the explain plan.
1. The importance of an index on the sort column created with desc attribute.
(create index emp_idx on emp (sal desc) )
2. The importance of optimizer_mode first_rows_n
3. As suggested here above the rank and the dense rank functions can be used as well, execution plan is often better with a WINDOW SORT PUSHED RANK in the explain plan.
Recently I faced this query, table t1 contained 1.000.000 rows
select * from (select * from t1 order by col1 desc, col2 desc) where rownum < 21
A full table scan on t1 was shown with set autotrace on
I created an index
create index t1_idx1 on t1 (col1 desc, col2 desc)
I gathered statistics with dbms_stats
I ran the same query with optimizer_mode = first_rows_10
create index t1_idx1 on t1 (col1 desc, col2 desc)
I gathered statistics with dbms_stats
I ran the same query with optimizer_mode = first_rows_10
I reduces IO with factor 15.000 !
No comments:
Post a Comment