Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: left outer join results order inconsitent

    Hi

    When using a leftouter join the number of rows returned are not in the same order.i.e when the number of columns in the select changes the order of results change.
    It never happens when I use a simple join.
    I have no choice of ordering it in my sql.

    does anyone know why this to happening.

    The order in which it returns is critical for the our application to run correctly.


    The following two queries fetch same rows but the order of the results is different.



    SELECT 38231 EVENTID,
    ROWNUM ROWNUMBER,
    table1.Column1 Column1,
    table1.Column2 Column2,
    table1.Column3 Column3,
    table1.Column4 Column4,
    table1.Column5 Column5,
    table1.Column6 Column6,
    table1.Column7 Column7,
    table1.Column8 Column8,
    table1.Column9 Column9,
    table1.Column10 Column10,
    table1.Column11 Column11
    FROM table1 LEFT OUTER table1 ON table1.Column1 = table2.Column1

    SELECT 38231 EVENTID,
    ROWNUM ROWNUMBER,
    table1.Column1 Column1,
    table1.Column2 Column2,
    table1.Column3 Column3,
    table1.Column4 Column4,
    table1.Column5 Column5,
    table1.Column6 Column6,
    table1.Column7 Column7,
    table1.Column8 Column8,
    table1.Column9 Column9,
    table1.Column10 Column10
    FROM table1 LEFT OUTER table1 ON table1.Column1 = table2.Column1

    thanks and regards
    reddy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you combine these two facts --
    Quote Originally Posted by skesireddy View Post
    I have no choice of ordering it in my sql.
    Quote Originally Posted by skesireddy View Post
    The order in which it returns is critical for the our application to run correctly.
    the only possible conclusion is that you are up da creek wifout a paddle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    ORDER of rows is never garanteed if you do not use an ORDER BY clause, that holds true no matter what kind of statement you run. Even a simple select of a single table
    Code:
    select emp_name from emp
    . Yes you may get the same order when you run a particular query 1000 times, but on the 1001st it could be different due to some update/insert/delete activity on the table.
    Dave

  4. #4
    Join Date
    Sep 2009
    Posts
    6
    Hi Dav,

    In this case data is fixed and it doesnot change. What I am surprised is it changes with the number of columns in the select clause.
    In the following examples both return in the same order no matter how many times you execute them.

    thanks and regards
    Sugunakar

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Different query plans can use different indexes. Using different indexes will change how the worktables are ordered. Different orders in the worktables will show up as different orders in your result set.

    As it was mentioned before, the only way to guarantee an order to the rows is to ask for it explicitly.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by skesireddy View Post
    When using a leftouter join the number of rows returned are not in the same order.i.e when the number of columns in the select changes the order of results change.

    does anyone know why this to happening.
    Because Oracle is free to choose the best access plan to retrieve the desired result.
    My guess is that with fewer columns, the select can be retrieved from an index and with more columns Oracle chooses a full table scan.

    It never happens when I use a simple join.
    You will need to show us the definition of the tables (CREATE TABLE ...), some sample data (as INSERT INTO ...) and the real SQL you are running.

    I have no choice of ordering it in my sql
    What do you mean with "no choice"?
    If you can change the SQL to retrieve fewer columns, apparently you can modify the SQL.
    And if you can modify the SQL you can append the required ORDER BY to it.

    The order in which it returns is critical for the our application to run correctly.
    The you have to use an ORDER BY.
    There is no other way.

Posting Permissions

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