Re: indexes and extents

On Thu, 9 Oct 1997, Mark Collins wrote: } > > That makes me wonder why the original poster has multiple extents. Has there been a } lot } > > of insert activity on the table after the indexes were created? If so, try DROP } > > INDEX/CREATE INDEX to rebuild them. Or are there multiple small chunks in the } dbspace } > > that contains the indexes? Remember that an extent can not span chunks, so that can } > > cause multiple extents. } > } > No I got hit with this one. It is tempting when detaching indexes to } > make a dbspace to hold the detached indexes from one or more tables. If } > more than one index is here and the calculated extent size of the index } > is releatively small and the tables are growing then the index will } > have many extents. Indeed we had one index that had to be dropped and } > rebuilt, after enlarging the next size of the table, becuase the engine } > would not add any more extents to the index. } > } > Art S. Kagel } } Ah, yes, I was assuming that we were only dealing with the indexes from a single table } in this separate dbspace. I don't normally mix indexes from multiple tables, and thus } forget that others might. Even multiple detached (and that is the keyword here) indexes from the same table in the same dbspace can suffer this fate. ODS records detached indexes in the tablespace tablespace and in sysmaster as if they were separate tables so extents of separate detached indexes, regardless of what table they belong to, cannot share extents or compress their extents together. Art S. Kagel, kagel@bloomberg.com