Re: Table creation advice

Art S. Kagel wrote: > > 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? > > It depends on the placement of the chunks on disk. Performance is best > in the center of the drives so judge that way. For the server it does > not matter much the order. > > Art S. Kagel Personally, I'm of the opinion that in an OLTP system, performance is mostly a function of cache hit ratio. Trying to physically place tables or rows within tables optimally on a disk is difficult, time consuming, and generally very low return per hour invested. Buy memory, it's cheap. DSS type servers are different animals however, but not relevant here. greg

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? It depends on the placement of the chunks on disk. Performance is best in the center of the drives so judge that way. For the server it does not matter much the order. Art S. Kagel