What is the Difference Between the FIRST_ROWS Hint and ROWNUM in the WHERE Clause?

10032011

March 10, 2011

A couple of days ago I saw an interesting question that asked what is the difference between using the FIRST_ROWS(100) hint in a SQL statement and using ROWNUM<=100 in the WHERE clause with the original SQL statement moved to an inline view. For example:

SELECT /*+ FIRST_ROWS(100) */
C1,
C2
FROM
T1
WHERE
C1 BETWEEN 1 AND 1000
ORDER BY
C1;

SELECT
*
FROM
(SELECT
C1,
C2
FROM
T1
WHERE
C1 BETWEEN 1 AND 1000
ORDER BY
C1)
WHERE
ROWNUM<=100;

My first thought was that the difference is actually quite simple to explain, and then I started to wonder, how could someone logically come to the conclusion that the two approaches would yield the same results? Do developers look at explain plan output? Could that explain how someone might look at these two approaches and question whether or not the two approaches are equivalent? Let’s see:

So, the FIRST_ROWS hinted plan shows that 102 rows will be returned, while the plan with ROWNUM in the WHERE clause shows that 100 of 102 rows will be returned. So, this is the first clue that the two approaches might not be equivalent. Other people have written extensively about what the FIRST_ROWS hint (and OPTIMIZER_MODE) mean and also how ROWNUM in the WHERE clause works (and a potentially significant bug that is present prior to Oracle Database 11.2.0.1). Is it possible to simplify the explanation of the difference, something like this?

The short answer is that the FIRST_ROWS hint tells the query optimizer: I really do not care to know if more than 1, 10, 100, or 1000 rows could be returned by the query, just plan the query execution as if my application will only retrieve 1, 10, 100, or 1000 rows – my application might still retrieve all of the rows, but just plan on the specified number being read.

The ROWNUM predicate in the WHERE clause tells the query optimizer: I really do not care if more than 1, 10, 100, or 1000 rows could be returned by the original query, plan the query execution as if my application will only retrieve 1, 10, 100, or 1000 rows and make certain that my application cannot retrieve more than the specified number of rows.

Simple, maybe just a little too simple?

Let’s put together a little test script to demonstrate. First, we will create a table with a primary key index, insert 500,000 rows, and then collect statistics with a 100% sample size:

Note in the plain (unmodified) query that 90,001 rows were returned to SQL*Plus, while the optimizer predicted that 90,002 rows would be returned. It required 0.43 seconds for the rows to be returned to the SQL*Plus session.

Note in the FIRST_ROWS(100) hinted query that 90,001 rows were returned to SQL*Plus, while the optimizer predicted that 103 rows would be returned due to the hint. It required 0.43 seconds for the rows to be returned to the SQL*Plus session.

Note in the query with the ROWNUM predicate in the WHERE clause that 100 rows were returned to SQL*Plus, while the optimizer predicted that 100 rows (103 rows before the COUNT STOPKEY operation) would be returned. It required 0.01 seconds for the rows to be returned to the SQL*Plus session.

Now for the second half of the output (created on Oracle Database 10.2.0.2 – your results may be a bit different). The first query:

Nothing too out of the ordinary in the above, the WHERE clause predicate was pushed into the inline view, although I thought that the inline view (indicated by the VIEW line in the explain plan output) might disappear.

The WHERE clause predicate was pushed into the inline view again, but notice that the optimizer did not predict that 100 or 103 rows would be returned by the query. Do you see the same results on other Oracle Database release versions? It is slightly interesting to see that the available index access path was not selected… almost as if the FIRST_ROWS hint had no effect… of course you know the reason why. :-) (Edit: March 10, 2011: The same result was achieved when moving the hint to the inline view, and same result was achieved when specifying the hint as FIRST_ROWS(1). I guess that it is time to review the 10053 trace to see what happened.)

The standard WHERE clause predicate used in all of the queries was pushed into the inline view again, and the ROWNUM predicate was also pushed into the inline view. Does this demonstrate the potential bug – what if instead of a full table scan, the primary key index access path were used to read the data from table T1?

Actions

Information

11 responses

If you read my article that described a potentially serious bug when ROWNUM is used in the WHERE clause, you probably recognize another item that the two approaches discussed in this article have in common. The use of ROWNUM in the WHERE clause effectively switches the optimizer to optimize for the specified number of rows to be retrieved as quickly as possible – just as what happens when a FIRST_ROWS(n) hint is specified. I had originally intended to mention this fact in the article.

The first one is why we are seeing “TABLE ACCESS BY INDEX ROWID” instead “TABLE FULE SCAN” ???
And second one is why we are seeing “db file scattered read” instead “db file sequential read”?
I’m appreciating to help me.
Ghassem

If the optimizer parameters and system (CPU) statistics tell the optimizer that index access paths are very inexpensive, you very well could see the execution plan change from a full table scan to an index range scan. Note that you posted TKPROF for the execution which shows the *actual* execution plan, while I posted the autotrace generated explain plan output which may not show the actual execution plan. Note that there is a risk that TKPROF in 11.1.0.6 *could* potentially display the wrong execution plan, if there are multiple execution plans in the trace file for the same SQL statement – see the following article for a demonstration:https://hoopercharles.wordpress.com/2010/01/11/explain-plan-lies-autotrace-lies-tkprof-lies-what-is-the-plan/

