Clustered and Non clustered indexes

[sonaliak]

Is there any thing like clustered and non clustered indexes in Oracle like in SQL server ?
Whats the syntax and what's the difference between the two ...

Thanks
Sonali

[chrisrlong]

Yeah, but it's a little confusing. Oracle has clusters and clustered indexes THAT ARE COMPLETELY DIFFERENT! :)

In Oracle, what you are looking for is an index-organized table. Search for that in OTN to get the full info. Basically, it works the same, but since Oracle has so many more bloody table, column and indexing options, there are all kinds of caveats that must be considered.

HTH,

- Chris

[chrisrlong]

Sorry, should have added that the difference between the 2 in SQLServer is that one is a separate index and table, and the other (clustered) is where the data actually resides in the leaf blocks of the index. In Oracle, any regular index is 'non-clustered'. The way to make a 'clustered' index is via an index-organized table.

The differences in functionality are:

- If you are using that index, you will save 1 I/O per record, since you do not have to make an extra jump from the leaf page to the table page.
- These 'clustered' indexes or index-organized tables *really* shine in ranged searches, since the table data is *physically* ordered according to the index. This is why they are *almost always* on string fields, such as last name. Somethere where the user is going to do a lot of selects like WHERE LAST_NAME LIKE 'D%' (or other ranged-queries). All Oracle has to do is figure out where D starts and ends and then multi-block read everything in between - very fast - much faster than finding that range in the index and then making the jump for every single record back to the table to find that physical record.
- They slow down DML, since the data is now stored with the index, the table itself is a larger animal and DML is a little slower
- SELECTs that do not use the index will also, correspondingly be a little slow. These last 2 minor drawbacks are often outweighed by the benefits, but be aware of them.
- The table and index are 1!! They must reside in the same physical place, and cannot therefore benefit from separate I/O paths.
- Maintenance and other things are probably affected, but that's not my specialty.

There's probably more to consider - so check the docs, but that's what I have off the top of my head,

- Chris

[sonaliak]

I have not seen here anyone using clustered indexes. Do you(or any one) use them extensively ??