Re: Fragmenting tables/indexes

} Hi. We have a system that we have fragmented based on Date so that when we } purge our system on a daily basis we simply drop the oldest fragment. This } part works } superbly, but retrieval of records seems to be unneccesarily slow. I } believe that } what we need is a separate dbspace for the indexes, but other DBAs feel } that this } will cause more problems because the indexes will have to be rebuilt to } drop the } data fragment. Am I wrong to assume that the index pages will work like } data pages } and simply "mark" the rows that are no longer used, and reuse these spaces } as } needed for the next day's data? Sadly, yes, you are mistaken. If you have a detached index on a fragmented table, the index must be dropped before you can ALTER FRAGMENT ... DETACH. While it seems intuitively obvious that an index fragmented the same way as the data (i.e, each fragmented by date, with the same date ranges, etc.) could just drop or detach the same fragment as the base table, it doesn't happen that way. } FYI the table has more than one index, and only one is organized with the } date } field first. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.