Re: Index uses more pages after unload/load (?)

Schaeflein, Paul wrote: > > I have a table that occupied 9543 data pages and 1320 index pages. The > table was unloaded using the "UNLOAD TO" statement. I had cause to drop and > re-load the table, and I specified the initial extent as 10863 pages > (converted to kbytes). The resulting table filled 11171 pages (9543 data, > 1628 index). > > My guess is because of the FILLFACTOR setting. Previously, the index pages > were closer to 100% full, and now they are at 90%. Is this correct? Should > I be looking at something else? Nope you almost hit the nail on the head! The natural loading of the table, or some prior reorganization, created nearly full index pages while the newly created table was a) created the default fill factor and b) the particular order of arrival of keys during the reload caused many nodes to be split creating two ~50% used pages which did not fill. Dropping the indexes and rebuilding with a greater fill factor (99 or 100?) will compress the table. Next time you reorganize this table do not create the indexes until after the reload or use ALTER FRAGMENT. Art S. Kagel