Oracle does not guarantee the order in which records are retrieved (read in from the disk or data buffers) nor does Oracle guarantee the order in which records are presented to you unless you specify an ORDER BY. The ROWNUM is assigned after Oracle has "selected" the record to appear in the result set, however the initial selection of the record is in whatever order Oracle deems to be most efficient in retrieving your records, and the ROWNUM is assigned after the record has passed the WHERE clause evaluation, and as a last step, the ORDER BY is applied to sort the order of presentation of the records to you.
..Rudy -----Original Message----- Sent: Wednesday, July 09, 2003 12:45 PM To: Multiple recipients of list ORACLE-L Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. > MaryAnn, the best way to understand rownum is to do the following: > > SQL> SELECT ROWNUM, GENDER > 2 FROM (SELECT ROWNUM, GENDER > 3 FROM EMP2 > 4 WHERE ROWNUM <= 20) > You will quickly see that, no matter how you order the result set, > the first record returned is rownum #1, second is rownum #2 etc. > The rownum value is assigned as rows are RETURNED or DISPLAYED, > not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 10000+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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.net -- Author: Rudy Zung INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
