Tuesday, August 31, 2010

Top N query

To fetch the data for top N rows. We can not use following query as it will apply order by after selecting first N rows
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.
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
I reduces IO with factor 15.000 !


No comments:

Post a Comment