Re: sort without order by

From: Norris <jcheong_at_cooper.com.hk>
Date: 19 Nov 1999 02:36:32 GMT
Message-ID: <812d3g$2v46$1@adenine.netfront.net>

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 Received on Thu Nov 18 1999 - 20:36:32 CST