Re: ROWNUM. can anybody clarify this to me.

From: Simon Thompson <scthomp_at_ibm.net>
Date: 1995/09/07
Message-ID: <42mehh$16qk_at_news-s01.ny.us.ibm.net>#1/1


In message <42ki05$t7l_at_cardinal.fs.com> - skannan_at_cardinal.fs.com ( S. Kannan) writes:

:> I think you are right. There has been a change in O7.1 in the EXPLAIN PLAN
:> statement. There is now an option called STOPKEY to the COUNT operation.
:> It is documented that the STOPKEY option is a count operation where the
:> number of rows returned is limited by the ROWNUM expression in the WHERE
:> clause. It, however, does not mention if a full table scan is avoided or
:> not. But I timed a query containing a ROWNUM in the WHERE clause and found
:> it to be significantly lesser to infer your conclusion. Thanks for pointing
:> this to me.

It probably depends on the statement. A simple:

        select * from table where rownum <= 10;

probably would not scan the whole table. On the other hand:

	select * 
                from table 
                where rownum <= 10
                order by SomeField;

would scan the whole table. It has to sort it (assuming there is no index on SomeField) to know which are the first 10 records. And an index may change the behaviour.

+---------------------------
| Simon Thompson

| Christchurch
| New Zealand Received on Thu Sep 07 1995 - 00:00:00 CEST

Original text of this message