RE: number of extents

Bill Hamilton asked: > Should I try to get the number of extents down on system tables? > Also, on automatic indexes, when should you worry about how many extents > they are in? Art S. Kagel responded: > Not to worry. All system catalog tables are cached in the data dictionary > cache so extent fragmentation in the catalog is not a problem since about > IDS 7.1. I hate to disagree, but I have seen secondary effects of too many extents. One is that it can become impossible to drop a chunk because it has system catalogue extents in it. There may also occasionally be a problem when the number of extents causes other tables to become interleaved because they cannot get a sufficiently large extent. These are not necessarily a huge performance impact, but they can be an administrative problem. The reason I want to bring this up is that there is not a good, safe way to compact the system catalogue tables other than to drop and recreate the database, which is not necessarily an option. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. sending to informix-list

Christopher Coleman wrote: > Bill Hamilton asked: > >>Should I try to get the number of extents down on system tables? >>Also, on automatic indexes, when should you worry about how many extents >>they are in? > > > Art S. Kagel responded: > >>Not to worry. All system catalog tables are cached in the data dictionary >>cache so extent fragmentation in the catalog is not a problem since about >>IDS 7.1. > > > I hate to disagree, but I have seen secondary effects of too many extents. One is that it can > become impossible to drop a chunk because it has system catalogue > extents in it. There may also occasionally be a problem when the > number of extents causes other tables to become interleaved because > they cannot get a sufficiently large extent. > > These are not necessarily a huge performance impact, but they can be an > administrative problem. You are correct Chris. I was focused on the performance question, but there are the administrative issues to deal with as well. > The reason I want to bring this up is that there is not a good, safe > way to compact the system catalogue tables other than to drop and > recreate the database, which is not necessarily an option. Yup. Art S. Kagel

Yes if you have lots of columns and tables I would a) create an empty database b) alter table systables next size .... alter table syscolumns next size... c) alter anyother tables that make have a large number of rows in once the complete schema is built. e.g. sysprocbody, systrigbody etc. Note IDS 10 has onconfig parameters to also handle TABLESPACE TABLESPACE extents. I wonder if the same has been considered for the Database Tablespace? Do any enviroments create a massive amount of databases?