Re: table storage

I think Aravind provided the correct answer. An IOT is the only Oracle table format that really causes the physical data to be stored in a specific predictable physical order. A full tablescan on an IOT returns all rows in key order, but secondary indexes are not supported.

Also the Oracle index cluster which Sybrand mentioned does not guarantee the order that the physical data is stored in, only that rows with the same cluster key for all the tables assigned to the cluster are stored together for faster retrieval when retrieved via a join. But you do have the ability to retrieve in the cluster key index order.

Oracle also provides hash clusters which randomize the distribution of the data based on the hash key value into the allocated storage. For static amounts of data where the key value is always referenced by an equal operator these can be very fast.

In article <38BFBF98.9689FE1_at_us.oracle.com>,   Aravind Yalamanchi <ayalaman_at_us.oracle.com> wrote:
>
> Oracle8 has Index-Organized tables for this purpose. You can create
> an Index-Organized table with "ORGANIZATION INDEX" option as in :
>
> create table tab1 (a int, b int, c int, d int, primary key(a,c))
> ORGANIZATION INDEX;
>
> In this case, all the rows in the table will be stored in the primary
> key order. Please let me know if you need additional information with
> some performance numbers.
>
> -Aravind.
>
> Sybrand Bakker wrote:
>
> > On 3 Mar 2000 07:10:39 GMT, feigenb_at_is03.fas.harvard.edu (Lee)
wrote:
> >
> > >Hi everyone,
> > >
> > >I'm new to Oracle, having used SQL Server and mysql in the past,
and having
> > >browsed the idnex of a couple of books, I have been unable to find
the answer
> > >to the following question:
> > >
> > >Using Oracle, is there a way to specify a column (or columns) that
a table
> > >should be physically sorted by? (i.e., is there a way to specify
one or more
> > >columns such that the data is stored on disk in the order of
values in that
> > >column(s))?
> > >
> > >I believe this is accomplished in SQL*Server via the use of a
CLUSTERED INDEX,
> > >but this seems to be a SQL*Server specific thing...
> > >
> > >Any pointers?
> > >
> > >Lee
> >
> > Create a cluster
> > create an index on that cluster
> > and create the table in that cluster.
> > This option is seldom used, as there seem to be performance
drawbacks
> > It is usually discussed in the context of 2 interrelated tables
with a
> > master detail relationship, which are always needed simultaneously.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
>
>

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Mar 04 2000 - 13:37:11 CST