Re: How can we avoid reclustering some indexes ??

Sorry, your mail is so long, it seems to have blown out the marking in Hotmail. I've marked my comments with a ***> *** We have a 24/7 OLTP application with very script constraints in terms>of response time. We are dealing with complex queries and quite huge tables>(in terms of OLTP application). In order to maintain a good response time,>one of our table has to be clustered, but after some time, index has to be >reclustered due to the fact clustering is not maintained. We are looking for>some solutions in order to avoid having to do such reorganisation (which >requires some outages ...). More complete description of our application :>============================================== Our Informix application is>in Production for a week monthes. This is a 24/7 application. Database size>= 8 giga (only used space and not allocated one). Average frequency of the>transactions = 3 transactions per second. Our biggest table contains>20Million of records (3 gig of data), and contains 7 indexes (we cannot do >less : 4 giga of indexes).We have some very strict performance constraints, >and are executing a very heavy query (involving about 20 tables of our model, >as well as this "critical table") which should get a reply in less than 800 >ms. The index used by this query (when accessing the big table) is clustered>(this is a multicolumn index : 4 columns). This allows us to ensure a good>response time by guaranteeing some sequential reads of the data pages. When>index is "fresh created", response time is fine (<400 ms), after some weeks >of activity, response time degrades and reach the limit acceptable.This is >normal due to the way index is less and less efficient (clustering is not >maintained), and mainly because the number of data pages to read is >increasing (records to read not sequential anymore, and spread in N pages). >... I have forgotten to mention that every night, we have some massive batch >processes deleting + recreating (but not updating .... :-( ) an average of >800.000 records in the big table. Basically, in 1 month our table of 20M of>records is re-generated. Question : ======== We are able to monitor quite>well the degradation of the performances by analysing the evolution of : *>performances, * disk/buffer read/write activity * growth of the>database indexes. When performance is not acceptable anymore, our solution>consists on "Re-clustering" the main used index. This obliges us to have>outages of our system once every two monthes in average. WE WOULD LIKE TO>KNOW HOW IT COULD BE POSSIBLE TO RECLUSTER LESS OFTEN. Any idea ?>Additional information : ======================== We have Informix>7.23.UC1.S6 We have HP servers - HP-UX V10 ***> What is the exact version of HPUX? We do not use kaios (pb with our>release of IFX). We use EMC disks. Indexes are isolated in separate dbspaces.>"Critical table (20M)" is also within a specific dbspace. We cannot restrict>the update activity of the system. We are not able to replace the>"DELETE+INSERT" by "UPDATE". ***> This might not help anyway, depending on what gets changed by the "update" process. Our plans : =========== We are currently>working on two points : * See if we can fragment our big table in a>clever way, in order to force Informix to always ensure some "related" >records are always located on the same part of the disk.This is perhaps the >first solution we will implement (we are now working on a strategy for >splitting in a clever way our big table). ***> This is definitely a good starting point, but will not ensure that the data's "congruence". * Try to see if we can >replace the DELETE+INSERT statements by UPDATE ones (this is a long term >solution which will oblige us to change a lot of things in our code). ***> Hm. Perhaps if we could see the table schema(s) and the query you're trying to process, it might help. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com