Re: sort without order by

From: Norris <jcheong_at_cooper.com.hk>
Date: 20 Nov 1999 01:43:20 GMT
Message-ID: <814ubo$2vck$1@adenine.netfront.net>

What I mean is I can use IOT to find minimum 2 or 3 quickly

Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> wrote:
> In article <812d3g$2v46$1_at_adenine.netfront.net>,
> Norris <jcheong_at_cooper.com.hk> wrote:

>> So, if I want to find minimum value of the key, I can use IOT to find

> it quickly, am I right?

>>
>> Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>> > A copy of this was sent to karsten_schmidt8891_at_my-deja.com
>> > (if that email address didn't require changing)
>> > On Thu, 18 Nov 1999 10:33:31 GMT, you wrote:
>>
>> >>Hi,
>> >>
>> >> not sure whether I 'd rely on this feature of index-organised

> tables.

>>
>> > It is a documented feature of IOT's, from the server concepts

> manual:

>>
>> > <quote>
>> > Ordinary Table
>> > .....
>> > Sequential scan returns all rows
>>
>> > Index-Organized Table
>> > ....
>> > Full-index scan returns all rows in primary key order
>> > </quote>
>>
>> >> This behaviour is probably due to the way index-organised tables

> are

>> >>implemented (as an B* index..), but the relational theory (a'la

> codd)

>> >>would say the result-set is not sorted unless you use order by.
>>
>> > agreed.  I would tend to add an order by to the query myself.

> Given that they

>> > wanted to mimick the behaviour of a Sybase/sqlserver clustered

> index -- the IOT

>> > would do it.
>>
>> >> right now this may be a no-op as the result is retrieved in a

> sorted

>> >>manner but order by may save you in future, in case the

> implementation

>> >>changes. (it is also more portable)
>> >>
>> >>Karsten
>> >>
>> >>In article <460yOJef1dyg75sckSORZy62zl0e_at_4ax.com>,
>> >>  tkyte_at_us.oracle.com wrote:
>> >>> A copy of this was sent to Norris <jcheong_at_cooper.com.hk>
>> >>> (if that email address didn't require changing)
>> >>> On 17 Nov 1999 05:34:20 GMT, you wrote:
>> >>>
>> >>> >In SQLServer, I can select data from table without order-by and

> the

>> >>records are sorted according to the clustered index of the table.

> What

>> >>is the syntax in oracle?
>> >>>
>> >>> you would use an index organized table.  A full scan of an index
>> >>organized table
>> >>> returns the data in the order of the primary key. These are

> available

>> >>in
>> >>> Oracle8.0 and up.
>> >>>
>> >>
>> >>
>> >>Sent via Deja.com http://www.deja.com/

>> >>Before you buy.
>>
>> > --
>> > See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to

> Oracle8i'...

>> > Current article is "Part I of V, Autonomous Transactions" updated

> June 21'st

>> >
>> > Thomas Kyte                   tkyte_at_us.oracle.com
>> > Oracle Service Industries     Reston, VA   USA
>>
>> > Opinions are mine and do not necessarily reflect those of Oracle

> Corporation

>>

> --
> If you want to find the minimum value of a key, your best bet would be
> to use the MIN function: SELECT MIN(KEY_COL_NAME) FROM MY_TABLE;

> Ed Stevens
> (Opinions are not necessarily those of my
> employer)

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Nov 19 1999 - 19:43:20 CST