Oracle Scratchpad

August 7, 2009

Rownum effects

Filed under: CBO,Execution plans,Hints — Jonathan Lewis @ 7:21 pm BST Aug 7,2009

A few months ago, I wrote a note about setting the optimizer_mode to one of the first_rows_N values (first_rows_1, first_rows_10, first_rows_100, or first_rows_1000).

One of the effects associated with this parameter is that the first_rows(N) hint and the predicate “rownum <= N” use the same first_rows_N arithmetic (although N can take any value for the rownum or hint).

In a recent follow-up, Timur Akhmadeev supplied a link to a discussion on the Oracle Forum about this topic, starting with a problem that a rownum predicate was causing and ending with a resolution through the row_number() analytic function.

I thought it would be worth making it easier for future researches to find the discussion by creating a specific blog item to point to it.


  1. Jonathan,

    Thank you very much for taking notice of this. I try to take these oddities and use them as a learning experience and the folks on the OTN Forum (Timur, Charles, and Tubby) provided really great insight. I also wanted to add that my initial analysis wouldn’t have been possible without your excellent book.

    Thanks again, and as always I truly enjoy reading about your experiences in any medium (book, blog, whatever!).

    Comment by Centinul — August 9, 2009 @ 1:49 am BST Aug 9,2009 | Reply

  2. Centinul,

    There are many discussions that appear on OTN and the newsgroup which rate a mention – and I ought to try and highlight them more often. I think that discussion (when there isn’t too much garbage flung in around the edges) is one of the best ways for people to learn the how to examine the facts.

    Thanks for the comments about reading the stuff I publish.

    Comment by Jonathan Lewis — August 10, 2009 @ 10:13 pm BST Aug 10,2009 | Reply

  3. Jonathan,

    I just wanted to pass along that I ran the test case I wrote in the thread against a installation and I received different results then Fortunately the result was the more efficient execution plan. I highlighted this in my most recent response to the thread.


    Comment by Centinul — September 20, 2009 @ 8:27 pm BST Sep 20,2009 | Reply

  4. Centinul,
    Thanks for letting me know. I’ve added a suggestion to the thread about a possible reason for 11.2 producing a better plan.

    Comment by Jonathan Lewis — September 20, 2009 @ 8:46 pm BST Sep 20,2009 | Reply

  5. […] rownum effects and first_rows_n, and an example of the problem in this item on the OTN database forum. Leave a Comment […]

    Pingback by first_rows_N again « Oracle Scratchpad — January 10, 2010 @ 7:03 pm GMT Jan 10,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by