Re: Index full-same problem/diff situation

Angela Neal wrote: > > Hi, > > Just read the other threads about full indexes. My scenario's a bit > different and I'd appreciate some input on how to deal with this > situation. > > I have a table fragmented across multiple dbspaces (1 wk data/dbspace). > Each dbspace has 2 chunks. Table was sized so that a single data and its > index extent claimed almost the entire chunk. The problem is that the > index pages allocated (ex:48652 pgs) are completely consumed way before > the data pages are used (ex:178203 free pgs). This in turn causes data > loads to fail. The number of rows actually loaded into the same number > of index pages also differs from week to week. > > When I rebuild the index after it decides its full, a large number of > pages are free'd up and I can continue loading my data. I have already > allocated additional space and the problem persists, just leaving more > data pages empty. Rebuilding the index weekly or allocating more space > aren't very appealing options. > > Any suggestions?? I would suggest detaching the index into a separate set of dbspaces and create it with a VERY low FILL FACTOR which will prevent or reduce the node splits. Rebuild the index weekly with the same low FILL FACTOR. Placing the index into separate dbspaces from the data will prevent the one from sucking up the space from the other. Art S. Kagel