Re: Are the indexes clustered or not:?

¨
1.
--
You can find IOT tables by doing a self join on the DBA_TABLES view where dba_tables1.table_name = dba_tables2.iot_name

2.
--
No.

A clustered index in db/2 udb and mssqls resembles what oracle calls Index organized table. The proporties of an IOT is - rows are identifyed by the PRIMARY KEY (and not a ROWID) - as a IOT has no ROWID there cannot be any secondary indexes (as it can in SQL Server)

- In a full table scan rows are returned in PK sequence
- IOTs do not support unique constraints
- IOTs cannot participate in distributed transactions

A cluster in oracle is a way to stire related sets of rows (from multiple tables) in the same Oracle server block. You get cluster information by joining the DBA_CLUSTERS view to DBA_CLU_COLUMNS, DBA_TAB_COLUMNS and DBA_CLUSTER_HASH_EXPRESSIONS. This is usful to tune joins in OLAP/DW environments

3. You cannot tell. Using clustered indexes in SQL Server or DB/2 isnt alays the best option, Oracle IOTs are primary useful for tables that has a few short nonkey columns that are "only" accessed by an non- composite primary key

Cheers,

Örjan

In article <8bfvqa$aad$1_at_mailint03.im.hou.compaq.com>, "david spaisman" <david.spaisman_at_compaq.com> wrote:
> Hello:
>
> I am looking at the indexes in a production system that I have taken
over
> recently. I have queried dba_indexes and have also looked through
DBArtisan
> looking at the tables and indexes but I cannot determine if the index
es
> are clustered or not.
>
> 1) How can I determine if the indexes are clustered?
>
> 2) If a primary key is unique, and I see the indexes named with a pk
> extension are, are these indexes considered 'clustered' in the
db2/mssql
> server sense of a clustered index?
>
> 3) I not , should I have them created as such?
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 24 2000 - 14:30:29 CST