Re: Ex Size

On Thu, 29 Jan 1998, Fergal Flanagan wrote: > Hi Art, > Just a quick query! > Ive just dome some extent sizing on tables of around 1.5 million > records. Obviously my computations are based upon the uniqueness of index > entries, so they come in at around 399072 - 450000Kb. > Should I create the whole table as one extent or split into first/next > extent sizes, bearing in mind that these tables will grow to around 3 > million in less than 1 year. > Some say to create 1 extent. What do you think? I'm posting this to c.d.i. as the answer is generally informative. Keep in mind that you can only get one extent if the current chunksize exceeds the extent size otherwise you get one extent per chunk. Beyond that if you are building the table from scratch, and there is no other table expansion happening in the dbspace, all extents that are contiguous will be compressed into a single extent anyway. Fewer extents is better than more extents, FEW extents is better than MANY extents but a few more extents is harmless. Try for a single extent, especially if the table will be growing and make the NEXT SIZE large enough to hold significant numbers of rows that are likely to be fetched concurrently. In other words if noone is interested in more than one months data at any one time then extents large enough to hold an entire month's data are as good as a single extent and if a running month is needed as good as two extents. This is especially true if you detach any indexes that are likely to be built such that their pages are not in the same extent as the data. In other words if the data is loaded chronologically, for example, only a chronological index should be attached. This is because new nodes will definitely be created for new data and will likely be localized with the data pages on disk (consider any strictly increasing keys as chronological) other index pages will tend to be non-local and new insertions may cause existing index nodes to split causing previously localized index pages to move to a new extent and become non-local. Detached indexes have a hard limit on the number of extents that they can have which is CALCULATED from the keysize and NEXT SIZE of the table so a larger next size for active tables with detached indexes is critical or the detached indexes will eventually run out of extents and become DISABLEd. Hope this helps. Art S. Kagel, kagel@bloomberg.com