Update statistics

Wayne, While yes, it does delete the distributions in the sys tables, it also performs an update statistics low on each table. Oddly enough, update statistics low (gathering basic table and column statistics, not distributions), can take longer than update statistics medium (sample distributions). This is what is taking so long. I'm unaware of a way to get Informix to drop the distributions without running update statistics low, though that would be very useful for large databases whose distributions get corrupted somehow. Any other insights into this? --John Bejarano. --- "Zablatzky, ...." <Wayne.Zablatzky@ubs.com> wrote: > > Why would "update statistics drop distributions" > take several hours.....? > And it is still running.... I would have assumed > that this would just > basically be a delete in the sys tables. Am I > wrong? > > > > Please do not transmit orders or instructions > regarding a UBS account by > email. The information provided in this email or any > attachments is not an > official transaction confirmation or account > statement. For your protection, > do not include account numbers, Social Security > numbers, credit card > numbers, passwords or other non-public information > in your email. Because > the information contained in this message may be > privileged, confidential, > proprietary or otherwise protected from disclosure, > please notify us > immediately by replying to this message and deleting > it from your computer > if you have received this communication in error. > Thank you. > > UBS Financial Services Inc. > UBS International Inc. > > > >

There is a way to drop distributions without running 'update statistics low' You may use the SQL command: DELETE FROM sysdistrib WHERE tabid = (SELECT tabid FROM systables WHERE tabname = '<the_table_in_question>'); Disadvantage of this approach is that this is a hack rather than the officially supported method. Distributions that a re in the distributions cache will stay there and get used by the optimizer until IDS is brought down and restarted. Best regards Tilman -- Tilman Model-Bosch IBM Data Managment Solutions, Informix Advanced Support c\\\\o SAP AG TECHDEV 05 Neurrotstr.16 69190 Walldorf forum.subscriber@iiug.org wrote on 19/03/2005 09:19:36: > Wayne, > > While yes, it does delete the distributions in the sys > tables, it also performs an update statistics low on > each table. Oddly enough, update statistics low > (gathering basic table and column statistics, not > distributions), can take longer than update statistics > medium (sample distributions). This is what is taking > so long. I'm unaware of a way to get Informix to drop > the distributions without running update statistics > low, though that would be very useful for large > databases whose distributions get corrupted somehow. > Any other insights into this? > > --John Bejarano. > > --- "Zablatzky, ...." <Wayne.Zablatzky@ubs.com> wrote: > > > > Why would "update statistics drop distributions" > > take several hours.....? > > And it is still running.... I would have assumed > > that this would just > > basically be a delete in the sys tables. Am I > > wrong? > > > > > > > > Please do not transmit orders or instructions > > regarding a UBS account by > > email. The information provided in this email or any > > attachments is not an > > official transaction confirmation or account > > statement. For your protection, > > do not include account numbers, Social Security > > numbers, credit card > > numbers, passwords or other non-public information > > in your email. Because > > the information contained in this message may be > > privileged, confidential, > > proprietary or otherwise protected from disclosure, > > please notify us > > immediately by replying to this message and deleting > > it from your computer > > if you have received this communication in error. > > Thank you. > > > > UBS Financial Services Inc. > > UBS International Inc. > > > > > > > > > > >

Tilman Mode.... wrote: > There is a way to drop distributions without running 'update statistics > low' > > You may use the SQL command: > > DELETE FROM sysdistrib > WHERE tabid = > (SELECT tabid FROM systables > WHERE tabname = '<the_table_in_question>');> > Disadvantage of this approach is that this is a hack rather than the > officially > supported method. > Distributions that a re in the distributions cache will stay there and get > used by the > optimizer until IDS is brought down and restarted. There is a solution for this too: Run some grant or revoke statement against the table. This will make the database engine reload the distributions for this table from sysdistrib the next time they are needed by some query. In our case they will then be flagged as not present in the dictionary cache. Michael > > Best regards > Tilman > > -- > Tilman Model-Bosch > IBM Data Managment Solutions, Informix Advanced Support > c\\\\o SAP AG > TECHDEV 05 > Neurrotstr.16 > 69190 Walldorf > > === Michael Mueller ================= Web: http://www.michael-mueller-it.de =====================================

Michael Mue.... said: > Tilman Mode.... wrote: >> There is a way to drop distributions without running 'update statistics >> low' >> >> You may use the SQL command: >> >> DELETE FROM sysdistrib >> WHERE tabid = >> (SELECT tabid FROM systables >> WHERE tabname = '<the_table_in_question>');>> >> Disadvantage of this approach is that this is a hack rather than the >> officially >> supported method. >> Distributions that a re in the distributions cache will stay there and >> get >> used by the >> optimizer until IDS is brought down and restarted. > > There is a solution for this too: Run some grant or revoke statement > against the table. This will make the database engine reload the > distributions for this table from sysdistrib the next time they are > needed by some query. In our case they will then be flagged as not > present in the dictionary cache. I can just see this going down a treat back in IBM Tech Support... :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco Travel broadens a person. You look as if you have been all over the world. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed http://i2.photobucket.com/albums/y41/Obnoxio/thinkIfoundtheproblem.jpg