Using rownum in the where condition.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Using rownum in the where condition.

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Using rownum in the where condition.

    Hi All,
    I am experiencing a strange thing, please let me know if this is normal.

    I am using oracle 8.1.7.4 and solaris 2.8.

    I am using this query.

    select * from (select * from cemp order by cstatus) where rownum < 10;

    Will the above query give the same records every time i execute the query. Will rownum give the first 9 records. If no ..why?

    what do i do to get the first 9 records.

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Depending on your data, it is not guaranteed to give you the same data in the same order each time. Imagine these situations:

    1) The "cemp" table contains more than 9 rows with the same "smallest" status. The order of the rows within this status will be totally dependant on what is already in cache and which blocks get read first from disk etc. As a result your ordering is not specific enough.

    2) You only have nine rows of the same "smallest" status. The same rows would be returned each time, but the order of the rows is not guaranteed.

    These are two very specific cases, but the problem can obviously be much bigger.

    Unless you specify an order that guarantees the order of each row, you CAN NOT assume Oracle will return the data in the same order each time.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    I wasnt aware that the order of returning the rows depends on what data blocks are cached in the buffer.

    Is this documented somewhere in the docs or some metalink note?

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Sorry.

    Just an off the cuff statement used as an extrapolation of the "whatever the DB can get access to first" idea. It wouldn't surprise me if it has some truth in it, but I have no evidence and I will try and keep my unproved theories to myself in future.

    Thanks for picking me up on that.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Tim,
    Thanks for the reply.
    So do you mean, the block read by oracle would change each time or is it

    that which ever block corresponding to this table comes first that would be

    read by oracle.

    Also will the position of the blocks keep changing.

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Unless you specify an order that differentiates between each row, Oracle will give you the data back in "no specific order". If all your data is in a single block then it will appear that the order is fixed, until a time when the data is no loger contained in a single block.

    The thing to remember here is, "If you want it in a specific order use an ORDER BY clause that *guarantees* that order, rather than relying on guesswork."

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width