Xref: alice comp.databases.oracle.server:74978
Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!arclight.uoregon.edu!logbridge.uoregon.edu!newsgate.cuhk.edu.hk!news.netfront.net!adenine.netfront.net!jcheong
From: Norris <jcheong@cooper.com.hk>
Newsgroups: comp.databases.oracle.server
Subject: Re: sort without order by
Date: 21 Nov 1999 01:37:00 GMT
Organization: Netfront http://www.netfront.net/
Lines: 120
Message-ID: <817ibs$2bo7$1@adenine.netfront.net>
References: <80teos$tf1$2@adenine.netfront.net> <460yOJef1dyg75sckSORZy62zl0e@4ax.com> <810klr$fjf$1@nnrp1.deja.com> <SRI0OHeMNUbWc66bBGgmt=fHE0v0@4ax.com> <812d3g$2v46$1@adenine.netfront.net> <813inf$jg0$1@nnrp1.deja.com> <814ubo$2vck$1@adenine.netfront.net> <enfd3ss3obnrde2k25004sj3esthmq8ps9@4ax.com> <816p45$1ivp$2@adenine.netfront.net> <ih4e3s0jpbm3icdm0894k691roqcteob8i@4ax.com>
X-Trace: adenine.netfront.net 943148220 77575 202.81.252.1 (21 Nov 1999 01:37:00 GMT)
X-Complaints-To: news@netfront.net
NNTP-Posting-Date: 21 Nov 1999 01:37:00 GMT
User-Agent: tin/pre-1.4-980818 ("Laura") (UNIX) (FreeBSD/3.2-RELEASE (i386))

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


ThomaeKyte <tkyte@us.oracle.com> wrote:
> A copy of this was sent to Norris <jcheong@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@8.0> create table t ( x int );

> Table created.

> tkyte@8.0> 
> tkyte@8.0> insert into t select rownum from all_users where rownum <10;

> 9 rows created.

> tkyte@8.0> 
> tkyte@8.0> create index t_idx on t(x);

> Index created.

> tkyte@8.0> 
> tkyte@8.0> set autotrace on
> tkyte@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@us.oracle.com> wrote:
>>> A copy of this was sent to Norris <jcheong@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@nmm.nissan-usa.com> wrote:
>>>>> In article <812d3g$2v46$1@adenine.netfront.net>,
>>>>>   Norris <jcheong@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@us.oracle.com> wrote:
>>>>>> > A copy of this was sent to karsten_schmidt8891@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@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@us.oracle.com
> Oracle Service Industries     Reston, VA   USA

> Opinions are mine and do not necessarily reflect those of Oracle Corporation