Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ROWNUM strangeness?

RE: ROWNUM strangeness?

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 29 Aug 2002 10:33:29 -0800
Message-ID: <F001.004C2E37.20020829103329@fatcity.com>


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
--------------------------------------------------------------------
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). Received on Thu Aug 29 2002 - 13:33:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US