Q

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 c

onstraints?

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.

This was first published in May 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close