Re: Pls Help... Oracle sort order

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 07 Jul 2007 06:41:28 -0700
Message-ID: <1183815687.358837@bubbleator.drizzle.com>

Martin T. wrote:
> On Jul 7, 8:10 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>> "DA Morgan" <damor..._at_psoug.org> a écrit dans le message de news: 1183755034.557..._at_bubbleator.drizzle.com...| Amritha.Da..._at_gmail.com wrote:
>>
>> | > In which sort order does oracle display the records if there is no
>> | > sort order specified in the SQL query?
>> | >
>> | > Is there any article which tells about this?
>> | >
>> | > My Oracle version is 9i
>> | >
>> | > Thanks.
>> |
>> | In whatever order it happens to find them depending on the path it
>> | chooses to read them.
>> |
>> | Don't expect this to change depending on version.
>> |
>> | If you want to have ordering without an ORDER BY clause you must
>> | use a sorted hash cluster or an Index Organized Table (IOT).
>>
>> Even in this case this is not true.
>>
>> For instance,http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7...
>>

>
> [quote] IF YOU WANT DATA SORTED, THOU SHALT USE ORDER BY [/quote]
>
> I like this. :-)
>
> While it may be interesting to understand why Oracle returns something
> in a certain order, I have to admit I do not quite understand why so
> many people seem to think that order-by is evil - any theories on this
> yet? ;)
>
> cheers,
> Martin

CREATE TABLE t AS
SELECT * FROM all_objects;

EXPLAIN PLAN FOR
SELECT * FROM t;

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT * FROM t
ORDER BY 1; SELECT * FROM TABLE(dbms_xplan.display);

That should explain it. <g>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jul 07 2007 - 08:41:28 CDT