Re: Fragmenting tables/indexes

Kate_Tomchik@HomeDepot.COM wrote: > > 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? Since you have to delete all of the rows in the fragment to be dropped before dropping the fragment the index, whether detached or not, should not be affected by the drop operation. A detached index will solve most of your access speed problem. The problem is caused by one or more of several 'features' of ODS 7.[12]x most of which seem to have been fixed in 7.3 (at least according the the plan for 7.3). If you ORDER BY data must be collected from all fragments before the sort can begin, depending on PDQPRIORITY this may be a sequential operation. ODS always sorts if the order by clause does not match a detached, non-fragmented, index even if only one fragment is searched and the fragmented index matches the ORDER BY clause (this is because of slow ORDERED MERGE code in 7.1 which was dropped in favor of this sort scheme, ORDERED MERGE returns in 7.3 with a newer, faster, algorithm). ESQL/C & 4GL applications searching a fragment with a replaceable parameter filtering a column included in the FRAGMENT BY expression will always search all fragments because 7.1x & 7.2x determine the fragment elimination at optimization (read PREPARE) time when the value of the replaceable parameter is not known (this is also to be fixed in 7.3 by deferring the decision until OPEN time). There are a few others that I know of and I'm sure that others also have hit a few, but these are the highlights. Bottom line try the detached non-fragmented index. Art S. Kagel