Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Different ORDER BY behavior in 9i and 10g databases

    When I run following query:

    SELECT 1 element, '1' value FROM dual
    UNION ALL
    SELECT 1 element, '2' value FROM dual
    UNION ALL
    SELECT 1 element, '3' value FROM dual
    ORDER BY element;

    on 9i database I get following results:
    1 1
    1 2
    1 3

    The same query run on 10g gives different results:
    1 1
    1 3
    1 2

    This is only a simple example of the issue Im experiencing in my application.
    Information regarding 10g database:
    10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production on SunOS version 5.10
    Information regarding 9i database:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production on HP-UX B.11.23 U 9000/800

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Both results are consistent with your ORDER BY clause, which only orders by the first column. If you want the data sorted by both columns then you need to say so. Oracle is not obliged to return data in a consistent order unless you explicitly tell it to via the ORDER BY clause - and indeed it will not. You were just "lucky" in 9i that the access path Oracle chose happened to return the data ordered in the way you wanted it even though you failed to say so!

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    Well, I know that Oracle does not guarantee order unless I use ORADER BY clause. In my “real world” scenario I have sorted (ordered by value column) view, let say SORTED_VIEW and dummy query:

    SELECT 0 element, ‘ ‘ value from dual 1=2
    UNION ALL
    SELECT 1 element, value FROM SORTED_VIEW
    ORDER BY element;

    Our clients on 9i were using this query for a long time and it works without problems. The issue has started occurring when we migrated to 10g.
    The question is:
    Should I expect that query above will properly ordered results?
    What I would like to achieve is ordered by value column results

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, I see no reason why that query should return data ordered by VALUE when you have only said to order by ELEMENT. Change the ORDER BY clause to say order by ELEMENT, VALUE and it will be fine.

    I would expect the ORDER BY in your SQL to override any ORDER BY in the view it selects from. Any resemblance between the view's order by and the results of this query would be a coincidence.

    It may well be that 9i happened to preserve the view's ORDER BY clause more often than 10G does, but that doesn't make 10G wrong, it means that your code bug went undetected in 9i.

    Another difference in 10G that has caught many developers out is that a GROUP BY returns unsorted data much more often than it used to. It has always been true that you should not rely on GROUP BY to order results, but it generally did so prior to 10G so lazy developers thought they could save a bit of typing and leave the ORDER BY off. Now they have a lot of bugs to fix!

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    I’m a bit confused because 9i seems to work more reasonable to me. It returns what I expected to be returned. I was digging in MetaLink searching for possible bugs but came up with nothing. Anyway, thanks for quick answer.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Look at it like this: a given query can only be ordered one way. If you did this:
    Code:
    SELECT dbms_random.value randomval, value FROM SORTED_VIEW
    ORDER BY randomval;
    you would expect the results to be ordered randomly, right? The ORDER BY in the view would make no difference here (except to slow down the query).
    In your case it just happens that it is possible to order by ELEMENT without losing the VALUE-based order the view returns - but "possible" isn't the same as "certain". You are sorting the data by VALUE and then sorting it a second time by ELEMENT. The second sort "overrides" the first - and is under no obligation to try to preserve any ordering dine by the first. Maybe 10G is being smart enough to say "since there is an order by in the main query, it is a waste of time to also sort the view so let's not bother".

    In fact, using AUTOTRACE shows that to be the case in this example:
    Code:
    SQL> create view sorted_emps as select * from emp order by empno;
    
    View created.
    SQL> set autotrace traceonly;
    SQL> select empno from sorted_emps;
    
    37 rows selected.
    
    --------------------------------------------------------------------------
    | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |       |    36 |   144 |     1   (0)| 00:00:01 |
    |   1 |  INDEX FULL SCAN | EMP_I |    36 |   144 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    SQL> select empno from sorted_emps order by deptno;
    
    37 rows selected.
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |    36 |   252 |     4  (25)| 00:00:01 |
    |   1 |  SORT ORDER BY     |      |    36 |   252 |     4  (25)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| EMP  |    36 |   252 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    Note that the first example gets EMP data via index EMP_I (on EMPNO) but the second does a full scan of EMP and then sorts the results.
    Last edited by andrewst; 02-19-10 at 11:13. Reason: Added example

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I love that your "expectation" is that, instead of coding the query correctly, you just expect it to give you the output you want without actually telling/coding it to do so.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    In version 10 and above when you do something like a distinct it uses hash values and there is NO order to the returned values. In the previous versions, it looked at the values and sorted them to eliminate duplicates. Databases change and you should always specify order by, don't depend on some sort of lucky ordering to work because it will bite you in the backside eventually.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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