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

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/ Received on Tue Jan 16 2001 - 23:54:08 CST