Re: sort without order by

What is the purpose of having "where x > -99999999999999"?

ThomaeKyte <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 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 - 19:37:00 CST