Re: sort without order by

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>
>
> >> 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
>

--
If you want to find the minimum value of a key, your best bet would be to use the MIN function: SELECT MIN(KEY_COL_NAME) FROM MY_TABLE;

Ed Stevens
(Opinions are not necessarily those of my employer)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 19 1999 - 07:18:41 CST