Oracle 9i: How can I determine, using metadata, whether or not an index is clustered?

Question

The question pretty much sums this up, but I'll provide some more details.

I can almost safely assume that any primary key index in an Oracle database is clustered. But I'm not one to assume. Besides, a user might have created a clustered index that wasn't the primary key. If that's the case, I'd really like to know.

So, in the interests of being really, really thorough, I'd like to remember (not that I forgot or anything), how to determine from the Oracle metadata, whether or not an index is clustered.

(And, per usual, Google was like rooting through a landfill, looking for the vintage Action Comics #1 that your mom through out because she thought it was useless at the time.)

Thanks!

Answer 1

What is known as a clustered index in SQL Server world is in Oracle world called an Index Organized Table. Table metadata is available in the all_tables or user_tables system views described here. My guess after skimming that link is that you can determine that a table is index-organized by checking whether the IOT_TYPE column is non-null.

Answer 2

Oracle does not have the concept of "clustered indexes" as SQL Server does. In general, Oracle table are "heaps" with the data stored in no particular order. There is a special type of table called an INDEX ORGANIZED table, which is (as its name suggests) a table that is organised like an index. However, in Oracle most tables would not be index organized - whereas my understanding is that most tables in SQL Server do have a clustered index.

Do not be tempted to declare all your Oracle tables as index organized in an attempt to emulate SQL Server; what is right for one DBMS is not necessarily right for another. I suggest you read the Oracle Database Concepts guide to get to know how Oracle works.

Index organized tables are identified by IOT_TYPE = 'IOT' in ALL_TABLES and USER_TABLES.