Questions on extent ...

1. How to retrieve the number of extents used by a table from any of the sysmaster tables or sysXXXX table ? 2. Is there a maximum number of extents for a table ? 3. Is it TRUE that whenever a table has more than 8 extents (regardless of the size of the extent), the accessing time to the table will increased ? thanx ...

Wu wrote: > 1. How to retrieve the number of extents used by a table from any of > the sysmaster tables or sysXXXX table ? The number of extents is on a partition table. For a fragmented table you will need to check each of the fragments. You can do a "select ti_nextns from systabinfo where ti_partnum = <partition number>. > > > 2. Is there a maximum number of extents for a table ? Yes, but it is dependent on the page size and the number of indexes on the table. On a 2K page system such as Solaris, I wouldn't start to worry too much about reaching the limit until I had arround 80 extents. > > > 3. Is it TRUE that whenever a table has more than 8 extents > (regardless of the size of the extent), the accessing time to the table > will increased ? Not quite true any more. In lev.5 we made a big deal about the 8 extent limit because we had a incore table which was sized to hold only 8 extents. This meant that we had to do extra work to get to any row that was not contained within the first 8 extents. In current versions this incore table is dynamically increased so that it holds the entire extent list for any partiion. > > > thanx ...