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

<b_arlt_at_my-deja.com> wrote in message news:943c1v$66n$1_at_nnrp1.deja.com...
> 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.

BOLLOCKS. That restriction has been lifted in 8.1.5 as a result of the introduction of the UROWID data type. You can now have as many secondary indexes and unique constraints on the IOT as you want. Get it right.

HJR
> 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/
Received on Fri Jan 19 2001 - 07:07:27 CST