Re: Alter index to cluster

> I have used the process "alter index so_and_so to cluster" and > then "alter index so_and_so to not cluster" to reduce the number > of extents for the table associated with the index. It seems to me that you shouldn't have to do this very often at all. There was a time when we did this every couple of months on *each* of 10 systems, so we were doing it all the time. (It was worse even than reducing the number of extents, because we were simply out of dbspace. ALTER INDEX did not have enough dbspace to work.) The solution was clear: institute data purging. This keeps the number of rows in the table inside known bounds, which may be contained in a pre-determined number of extents. Originally we purged once per month, then weekly, now we purge at each insert. This keeps the range of row fluctuation very small. We have matured now to where we almost never have to rebuild a table, each table has only one or so extents (and thus is stored in contiguous space). This requires a prediction about the eventual (supposedly stable) size of the table. Given proper analysis (pedants might say "software engineering") this system is easy for the DBA to implement at the outset of the project. I've opined about this before, but perhaps (now that I've finished another degree program) it's time to attempt better communication. :) Didactically, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|