I suggest reviewing the raw trace file to see why db file scattered reads were used during the execution. I suspect that you will find that many of those reads were used to fetch the table blocks (as indicated by the statistics) but could also be used to quickly read adjacent index blocks. See the comments and test case by Timur Akhmadeev in this thread for a demonstration of db file scattered read waits appearing during an index range scan:http://forums.oracle.com/forums/thread.jspa?threadID=941864&tstart=0

Charles,
I believe Timur’s test case was specific to a particular version (10.2.0.3) and it could not be reproduced in different versions in a deterministic manner.
In fact, Timur’s test case was also referenced in another discussion (see http://forums.oracle.com/forums/thread.jspa?messageID=4581053#4581053 )
where he has acknowledged the issue. It makes more sense to say that the read of adjacent index blocks would result in “DB File Parallel Read” and
not “DB File Scattered Read”.

I think in that article Timur simply stated that he could not reproduce the results on a particular version – I believe that I had the same problem with the test case when I tried to produce the results on Oracle Database 10.2.0.2 Standard Edition. However, that does not mean that a db file scattered read operation cannot be used to read index blocks. You can see an example of that happening roughly half way through this article, as partially shown below:https://hoopercharles.wordpress.com/2010/11/21/different-performance-from-standard-edition-and-enterprise-edition-2/
Standard Edition (obj# 20275 is the table T1, obj# 20276 is the index):

The “db file parallel read” wait event will appear when multiple non-adjacent blocks are read during a prefetch operation

Although I’m not Narendra, I think by “adjacent index blocks” he means “the blocks which are logically adjacent in the index structure”. So, they are read in parallel by process – it simply picks up ROWIDs of index blocks from a branch block and issues one request to access them all (maybe not all, I don’t know in-depth details).

I did not intend to say it never happens. My point was about whether it is deterministic enough to make a generic statement.
I know very little about how CBO works and things change with each oracle version. During the OTN forum discussion that I had mentioned above,
I was not able to reproduce the behaviour with a couple of oracle versions. I will certainly see if I can reproduce the results with your test case.
BTW, I might have missed it but the documentation link that you have provided does not appear to make any reference to blocks being adjacent or not.

You remind me once again about the importance of making a complete (and correct) statement. Thanks for that.
I think I wanted to say that when a table is accessed using an index range scan, the conventional wisdom said table blocks will
be read one-block-at-a-time (and hence might result in “DB File Sequential Read” waits). I think from 9i onwards, CBO was enhanced
to perform multiple single-block reads for a table (resulting from indexed range scan) in parallel, instead of accessing single (table) blocks (adjacent or not)
in a serial manner. I think Tom (as always) explains it nicely here

Timur,
Thank you for pointing out “logical” and “physical” regarding the term “adjacent”.

Narendra,
Shortly after I posted my comment I thought about adding a couple of clarifications – my comment above might appear to be a little too harsh, essentially stating: “It happens this way, end of discussion.” There is a lot that I still do not understand about Oracle Database, so it is not my intention to say “End of discussion.” With my test case that I linked to above, you may see different results when testing different Oracle Database release versions or Standard/Enterprise edition (I saw db file sequential read waits on 10.2.0.2 Standard Edition).

I agree with you that the documentation link that I provided does not distinguish between adjacent and non-adjacent blocks when describing the “db file parallel read” wait event. Just because I said that something is true (in part 1 of the article series linked to in my previous comment) does not necessarily mean that it is true. However, a search of the Internet finds some agreement with my suggestion (disclaimer: I might have learned of this fact from this article), take a look at the article and the comments attached: http://jonathanlewis.wordpress.com/2006/12/15/index-operations/

Two things about the Jonathan’s blog post that you have mentioned
1) It is dated 15th December 2006. Lots of things might have changed since (but it may still be valid). Just trying to read things in context of the time (as Jonathan himself says)
2) In the 2nd comment, Jonathan himself claims that it is his assumption, which was not proved then
As I said earlier, it was more of a “conventional wisdom” to say that Table Access By index range scan would (typically) cause “DB File Sequential Reads” waits. There would
always be cases where “DB File Scattered Read” waits would be observed but my understanding is it would be safe to treat such cases as exceptions (handled internally by Oracle) rather than rules.
I think I have managed to significantly divert the discussion from the topic of your original blog post so apologies for that.

It is true that the comments in this article have drifted a bit from the original content of the article, but it has been an interesting conversation. In case it is not obvious to the casual reader of this blog, I think that I should point out that you have made excellent points in your comments. It would be interesting to see any insight that Jonathan or someone else could provide regarding the frequency of “db file scattered read” waits during index range scan operations – in my experience those operations appear to be infrequently used in the older Oracle Database release versions, but seem to be more common in more recent releases.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: