Clustered Index

l.nethaji · Posted: Thu Aug 19, 2010 1:27 pm

Hi,

I have some basic db2 understanding

Based on my understanding ,

Clustered index is declared to make the physical ordering in the table .

So when the search is happening in a table for particular value the index table is referred for effecient search.

If the index table is going to be referred for both normal and clustered index ,

What the advantage of having clustered index and what the need for physcial order.

Please let me know if i am wrong .

regards,
L.nethaji
regards,
L.Nethaji

GuyC · Posted: Thu Aug 19, 2010 1:47 pm

you need to read some performance and tuning guides.
clustering is interesting when reading (or scanning) more than 1 row via an index.

if you are selecting several rows and they are on the same page or on pages closeby you benefit from these two things :
- usually there are more than 1 row on a page
- pages next to each other can be read in 1 I/O and asynchronously before needing them = prefetching.

clustering helps you avoid I/O's if you put the rows (as much as possible) in the order in which you will mostly process them.

l.nethaji · Posted: Thu Aug 19, 2010 2:48 pm

Hi ,

Thanks A lot .

I am able to infer the advantages during i/o operation in case of group selection.

Still i am in the process of digging out more into cluster index.

Regards,
L.Nethaji.