Re: Indexes: Attached or Detached?

> I asked because I've heard that detached indexes tend to be faster, but I'm > seeing mucho fragmentation (the bad kind) on these indexes, and I know of no > way to control the extent sizes of indexes. True, there is no way to directly control the size of index extents. That is a function of the table extent size combined with the ratio of key length to row length. Thus, if you allocate a table with extent size 50, any index on that table will have a small extent size, but if you allocate the table 500000, then the index is correspondingly larger. Is index extent size the only problem you're having, or are there others? > I'm wondering if there's a point of diminishing returns there. Also, if the > indexes are spread to the four corners of the globe, read-ahead is doing a > lot less good, because the indexes are no longer continuous on disk. Even when a detached index becomes fragmented, I would bet that it will still have more contiguous pages than would an attached index, because the attached index uses pages from the table's extents, intermixing index pages with data pages. That intermixing becomes even more of a problem when you have multiple attached indexes. With the detached index, while you may have multiple extents in the index, each one is composed of pages for only one index. Note that the above is merely opinion, as I've not verified any of it (yet). > Thanks for the tips. If I do any testing of my own, I'll let the group know > what I find. Looking forward to it. Mark Collins mcollins@us.dhl.com Words that come to mean everything may finally mean nothing; yet their very emptiness may allow them to be filled with a mesmerizing glamour.