Re: Database Setup

>> On Fri, 18 Aug 2000 00:57:15 GMT, jrw3319@gis.net (John Welch) wrote: >> >> >Hello all, >> > Here are a few more questions to all Informix gurus from an Informix >> >newbie. >> >... >> >> First, I want to thank everyone who responded to my question. >> Now for a couple of follow up questions/comments: >> >> Of the three areas of concern that I highlighted, the lack of >> constraints is the least of my worries. I know for a fact that >> validation is being done within the 4GL code. I guess in moving to a >> Relational Database system I thought that one of the primary things >> that made it "relational" was the definition of primary and foreign >> keys. >> As far as the extent size, one of the reasons for my concern is in >> the conversion of data from our existing system to the new system. >> Some of the files that we will be converting have 10's of thousands, >> and in some cases 100's of thousands (i.e. history files) records. So >> my concern is that right of the gate, after converting data, we will >> already have several extents for some tables. For example, our item >> file has approixmately 20,000 records. The row size for one of the >> item tables in the new system is 359. Using a formula I learned in >> class, if I did the math right, I come up with an extent size of >> around 6MB for this table. If we leave the extent size as is (16k) I >> figure that right after the conversion we will have over 300 extents >> for this one table, if my logic is correct. Except that as you load the table adjacent extents will 'join up' to become one. We loaded a much larger db via dbimport and when it was finished (before they started using it) the only tables with more than 1 extent were system tables, and it's unavoidable for those tables. What we did to control this though was to create separate dbspaces for each of the very dynamic tables in the db - about 10 in our case. If there is only 1 table in a dbspace there is only 1 extent unless you have to extend the dbspace by adding a chunk. We do have other tables now with lots of extents but performance problems are mainly down to the design of one or two parts of the application (which is being addressed), plus the ability of something somewhere to sporadically run 'the mother of all SQLs' which reduces %rcache to 0.00! >> This same type situation >> exists for several tables in the new database. Considering this >> information, should I still go with the wait and see approach or is it >> worth going through the exercise of calculating and changing extent >> sizes prior to conversion? >> Finally, most of the responses focused on extent sizes. How about >> the issue of defining a separate space for indexes? Is this even >> worth pursuing, or is this one of those "in theory" only areas. PS please stick to posting in plain text.