Extents: Reality and Myth

I am in the process of re-sizing a database, and revisited the topic of extents. We are running IDS 7.30 UC7, RS/6000 w/8 cpus, AIX 4.3, and we use a disk volume manager. I've looked at FAQ and several books, including Joe Lumbley's "Informix - DBA Survival Guide". Given all this, I still have questions. Would someone please fill in the blanks, and them some: All this pertains to IDS 7.3X: 1.The maximum number of extents that a table can have is _____. 2. If there is in fact a maximum number of extents, when that is reached the engine will ___________________________________. 3. If the maximum number of extents is very very large (don't know this until someone answers question 1.), the benefits of minimizing the number of extents allocated is _____________________________________. 4. If a dbspace is dedicated to a single table, that table will have one and only extent regardless of what extent size and next extent size is set to: ___ True ___ False. 5. Any Comments about extents, determining their size, etc: _____________________________________________________________ _____________________________________________________________ Any clarification will be helpful. No tap dancing please. Vic

"Victor Glass" <vglass@mailbox.bellatlantic.net> wrote in message news:38CF84F9.C095042E@mailbox.bellatlantic.net... > I am in the process of re-sizing a database, and revisited the topic of > extents. We are running IDS 7.30 UC7, RS/6000 w/8 cpus, AIX 4.3, and we > use a disk volume manager. I've looked at FAQ and several books, > including Joe Lumbley's "Informix - DBA Survival Guide". Given all this, > I still have questions. Would someone please fill in the blanks, and > them some: > > All this pertains to IDS 7.3X: > > 1.The maximum number of extents that a table can have is _____. (it depends on how many indexes and special columns the table has. There is a table definition page (tblspace tblspace) that holds all this info. When full, it is all over for the table. The normal value is probably around 250.... > 2. If there is in fact a maximum number of extents, when that is reached > the engine will ___________________________________. Don't know --- suspect that table inserts will fail. > 3. If the maximum number of extents is very very large (don't know this > until someone answers question 1.), the benefits of minimizing the > number of extents allocated is _____________________________________. Well, it reduces the path length to determine which physical extent a logical page is in. > 4. If a dbspace is dedicated to a single table, that table will have one > and only extent regardless of what extent size and next extent size is > set to: ___ True ___ False. -- False --- if the dbspace extends to multiple chunks, each chunk will require an extent. Also, if the dbspace was not INITIALLY dedicated to the table, but now contains gaps where other tables used to be, using those gaps will probably require an extent, since an extent must be a contiguous set of logical pages. > 5. Any Comments about extents, determining their size, etc: To determine the extent size, use oncheck -pT (or -pt) to print the table info. A list of all extents will be produced. You may want to consider taking the 'internals' class. Excellent class that goes into a LOT of the physical storage of the database. > > Any clarification will be helpful. No tap dancing please. > > Vic >

Victor Glass wrote: > > I am in the process of re-sizing a database, and revisited the topic of > extents. We are running IDS 7.30 UC7, RS/6000 w/8 cpus, AIX 4.3, and we > use a disk volume manager. I've looked at FAQ and several books, > including Joe Lumbley's "Informix - DBA Survival Guide". Given all this, > I still have questions. Would someone please fill in the blanks, and > them some: > > All this pertains to IDS 7.3X: > > 1.The maximum number of extents that a table can have is _____. It depends on the number and size of indices, varchar fields, etc. We had a problem (in test environment, thankfully) when a wide table reached 206 extents. > 2. If there is in fact a maximum number of extents, when that is reached > the engine will ___________________________________. Stubbornly refuse to allow any more inserts into the table. I don't remember the exact error number. > 3. If the maximum number of extents is very very large (don't know this > until someone answers question 1.), the benefits of minimizing the > number of extents allocated is _____________________________________. Decreasing the time needed for the data to be read from disk. > 4. If a dbspace is dedicated to a single table, that table will have one > and only extent regardless of what extent size and next extent size is > set to: ___ True ___ False. Again, it depends. As long as the dbspace takes up only one chunk, then the answer would be true. If the dbspace spans chunks, it would be false. And it also depends on what freespace was left after the tables originally in the dbspace were moved; each of those freespaces would be an extent. > 5. Any Comments about extents, determining their size, etc: > _____________________________________________________________ > _____________________________________________________________ > oncheck -pT <dbsname>:<tablename> is a good start.> Any clarification will be helpful. No tap dancing please. I'm not a good dancer anyway . . .. 8-) -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */