Re: Index, Foreign Keys, and Fragmented Tables?

>From: "Russell Bierschbach" <rbierschbach@simpletel.com> >Reply-To: "Russell Bierschbach" <rbierschbach@simpletel.com> >To: informix-list@iiug.org >Subject: Index, Foreign Keys, and Fragmented Tables? >Date: Wed, 12 May 1999 12:54:13 -0500 > >My database currently has two large tables (and a couple hundred small >ones). I'm in the process of moving the database to a new server, and >fragmenting these large tables accross several dbspaces at the same time. >I >saw previous posts about using the High Performance Loader to load the >tables (without indexes or foreign keys applied), then creating indexes on >the tables (in a seperate dbspace). My first question is, after I load the >tables, and create the indexes, what is the correct syntax to modify those >indexes to actually be foreign keys to other tables? Add constraint - foreign keys after creating the index on the same columns then Informix will use your indexes for these foreign keys: e.g ALTER TABLE tab1 ADD CONSTRAINT (FOREIGN KEY (col1, col2) REFERENCES tab2 ON DELETE CASCADE CONSTRAINT yourname) >Another Question: I have attempted this procedure before (except I never >applied foreign keys to the tables, just indexes) and thought I was >finished, so I switched the new server/database to be the "live" system for >a little while, and checkpoint lengths skyrocketed from 0-1 seconds every 5 >minutes to 15-20 seconds every 2 minutes (which is a bad thing when records >have to be updated live). I attempted to make a couple modifcations to the >onconfig file (changing LRU MIN/MAX down to 0/1 from 2/5 and such), but I >was unable to make sufficient improvements quickly enough to leave the >system "live". What's puzzling to me is that the new system is basically >identical to the old system except the processors on the new system were >240 >Mhz vs. 180 MHz (both dual processor HP PA-RISC). The operating systems >are >basically an identical setup as well. Are there other configuration/tuning >parameters that I really need to watch when I have tables fragmented >accross >mulitple dbspaces, as opposed to in one single dbspace? I would use onstat -rl to check the percentage use of physical log file. If during checkpoint the percentage is about 75% then your physical log is too small to contain all the before images on a heavy activity OLTP environment within your CKPTINTVL time. Another parameter to look is BUFFERS. onstat -p will tell you read/write cache rate and buffwaits/(bufwrits+dskreads) percentage. And onstat -F will tell you if there're any FG writes which is a performence drag. > >One more disgrunted question. Why do I have to update statistics on a >table >everytime it increases from say 33 million rows to 35 million rows. What a >pain. (Yes, I already have to dostats utility). I just don't understand >why the database inserts the rows ok, and updates the indexes, but then has >to be told they are there to use the indexes properly. I don't expect an >answer on this, just needed to vent. > Same feel here. Informix likes to "divide and conquer" -- Each time doing one thing efficiently. Have a good day! Dong _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com