Oracle-L: RE: ROWNUM strangeness?

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Thu, 29 Aug 2002 12:13:40 -0800
Message-ID: <F001.004C30E9.20020829121340@fatcity.com>

It is not the predicate that causes the behavior, but the distinct. I'm taking an educated guess here, so please correct me if I am wrong. As the row is read, the predicate is applied. If the row matches the predicate, a rownum is assigned and it is saved as part of the result set. Once the rownum criteria is met, all subsequent rows are discarded as not meeting the criteria or oracle stops processing. I'm not sure which, but a few tests could confirm which is true. Regardless, the result is the same. After all matching rows have been placed in the result set, the DISTINCT operation sorts the data and discards any duplicates. As such, no new rows are read to match the 'rownum' criteria.

SQL> select deptno, job
2 from emp
3 where rownum < 5;

DEPTNO JOB
---------- ---------

        20 CLERK
        30 SALESMAN
        30 SALESMAN
        20 MANAGER

SQL> edit
Wrote file afiedt.buf

  1 select distinct deptno, job
  2 from emp
  3* where rownum < 5
SQL> /     DEPTNO JOB
---------- ---------

        20 CLERK
        20 MANAGER
        30 SALESMAN

-----Original Message-----
Sent: Thursday, August 29, 2002 12:33 PM To: Multiple recipients of list ORACLE-L

Paul,

It's because of how ROWNUM is interpreted.

The first query only returned the first 50 rows it found and you got 24 that passed the rest of your criteria.
The second query returned 1000 rows of which only 336 passed the rest of your criteria.

Makes perfect sense to me. This is why ROWNUM can cause confusing results.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Thursday, August 29, 2002 11:53 AM To: Multiple recipients of list ORACLE-L

I've been given a query to investigate. The performance varies extremely depending on whether the entire result set is fetched or a ROWNUM < n clause is attached; that makes sense to me. What doesn't make sense is the following: if we add WHERE ROWNUM < 50, the query returns 24 rows. If we add WHERE ROWNUM < 1000, the query returns 336 rows!

I don't know the size of the entire result set (I'm running a count right now), but if the result set consists of at least 336 rows, shouldn't WHERE ROWNUM < 50 return 49 rows?

TIA!

Paul Baumgartel, Adept Computer Associates, Inc. paul.baumgartel_at_aya.yale.edu

Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Paul Baumgartel
INET: treegarden_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------