Re: max number of extents

> I am confused by max number of extents. Many of us have been over the years. 8-o > When I finderr on 136 it tells me the max number is between 200 and 50??????? Ya. > I found a formula to calculate max extents and the number is over 1,000 on every > table. > Which is right? I dunno, depends on the formula you found. > What about tables that are fragmented by round robin - is anything different? No difference, except the max # of extents is per fragment. > Any help anyone can give me with this would be appreciated very much as I am > getting desperate! Here's what I can tell you. There is a maximum number of extents because the list of extents is kept on the TABLESPACE TABLESPACE page of the partition (read table or fragment) which is reported by the sysmaster:sysextents table. The reason that there is a maximum is that the entire list must fit on that one page. The reason the max is different from table to table is that the extent list has to share the space remaining after the partitions header info (reported in sysmaster:sysptnhdr) with descriptions of 'special' columns. A special column is any variable length column, so the more varchar, lvarchar, BLOB, SLOB, etc. type columns a table has the fewer extents it can keep track of. Note that having more than 30 extents is probably detrimental to performance unless data locality limits the data typically accessed to only a few of those extents. In general if you have many extents in a partition, it is time to reorg the table to compress it. If you have many extents made up of most or all of a chunk, then it's time to recreate the table in dbspace(s) with larger chunks (9.4 relieves us of the 2GB chunk size limit). Art S. Kagel