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. [EMAIL PROTECTED] __________________________________________________ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Fink, Dan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).