Edi Yanto (何 萬 新)

Oracle Top-N Query

Posted on: October 1, 2009


  1. Using RANK function, with a faster execution plan (WINDOW SORT PUSHED RANK)
    SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
    FROM
    (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
    RANK() OVER
    (ORDER BY SAL Desc) AS Emp_Rank
    FROM Emp)
    WHERE Emp_Rank <= 5;
  2. Using row_number function (speed up top-n queries)
    SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
    FROM
    (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
    row_number() OVER
    (ORDER BY SAL Desc) Emp_Rank
    FROM Emp)
    WHERE Emp_Rank <= 5;
  3. Using ROWNUM
    SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
    FROM
    (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
    FROM Emp
    ORDER BY SAL Desc)
    WHERE ROWNUM <= 5;

Using ROWNUM to materialize an in-line view is not efficient, and it’s better to materialize the subquery using the SQL-99 WITH clause.  ROWNUM can cause performance problems.  Using ROWNUM may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans.  One solution is to always include an all_rows hint when using ROWNUM to perform a top-n query.  See tuning SQL with “rownum”.

5 Responses to "Oracle Top-N Query"

Hi,

Very good article. Thanks for providing such great tip.

Like

I typically do not write comments on posts, but your post urged me to commend your writings. Thanks for writing this, I’ll certainly popular your site and come back as soon as in awhile. Content blogging.

Like

I found your blog while on google. I am very impressed with your writing skills and you have it together. I am starting my own blog and have been looking for ideas and you have inspired me. Thank you!

Like

So I quite simply state you produce many excellent points and I will publish a number of creative ideas to add to soon.

Like

Awesome site, how long have you been working on it?

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

October 2009
M T W T F S S
« Jun   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: