Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Sweden
    Posts
    8

    Unanswered: Row evaluation order

    Hi.

    I have two instances of a web application that runs on two different servers.

    When I hit the database with identical queries, the result differ in the order of the records in the result set. On one of the servers the result is returned in the order that they were inserted, on the other server the result appears to be close to random.
    I have double checked with sql plus to make sure that the data is identical and that they appear to be in the same order.

    I read somewhere that the order of the result set obtained by the execution of a select statement can not be guaranteed.

    I have some 17 tables in my database and all but one of the tables return the result in the order that the records are stored, using sqlldr, when a select statement is executed

    Is there no way to be sure that I get the result in the same order as they are stored in the table in the database?
    Doing an order by on a table with many rows does feel like a performance hog.

    Any comments or suggestions are welcome.

    Best regards
    Magnus

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Row evaluation order

    ORDER BY is the only way to be sure of getting rows back in any particular order. You must use ORDER BY.

  3. #3
    Join Date
    Feb 2004
    Location
    Sweden
    Posts
    8
    Hi again.

    It has been a while.....

    The data in the tables never change since this is only a replica used at a remote site. When an update is needed the tables are dropped and recreated before loading of data.

    I was thinking that perhaps some clever use of indexes would generate the kind of behaviour that I am after.


    Best regards
    Magnus

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If there is an index on the column(s) you want the data ordered by, and you specify ORDER BY those columns, then the optimizer may well use that index to order the records.

    If you leave off the ORDER BY, you may find that you get the records back in the order you want - or you may not. Even if it works this time, it might not next time. If the order is important, use ORDER BY.

  5. #5
    Join Date
    Feb 2004
    Location
    Sweden
    Posts
    8
    Ok. Thanks for bearing with me.

    I assume that I have just been lucky not to notice this before.

    /Magnus

Posting Permissions

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