Re: sort without order by

A copy of this was sent to Norris <jcheong_at_cooper.com.hk> (if that email address didn't require changing) On 20 Nov 1999 18:26:13 GMT, you wrote:

>What is the syntax for the index on a heap organized table?
>

create table t ( x int );

create index t_idx on t(x);

Don't get confused by 'heap organized' -- that just means a 'regular' table. you can just use an index to get this information

tkyte_at_8.0> create table t ( x int );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> insert into t select rownum from all_users where rownum <10;

9 rows created.

tkyte_at_8.0>
tkyte_at_8.0> create index t_idx on t(x);

Index created.

tkyte_at_8.0> 
tkyte_at_8.0> set autotrace on
tkyte_at_8.0> select x from t where x > -99999999999999 order by x;

         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
>Thomas Kyte <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 20 Nov 1999 01:43:20 GMT, you wrote:
>
>>>What I mean is I can use IOT to find minimum 2 or 3 quickly
>>>
>
>> you can but you can also just use an index on a heap organized (regular) table
>> just as well.
>
>>>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>
>>>>>
>> ....
>
>> --
>> 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

--
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 Sat Nov 20 1999 - 15:30:22 CST