Re: Maximum Number Of Extents

Mister Creosote wrote: > > Could someone out there let me know the maximum number of extents > allowable for a non-system table. Our production database is growing > rather more rapidly than expected and we have a number of tables with > in excess of 50 extents. I would very much appreciate "the official > line" with regard to the impact of having so many extents. I hope to be > able to persuade the powers that be to let me reorganise (i.e. allocate > initial extents etc) this database in the near future, but need some > ammunition to better my case. > The answer is . . . . it depends. We hit the upper limit on the number of extents (like a bird on a window) for a given table (test system, whew!!) at 206. Much depends on the width of the table; unfortunately, I've never seen a hard limit. Other benefits of reorgs would be: o Optimizing storage sizes. o Recreating indices also improve performance (not sure about SE, though). o Hot table placement -- depending on your disk layout. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

"Carlson@WHSmith" wrote: > > Mister Creosote wrote: > > > > Could someone out there let me know the maximum number of extents > > allowable for a non-system table. Our production database is growing > > rather more rapidly than expected and we have a number of tables with > > in excess of 50 extents. I would very much appreciate "the official > > line" with regard to the impact of having so many extents. I hope to be > > able to persuade the powers that be to let me reorganise (i.e. allocate > > initial extents etc) this database in the near future, but need some > > ammunition to better my case. > > > > The answer is . . . . it depends. It depends on the Informix page size and the number of 'special' columns. Each extent and each special column need an entry on the table's (or its fragment's) TBLSPACE TBLSPACE page which is fixed at the size of a page in your release (either 2K or 4K). The more special columns (VARCHAR, NVARCHAR, LVARCHAR, NLVARCHAR, TEXT, BYTE, etc) the fewer extents the page will have room for. The solution is to reorg the table to fewer extents, alter the columns to fixed length (ie CHAR) or both. Art S. Kagel > We hit the upper limit on the number of extents (like a bird on a > window) for a given table (test system, whew!!) at 206. Much depends on > the width of the table; unfortunately, I've never seen a hard limit. > > Other benefits of reorgs would be: > o Optimizing storage sizes. > o Recreating indices also improve performance (not sure about SE, > though). > o Hot table placement -- depending on your disk layout. > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */

In article <39204A54.1DB0FC3C@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >"Carlson@WHSmith" wrote: >> >> Mister Creosote wrote: >> > >> > Could someone out there let me know the maximum number of extents >> > allowable for a non-system table. Our production database is growing >> > rather more rapidly than expected and we have a number of tables with >> > in excess of 50 extents. I would very much appreciate "the official >> > line" with regard to the impact of having so many extents. I hope to be >> > able to persuade the powers that be to let me reorganise (i.e. allocate >> > initial extents etc) this database in the near future, but need some >> > ammunition to better my case. >> > >> >> The answer is . . . . it depends. > >It depends on the Informix page size and the number of 'special' columns. >Each extent and each special column need an entry on the table's (or its >fragment's) TBLSPACE TBLSPACE page which is fixed at the size of a page in >your release (either 2K or 4K). The more special columns (VARCHAR, >NVARCHAR, LVARCHAR, NLVARCHAR, TEXT, BYTE, etc) the fewer extents the page >will have room for. The solution is to reorg the table to fewer extents, >alter the columns to fixed length (ie CHAR) or both. > >Art S. Kagel > True, we usually hit problems at 120+ -- David Williams