Re: Update statistics from "generation program" question

First off you will find that using HIGH will give you VERY LITTLE... let me repeat... "very little" over update med. You may want to remove the low's and do med instead of high, and run high with distributions only for all columns that head an index or not at all. In addition, you may even want to alter the sampling rate by adjusting the confidence and resolution parameters. Good luck. >From: Doug McAllister <doug.mcallister@nospam.fmr.com> >Reply-To: Doug McAllister <doug.mcallister@nospam.fmr.com> >To: informix-list@iiug.org >Subject: Update statistics from "generation program" question >Date: Thu, 30 Mar 2000 09:59:12 -0500 > >This is a multi-part message in MIME format. >--------------AD06AC18049333F604EE0DF3 >Content-Type: text/plain; charset=us-ascii >Content-Transfer-Encoding: 7bit > >Hi all.. > >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. > >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. > >thanks as always..... > >--------------AD06AC18049333F604EE0DF3 >Content-Type: text/x-vcard; charset=us-ascii; > name="doug.mcallister.vcf" >Content-Transfer-Encoding: 7bit >Content-Description: Card for Doug McAllister >Content-Disposition: attachment; > filename="doug.mcallister.vcf" > >begin:vcard >n:McAllister;Doug >tel;work:(603) 791-5688 >x-mozilla-html:TRUE >url:www.fidelity.com >org:Fidelity Investments >version:2.1 >email;internet:doug.mcallister@fmr.com >title:Consulting DBA >adr;quoted-printable:;;2 Contra Way=0D=0AT1Q;Merrimack;NH;03054;USA >fn:Doug McAllister >end:vcard > >--------------AD06AC18049333F604EE0DF3-- > ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com