Re: Table creation advice

I don't think that it will make a significant difference either way. Don Hartshorn wrote: > I need some opinions on general table creation guidelines within a > database. Assume that I'm creating all the tables in one dbspace. > Here's the situation: about half the tables are static lookup > tables (like state fields, military rank, whatever), and about half are > dynamic data tables, with initial exents sized to keep data and indexes > together for about one calendar year. The static tables are generally > very small, both in column size and number of rows; the dynamic tables > are larger, especially in number of rows. The database will be used for > OLTP. Assume the ONCONFIG is tuned appropriately and properly for the > platform and OLTP. > Question: Is is better for performance to place the smaller static > tables first in the creation schema, or the larger dynamic tables? I've > thought about this one and convinced myself both ways. Any thoughts/ > opinions? > > Thanks, > -- Don H.

In article <3696D0CC.9F745140@informix.com>, Madison Pruet <mpruet@informix.com> writes >I don't think that it will make a significant difference either way. > >Don Hartshorn wrote: > >> I need some opinions on general table creation guidelines within a >> database. Assume that I'm creating all the tables in one dbspace. >> Here's the situation: about half the tables are static lookup >> tables (like state fields, military rank, whatever), and about half are >> dynamic data tables, with initial exents sized to keep data and indexes >> together for about one calendar year. The static tables are generally >> very small, both in column size and number of rows; the dynamic tables >> are larger, especially in number of rows. The database will be used for >> OLTP. Assume the ONCONFIG is tuned appropriately and properly for the >> platform and OLTP. >> Question: Is is better for performance to place the smaller static >> tables first in the creation schema, or the larger dynamic tables? I've >> thought about this one and convinced myself both ways. Any thoughts/ >> opinions? >> >> Thanks, >> -- Don H. > > Sounds good, if the dynamic tables get frequently reorganised (dropped and reloaded) then creating the static tables first may help keep extent fragmentation under control (or may not). What I'd recommend, though, is to place such tables in their own database and use synonyms from the main DB to point to the "defaults" database. By doing this, you can: 1. export the small defaults database separately from the main (large) DB; 2. control changes to these static tables better (IMHO), with development, test and prod versions of the defaults databases; 3. we have many development databases (in three instances), but we point them to one defaults database to make sure they all run with the same set of parameters/lookups and thus get consistent results. That's all I can think of .... TTFN -- Simon Barber