Is an index clustered or unclustered in Oracle?

Question

How can I determine if an Oracle index is clustered or unclustered?

I've done

select FIELD from TABLE where rownum <100

where FIELD is the field on which is built the index. I have ordered tuples, but the result is wrong because the index is unclustered.

Answer 1

By default all indexes in Oracle are unclustered. The only clustered indexes in Oracle are the Index-Organized tables (IOT) primary key indexes.

You can determine if a table is an IOT by looking at the IOT_TYPE column in the ALL_TABLES view (its primary key could be determined by querying the ALL_CONSTRAINTS and ALL_CONS_COLUMNS views).

Here are some reasons why your query might return ordered rows:

1. Your table is index-organized and FIELD is the leading part of its primary key.
2. Your table is heap-organized but the rows are by chance ordered by FIELD, this happens sometimes on an incrementing identity column.

Case 2 will return sorted rows only by chance. The order of the inserts is not guaranteed, furthermore Oracle is free to reuse old blocks if some happen to have available space in the future, disrupting the fragile ordering.

Case 1 will most of the time return ordered rows, however you shouldn't rely on it since the order of the rows returned depends upon the algorithm of the access path which may change in the future (or if you change DB parameter, especially parallelism).

In both case if you want ordered rows you should supply an ORDER BY clause:

SELECT field 
  FROM (SELECT field 
          FROM TABLE 
         ORDER BY field) 
 WHERE rownum <= 100;