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”.

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

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

Join 1,457 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  

  • Edi Yanto: Dear Nakka, According to the error message "Post-processing of request 7823071 failed at 17-May-2018 02:25:18 with the error message: One or more pos
  • Pallavi Nakka: Hi Edi, Can you please help me with the below error +---------------------------------------------------------------------------+ Receivables:
  • Juwanto: Lupa yaa

Visitors

Categories

Blog Stats

  • 198,245 hits

Users Online

web counter

Pages

Advertisements
%d bloggers like this: