Re: sort without order by

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 Received on Thu Nov 18 1999 - 08:53:29 CST