Re: Issue with the order of data being retrieved from 10g.

<neeraj.gupta1983_at_gmail.com> wrote in message news:1180451437.043332.96110_at_q19g2000prn.googlegroups.com...
> Hi
>
> My problem is:
>
> i have a table say POLICY with three columns A, B and C.
> table has two rows, with column C having value 0 for both rows.
>
> Now i am writing
>
> select * from POLICY order by C.
>
> as both the rows has value of 0(zero), the result should be shorted
> assending based on the
> rowid, but here its opposite, i.e. the result set is shorted
> descending for rowid.
>
> Is that an issue with the version of 10g server, i am using or Is it
> some settings of the oracle server?
>
> Thanks
> Neeraj
>

As others have stated, you should never assume any ordering for the result set, you should always use an "order by" clause.

However, this behaviour is version dependent. In 10.2 Oracle introduced a new sorting algorithm that reduced the CPU and memory overheads. It isn't always used, but in your example you can see the side effects. You can also demonstrate the effect explicitly

SQL> alter session set "_newsort_enabled"=false;

Session altered.

SQL> select /*+ old sort */ t1.*, t1.rowid from t1 order by c;

         A B C ROWID

---------- ---------- ---------- ------------------
         1          1          0 AAATnbAAGAAABQKAAA
         1          1          0 AAATnbAAGAAABQKAAB
         1          1          0 AAATnbAAGAAABQKAAC
         1          1          0 AAATnbAAGAAABQKAAD
         1          1          0 AAATnbAAGAAABQKAAE

5 rows selected.

SQL> alter session set "_newsort_enabled"=true;

Session altered.

SQL> select /*+ new sort */ t1.*, t1.rowid from t1 order by c;

         A B C ROWID

---------- ---------- ---------- ------------------
         1          1          0 AAATnbAAGAAABQKAAA
         1          1          0 AAATnbAAGAAABQKAAB
         1          1          0 AAATnbAAGAAABQKAAE
         1          1          0 AAATnbAAGAAABQKAAD
         1          1          0 AAATnbAAGAAABQKAAC

5 rows selected.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html