Menu
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.
571
The Oracle database always uses the primary key as the clustering key. PostgreSQL only uses heap tables. You can, however, use the CLUSTER clause to align the contents of the heap table with an index. By default SQL Server uses clustered indexes (index-organized tables) using the primary key as clustering key.
A Clustered index is a type of index in which table records are physically reordered to match the index. A Non-Clustered index is a special type of index in which logical order of index does not match physical stored order of the rows on disk. The size of clustered index is large.
A Clustered index is one of the special types of index which reorders the way records in the table are physically stored on the disk. It sorts and stores the data rows in the table or view based on their key values. It is essentially a sorted copy of the data in the indexed columns.
The nonclustered indexes you define will largely be based on the data queries your applications will issue. Any defined primary key and UNIQUE constraints automatically get a unique index assigned by SQL Server. Foreign Keys do not get an index automatically defined.
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:
FIELD is the leading part of its primary key.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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
