What is difference between Clustered Index and Non-Cluster Index?
What is difference between cluster index and non cluster index
Software/Hardware used:
Software/Hardware used:
ASKED:
February 17, 2009 6:27 AM
UPDATED:
February 20, 2009 7:02 AM
Answer Wiki:
"There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows."
<a href="http://www.mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm">Source</a>.
To see all answers submitted to the Answer Wiki: View Answer History.
A clustered index doesn’t actually change the order that the data is physically sorted in. It used to back in SQL 6.5, but it hasn’t since then.
I posted about the differences on my blog.
When the clustered index is built the indexed columns are sorted within each page in the correct order just like a normal index, however the clustered index points back to the heap (the table) for the other values.
Row level data within the table is sorted in the order it is written to the data page which is why the only way to guarantee order is to use the ORDER BY.
You did not say which database you are using. I defer to Mr. Denny regarding SQL Server, but other databases do things different. In Informix, for example, you can run ALTER INDEX … TO CLUSTER. This will cause the table to be reorganized immediately, with the rows stored in the order that matches the key of the index that was ALTERed.
In DB2, a clustering index attempts to maintain clustering even as new rows are inserted. This can lead to page splits when the inserted row belongs on a page that is already full. These splits used to be called “near-off” or “far-off” splits (don’t know if that terminology is still in use — it’s been a while), depending on whether there was an empty page (see FREEPAGE parameter) nearby that could be used to hold the data split from the original page. Additionally, when the table is REORGed, these splits will be consolidated and the rows will be placed in CLUSTER key order.
I do not know if Oracle has anything comparable.
I think the closest thing in Oracle would be Index Organized Tables (IOT).
Hi Amin,
Clustered index sorts the records accordingly and stores in the table.
Non-Clustered index is not physically ordered but ordered its offset.
Regards,
Siva
HEY WIKE YOU UNIT INCH GOAT… Yeah, we remember when we had our first beer, too. — Tom