Problem solve Get help with specific problems with your technologies, process and projects.

Difference between clustered and non-clustered indexes

What's the difference between clustered and non-clustered indexes? When do you use them for assigning UNIQUE constraints?

Every index implicitly involves a logical sequence. This allows for searching. If the database engine is searching an index linearly, i.e. in logical sequence, it will know when to stop when it either finds the entry or notices that it has "gone past" the point in the sequence where the entry "belongs." (However, most database engines use more efficient index searches than linear.)

A clustered index is one in which the index entries are actually data records. So when the database searches for an entry, it will end up with the actual data a lot faster. This means that the index/data entries have to be physically stored on the disk in a manner that aids retrieval -- in most cases, this is in sequential order.

Contact the DBA for your installation to find out whether your database supports clustering indexes and whether he or she recommends them, and under what conditions.

Unique constraints should be declared separately from the indexes (it is only a coincidence that most database engines utilize an index in order to enforce a unique constraint). Again, consult your DBA about whether your particular database allows unique clustering indexes.

Dig Deeper on Oracle and SQL