Re: equivalent of 'clustered' (SQL) in ORACLE

I have been corrected.

Both Mark Townsend and Valery Yourinsky ammended this response to include the fact that secondary indices are allowed on IOTs as of Oracle 8i.

This makes Oracle IOTs a nearest equivalent to MSSQLSvr Clustered tables. Still, the usage of an IOT should be left to applications similar to those I described previously: applications with relatively static table data.

Thanks for the update Mark and Valery.

-Brian

In article <943c1v$66n$1_at_nnrp1.deja.com>,   b_arlt_at_my-deja.com wrote:
> In article <9439i4$4f8$1_at_nnrp1.deja.com>,
> iluzn_at_my-deja.com wrote:
> > This is what I have written in SQL:
> >
> > create table TEST
> > (ID int primary key clustered,
> > name varchar(30),
> > .....etc)
> >
> > i am unsure of what "clustered" does and how to duplicate its effect
> > within an oracle DB
> >
> > can you help?
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> The term 'clustered' in MSSQLSvr implies that the data of the table is
> stored as an index which is ordered upon the primary key.
>
> There is a similar construct in Oracle, an index organized table
> (IOT). The use of an IOT, however, comes with severe restrictions
> including the inability to define any other indices on the table.
 This
> means that >>ALL<< efficient access to the table must be done via the
> primary key. In most cases this is not appropriate.
>
> Clustered tables in MSSQLSvr are most useful in applications with
> relatively static tables or strictly increasing tables with data that
> is relatively static after insert (i.e. few deletes or updates).
>
> Usually, these applications also show a need for rapid access to
> multiple, varying columns within the table. If this is the nature of
> your application, your solution is probably going to be more complex
 in
> Oracle. You should review the activity on the segment and allocate
> indices based upon query demands.
>
> If most of your activity is centered around the primary key, I would
> recommend a wait-and-see approach. If your data is allocated
> efficiently, you should be able to get by with the primary key index.
> (Make sure the index is created in a separate tablespace.) If you
> notice queries bogging down, run an explain-plan and isolate and
 remove
> any table scans.
>
> Hope this helps. Good luck.
>
> -Brian
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 17 2001 - 09:13:24 CST