Re: Update statistics from "generation program" question

From: Doug McAllister <doug.mcallister@nospam.fmr.com> > >Given the following table definition: > >create table acct_nbr_srch_keys > ( > t_acct_num char(10), > fund_num char(3), > acct_num char(13), > doc_id integer not null , > fldr_id integer not null > );> >create unique index acctnbr_s_keys_pk on acct_nbr_srch_keys >(acct_num,fund_num,t_acct_num,doc_id);> >create index acctnbr_s_keys_ix1 on acct_nbr_srch_keys >(t_acct_num,acct_num,fund_num,fldr_id,doc_id); >create index acctnbr_sk_doc_fk on acct_nbr_srch_keys (doc_id); >create index acctnbr_s_keys_ix2 on acct_nbr_srch_keys (fldr_id);> >I am curious to understand why the update statistics statements are >generated as follows: > >UPDATE STATISTICS MEDIUM FOR TABLE acct_nbr_srch_keys DISTRIBUTIONS>ONLY; >UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (t_acct_num)>DISTRIBUTIONS ONLY; >UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (t_acct_num, >acct_num, fund_num, fldr_id, doc_id); >UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (acct_num)>DISTRIBUTIONS ONLY; >UPDATE STATISTICS LOW FOR TABLE acct_nbr_srch_keys (acct_num, fund_num, >t_acct_num, doc_id); >UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (doc_id); >UPDATE STATISTICS HIGH FOR TABLE acct_nbr_srch_keys (fldr_id);> >Specifically, why are there multiple updates for the same columns (look >at the two LOW statements). >I understand the HIGH's. There's an interestling little buglet in UPDATE STATISTICS HIGH. You would think it does an implicit UPDATE STATISTICS LOW, wouldn't you? Well, it doesn't under certain circumstances (I think it's if you're US'ing a single column, but I don't really remember). >This table contains 31,000,000 rows and this update statistics job takes >15 hours to complete on a six-way Sun machine. I would like to shave any >time off the execution that I can. Maybe if you did a multi-column US instead, you might not need the extra US LOW. But I'm not sure. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

Obnoxio The Clown wrote: [SNIP] > There's an interestling little buglet in UPDATE STATISTICS HIGH. You would > think it does an implicit UPDATE STATISTICS LOW, wouldn't you? Well, it > doesn't under certain circumstances (I think it's if you're US'ing a single > column, but I don't really remember). > If the column list is a superset of an index then that index's sysindex record will be updated and all of the listed columns will have their syscolumns stats (colmax & colmin) updated. > > >This table contains 31,000,000 rows and this update statistics job takes > >15 hours to complete on a six-way Sun machine. I would like to shave any > >time off the execution that I can. > > Maybe if you did a multi-column US instead, you might not need the extra US > LOW. But I'm not sure. This is the reason that for a single column index, if the columns has not already been updated LOW as part of some other index, dostats does ONLY the HIGH leaving off the DISTRIBUTIONS ONLY clause since the HIGH will properly update the LOW stats. The DISTRIBUTIONS ONLY clause is included otherwise to avoid performing all of the LOW counting that cannot be used to update sysindexes anyway. Art S. Kagel