Update Stats on Combo Index

I would like to know about how Update statistics works in regards to combo indexes. Specifically, we update stats medium on each column that is included in an index and the primary key index as high. We seem to sometimes have slowness issues with combo indexes even directly after the stats have been updated. My question is should be do an update stats on the combo index or should the individual columns be sufficient? Thanks Lennie Informix DBA Information Technology Tax team Lake County, IL O: 847-377-2092 C: 847-309-7718 Math illiteracy affects 7 out of every 5 people.

Jarratt, Li.... said: > > > I would like to know about how Update statistics works in regards to combo > indexes. Specifically, we update stats medium on each column that is > included in an index and the primary key index as high. We seem to > sometimes have slowness issues with combo indexes even directly after the > stats have been updated. My question is should be do an update stats on > the > combo index or should the individual columns be sufficient? Please explain "do an update stats on the combo index"? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com

Combo Index: update statistics medium for table mytable(column1, column2, column3,column4); > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > Obnoxio The.... > Sent: Tuesday, January 24, 2006 9:35 AM > To: ids@iiug.org > Subject: Re: Update Stats on Combo Index [6253] > > > > Jarratt, Li.... said: > > > > > > I would like to know about how Update statistics works in > regards to combo > > indexes. Specifically, we update stats medium on each > column that is > > included in an index and the primary key index as high. We seem to > > sometimes have slowness issues with combo indexes even > directly after the > > stats have been updated. My question is should be do an > update stats on > > the > > combo index or should the individual columns be sufficient? > > Please explain "do an update stats on the combo index"? > > -- > Bye now, > Obnoxio > > "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" > - Coluche > > did i mention i like nulls? heck, i even go so far as to say that all > columns in a table except the primary key could/should be > nullable. this > has certain advantages, for example, if you need to insert a > child record > and you don't have a parent row for it, just do an insert > into the parent > table with the primary key value (everything else null), and voila, > relational integrity is preserved. but this is, admittedly, a bit > controversial among modellers. > > --r937, dbforums.com > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Jarratt, Li.... said: > > > Combo Index: > update statistics medium for table mytable(column1, column2, column3,> column4); And do you do an UPDATE STATISTICS LOW? >> -----Original Message----- >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of >> Obnoxio The.... >> Sent: Tuesday, January 24, 2006 9:35 AM >> To: ids@iiug.org >> Subject: Re: Update Stats on Combo Index [6253] >> >> >> >> Jarratt, Li.... said: >> > >> > >> > I would like to know about how Update statistics works in >> regards to combo >> > indexes. Specifically, we update stats medium on each >> column that is >> > included in an index and the primary key index as high. We seem to >> > sometimes have slowness issues with combo indexes even >> directly after the >> > stats have been updated. My question is should be do an >> update stats on >> > the >> > combo index or should the individual columns be sufficient? >> >> Please explain "do an update stats on the combo index"? >> >> -- >> Bye now, >> Obnoxio >> >> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" >> - Coluche >> >> did i mention i like nulls? heck, i even go so far as to say that all >> columns in a table except the primary key could/should be >> nullable. this >> has certain advantages, for example, if you need to insert a >> child record >> and you don't have a parent row for it, just do an insert >> into the parent >> table with the primary key value (everything else null), and voila, >> relational integrity is preserved. but this is, admittedly, a bit >> controversial among modellers. >> >> --r937, dbforums.com >> >> >> ************************************************************** >> ***************** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com

I do a medium on the entire database before doing the individual columns. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > Obnoxio The.... > Sent: Tuesday, January 24, 2006 9:45 AM > To: ids@iiug.org > Subject: RE: Update Stats on Combo Index [6255] > > > > Jarratt, Li.... said: > > > > > > Combo Index: > > update statistics medium for table mytable(column1, > column2, column3,> > column4); > > And do you do an UPDATE STATISTICS LOW? > > >> -----Original Message----- > >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of > >> Obnoxio The.... > >> Sent: Tuesday, January 24, 2006 9:35 AM > >> To: ids@iiug.org > >> Subject: Re: Update Stats on Combo Index [6253] > >> > >> > >> > >> Jarratt, Li.... said: > >> > > >> > > >> > I would like to know about how Update statistics works in > >> regards to combo > >> > indexes. Specifically, we update stats medium on each > >> column that is > >> > included in an index and the primary key index as high. > We seem to > >> > sometimes have slowness issues with combo indexes even > >> directly after the > >> > stats have been updated. My question is should be do an > >> update stats on > >> > the > >> > combo index or should the individual columns be sufficient? > >> > >> Please explain "do an update stats on the combo index"? > >> > >> -- > >> Bye now, > >> Obnoxio > >> > >> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer > sa gueule" > >> - Coluche > >> > >> did i mention i like nulls? heck, i even go so far as to > say that all > >> columns in a table except the primary key could/should be > >> nullable. this > >> has certain advantages, for example, if you need to insert a > >> child record > >> and you don't have a parent row for it, just do an insert > >> into the parent > >> table with the primary key value (everything else null), > and voila, > >> relational integrity is preserved. but this is, admittedly, a bit > >> controversial among modellers. > >> > >> --r937, dbforums.com > >> > >> > >> ************************************************************** > >> ***************** > >> Forum Note: Use "Reply" to post a response in the > discussion forum. > >> > > > > > > > ************************************************************** > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- > Bye now, > Obnoxio > > "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" > - Coluche > > did i mention i like nulls? heck, i even go so far as to say that all > columns in a table except the primary key could/should be > nullable. this > has certain advantages, for example, if you need to insert a > child record > and you don't have a parent row for it, just do an insert > into the parent > table with the primary key value (everything else null), and voila, > relational integrity is preserved. but this is, admittedly, a bit > controversial among modellers. > > --r937, dbforums.com > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Lennie: You should be doing one of the following: If you have an older engine version (Earlier than 7.31xD2 or 9.30xC3): 1. MEDIUM on all columns in the table. 2. Separate HIGH with DISTRIBUTIONS ONLY on the first column of each index key and the first column that's different if several indexes start with the same column(s). 3. LOW on the entire key of every index. - Optimization: if there's a single column index you can omit the DISTRIBUTIONS ONLY when you do the HIGH on that column and eliminate the LOW on that single column key. If you have a later, optimized, server version (ie 7.31xD2+ or 9.30xC3+ or 9.4 or 10.0): 1. Single HIGH with DISTRIBUTIONS ONLY on all columns that lead any index plus the first columns that are different when several indexes start with the same column(s). If this statement is longer than 64K break it up into two or more statements. 2. MEDIUM on all columns not listed in the HIGH run. If this statement is longer than 64K break it up into two or more statements. 3. LOW on the entire key of every index. OR - you can just get my dostats utility which implements these protocols automatically. Dostats also provides many options and features that make managing your server stats much easier. Dostats is included in the package utils2_ak available from the IIUG Software Repository. The information above is found in the Informix Performance Guide and in a paper by John Miller III at: www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/020 3miller.html Art S. Kagel ----- Original Message ----- From: Li.... Jarratt <ids@iiug.org> At: 1/24 10:21 I would like to know about how Update statistics works in regards to combo indexes. Specifically, we update stats medium on each column that is included in an index and the primary key index as high. We seem to sometimes have slowness issues with combo indexes even directly after the stats have been updated. My question is should be do an update stats on the combo index or should the individual columns be sufficient? Thanks Lennie Informix DBA Information Technology Tax team Lake County, IL O: 847-377-2092 C: 847-309-7718 Math illiteracy affects 7 out of every 5 people. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Jarratt, Li.... said: > > > I do a medium on the entire database before doing the individual columns. And do you do the HIGH before or after the MEDIUM? >> -----Original Message----- >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of >> Obnoxio The.... >> Sent: Tuesday, January 24, 2006 9:45 AM >> To: ids@iiug.org >> Subject: RE: Update Stats on Combo Index [6255] >> >> >> >> Jarratt, Li.... said: >> > >> > >> > Combo Index: >> > update statistics medium for table mytable(column1, >> column2, column3,>> > column4); >> >> And do you do an UPDATE STATISTICS LOW? >> >> >> -----Original Message----- >> >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On >> Behalf Of >> >> Obnoxio The.... >> >> Sent: Tuesday, January 24, 2006 9:35 AM >> >> To: ids@iiug.org >> >> Subject: Re: Update Stats on Combo Index [6253] >> >> >> >> >> >> >> >> Jarratt, Li.... said: >> >> > >> >> > >> >> > I would like to know about how Update statistics works in >> >> regards to combo >> >> > indexes. Specifically, we update stats medium on each >> >> column that is >> >> > included in an index and the primary key index as high. >> We seem to >> >> > sometimes have slowness issues with combo indexes even >> >> directly after the >> >> > stats have been updated. My question is should be do an >> >> update stats on >> >> > the >> >> > combo index or should the individual columns be sufficient? >> >> >> >> Please explain "do an update stats on the combo index"? >> >> >> >> -- >> >> Bye now, >> >> Obnoxio >> >> >> >> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer >> sa gueule" >> >> - Coluche >> >> >> >> did i mention i like nulls? heck, i even go so far as to >> say that all >> >> columns in a table except the primary key could/should be >> >> nullable. this >> >> has certain advantages, for example, if you need to insert a >> >> child record >> >> and you don't have a parent row for it, just do an insert >> >> into the parent >> >> table with the primary key value (everything else null), >> and voila, >> >> relational integrity is preserved. but this is, admittedly, a bit >> >> controversial among modellers. >> >> >> >> --r937, dbforums.com >> >> >> >> >> >> ************************************************************** >> >> ***************** >> >> Forum Note: Use "Reply" to post a response in the >> discussion forum. >> >> >> > >> > >> > >> ************************************************************** >> ***************** >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> >> -- >> Bye now, >> Obnoxio >> >> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" >> - Coluche >> >> did i mention i like nulls? heck, i even go so far as to say that all >> columns in a table except the primary key could/should be >> nullable. this >> has certain advantages, for example, if you need to insert a >> child record >> and you don't have a parent row for it, just do an insert >> into the parent >> table with the primary key value (everything else null), and voila, >> relational integrity is preserved. but this is, admittedly, a bit >> controversial among modellers. >> >> --r937, dbforums.com >> >> >> ************************************************************** >> ***************** >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com

Lennie, Here is an example for doing the update stats. Index_1 (A,B,C) Index_2 (A,B,D) Index_3 (E) update stat medimum on table with distributions. Update Stat High (A) Update Stat High (E) Update Stat High (C) Update Stat High (D) Update stat Medium (B) Update Stat Low (A,B,C) Update Stat Low (A,B,D) Hope this will help. Thanks Anup --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > I would like to know about how Update statistics > works in regards to combo > indexes. Specifically, we update stats medium on > each column that is > included in an index and the primary key index as > high. We seem to > sometimes have slowness issues with combo indexes > even directly after the > stats have been updated. My question is should be do > an update stats on the > combo index or should the individual columns be > sufficient? > > Thanks > > Lennie > > Informix DBA > Information Technology Tax team > Lake County, IL > O: 847-377-2092 > C: 847-309-7718 > > Math illiteracy affects 7 out of every 5 people. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

Almost, see my comments below ----- Original Message ----- From: Anup Agrawal <ids@iiug.org> At: 1/24 11:59 > Lennie, > > Here is an example for doing the update stats. > > Index_1 (A,B,C) > Index_2 (A,B,D) > Index_3 (E) > <older servers yes, for newer servers skip this> update stat medium on table (note MEDIUM implies distributions and the DISTRIBUTIONS ONLY clause is neither needed for speed nor desirable) <older servers yes>Update Stat High (A) DISTRIBUTIONS ONLY <older servers yes>Update Stat High (E) DISTRIBUTIONS ONLY <older servers yes>Update Stat High (C) DISTRIBUTIONS ONLY <older servers yes>Update Stat High (D) DISTRIBUTIONS ONLY <newer servers only substitute for the high's above> Update stats high (A,E,C,D) DISTRIBUTIONS ONLY <newer servers only substitute for the medium above> Update stat Medium (B) <yes>Update Stat Low (A,B,C) <yes>Update Stat Low (A,B,D) Art S. Kagel > Hope this will help. > > Thanks > Anup --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > I would like to know about how Update statistics > works in regards to combo > indexes. Specifically, we update stats medium on > each column that is > included in an index and the primary key index as > high. We seem to > sometimes have slowness issues with combo indexes > even directly after the > stats have been updated. My question is should be do > an update stats on the > combo index or should the individual columns be > sufficient? > > Thanks > > Lennie <SNIP>

Thanks Art. I can use dostats now that we finally got off of Windows and onto Linux. Lennie > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of ART > KAGEL, .... > Sent: Tuesday, January 24, 2006 11:11 AM > To: ids@iiug.org > Subject: Re: Update Stats on Combo Index [6260] > > > > Almost, see my comments below > ----- Original Message ----- > From: Anup Agrawal <ids@iiug.org> > At: 1/24 11:59 > > > Lennie, > > > > Here is an example for doing the update stats. > > > > Index_1 (A,B,C) > > Index_2 (A,B,D) > > Index_3 (E) > > > <older servers yes, for newer servers skip this> update stat > medium on table > (note MEDIUM implies distributions and the DISTRIBUTIONS ONLY > clause is > neither needed for speed nor desirable) > <older servers yes>Update Stat High (A) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (E) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (C) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (D) DISTRIBUTIONS ONLY > <newer servers only substitute for the high's above> > > Update stats high (A,E,C,D) DISTRIBUTIONS ONLY > <newer servers only substitute for the medium above> > > Update stat Medium (B) > <yes>Update Stat Low (A,B,C) > <yes>Update Stat Low (A,B,D) > > Art S. Kagel > > Hope this will help. > > > > Thanks > > Anup > > --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > > > > I would like to know about how Update statistics > > works in regards to combo > > indexes. Specifically, we update stats medium on > > each column that is > > included in an index and the primary key index as > > high. We seem to > > sometimes have slowness issues with combo indexes > > even directly after the > > stats have been updated. My question is should be do > > an update stats on the > > combo index or should the individual columns be > > sufficient? > > > > Thanks > > > > Lennie > <SNIP> > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Congratulations! That's good to hear. Art ----- Original Message ----- From: Li.... Jarratt <ids@iiug.org> At: 1/24 12:12 Thanks Art. I can use dostats now that we finally got off of Windows and onto Linux. Lennie > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of ART > KAGEL, .... > Sent: Tuesday, January 24, 2006 11:11 AM > To: ids@iiug.org > Subject: Re: Update Stats on Combo Index [6260] > > > > Almost, see my comments below > ----- Original Message ----- > From: Anup Agrawal <ids@iiug.org> > At: 1/24 11:59 > > > Lennie, > > > > Here is an example for doing the update stats. > > > > Index_1 (A,B,C) > > Index_2 (A,B,D) > > Index_3 (E) > > > <older servers yes, for newer servers skip this> update stat > medium on table > (note MEDIUM implies distributions and the DISTRIBUTIONS ONLY > clause is > neither needed for speed nor desirable) > <older servers yes>Update Stat High (A) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (E) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (C) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (D) DISTRIBUTIONS ONLY > <newer servers only substitute for the high's above> > > Update stats high (A,E,C,D) DISTRIBUTIONS ONLY > <newer servers only substitute for the medium above> > > Update stat Medium (B) > <yes>Update Stat Low (A,B,C) > <yes>Update Stat Low (A,B,D) > > Art S. Kagel > > Hope this will help. > > > > Thanks > > Anup > > --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > > > > I would like to know about how Update statistics > > works in regards to combo > > indexes. Specifically, we update stats medium on > > each column that is > > included in an index and the primary key index as > > high. We seem to > > sometimes have slowness issues with combo indexes > > even directly after the > > stats have been updated. My question is should be do > > an update stats on the > > combo index or should the individual columns be > > sufficient? > > > > Thanks > > > > Lennie > <SNIP> > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

And as Art pointed out if you're running 7.31xD2+, 9.30xC3+, 9.4 or 10.0 you can run update statistics in the following way for the following table as a starting point. table uptable with columns A B C D E F G index_1 (A, B, C) index_2 (A, B, D) index_3 (E) update statistics low for table uptable (A, B, C, D, E); update statistics medium for table uptable (B, F, G) distributions only; update statistics high for table uptable (A, C, D, E) distributionsonly; You may want to move some of the columns from the medium run to the high run if a column is frequently used to join two tables, it may help and it may not but this is a good starting point. You can run these statements in any order and can be run in parallel. Setting PDQ priority and PSORT_NPROCS will give you more memory and more parallelism which will decrease the run time for update statistics. Andrew Ford -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Anup Agrawal Sent: Tuesday, January 24, 2006 12:00 PM To: ids@iiug.org Subject: Re: Update Stats on Combo Index [6259] Lennie, Here is an example for doing the update stats. Index_1 (A,B,C) Index_2 (A,B,D) Index_3 (E) update stat medimum on table with distributions. Update Stat High (A) Update Stat High (E) Update Stat High (C) Update Stat High (D) Update stat Medium (B) Update Stat Low (A,B,C) Update Stat Low (A,B,D) Hope this will help. Thanks Anup --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > I would like to know about how Update statistics > works in regards to combo > indexes. Specifically, we update stats medium on > each column that is > included in an index and the primary key index as > high. We seem to > sometimes have slowness issues with combo indexes > even directly after the > stats have been updated. My question is should be do > an update stats on the > combo index or should the individual columns be > sufficient? > > Thanks > > Lennie > > Informix DBA > Information Technology Tax team > Lake County, IL > O: 847-377-2092 > C: 847-309-7718 > > Math illiteracy affects 7 out of every 5 people. > > > ************************************************************************ ******* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Ford, Andrew G said: > > And as Art pointed out if you're running 7.31xD2+, 9.30xC3+, 9.4 or 10.0 > you can run update statistics in the following way for the following > table as a starting point. > > table uptable with columns > > A > > B > > C > > D > > E > > F > > G > > index_1 (A, B, C) > index_2 (A, B, D) > index_3 (E) > > update statistics low for table uptable (A, B, C, D, E); > update statistics medium for table uptable (B, F, G) distributions only; > update statistics high for table uptable (A, C, D, E) distributions> only; > > You may want to move some of the columns from the medium run to the high > run if a column is frequently used to join two tables, it may help and > it may not but this is a good starting point. > > You can run these statements in any order and can be run in parallel. > > Setting PDQ priority and PSORT_NPROCS will give you more memory and more > parallelism which will decrease the run time for update statistics. And DBUPSPACE. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com

Slightly off topic but, How good/useful/accurate is the "Generate an Update Statistics Script..." option on ISA -----Original Message----- From: ART KAGEL, .... [mailto:kagel@bloomberg.net] Sent: 24 January 2006 17:15 To: ids@iiug.org Subject: RE: Update Stats on Combo Index [6262] Congratulations! That's good to hear. Art ----- Original Message ----- From: Li.... Jarratt <ids@iiug.org> At: 1/24 12:12 Thanks Art. I can use dostats now that we finally got off of Windows and onto Linux. Lennie > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of ART > KAGEL, .... > Sent: Tuesday, January 24, 2006 11:11 AM > To: ids@iiug.org > Subject: Re: Update Stats on Combo Index [6260] > > > > Almost, see my comments below > ----- Original Message ----- > From: Anup Agrawal <ids@iiug.org> > At: 1/24 11:59 > > > Lennie, > > > > Here is an example for doing the update stats. > > > > Index_1 (A,B,C) > > Index_2 (A,B,D) > > Index_3 (E) > > > <older servers yes, for newer servers skip this> update stat > medium on table > (note MEDIUM implies distributions and the DISTRIBUTIONS ONLY > clause is > neither needed for speed nor desirable) > <older servers yes>Update Stat High (A) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (E) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (C) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (D) DISTRIBUTIONS ONLY > <newer servers only substitute for the high's above> > > Update stats high (A,E,C,D) DISTRIBUTIONS ONLY > <newer servers only substitute for the medium above> > > Update stat Medium (B) > <yes>Update Stat Low (A,B,C) > <yes>Update Stat Low (A,B,D) > > Art S. Kagel > > Hope this will help. > > > > Thanks > > Anup > > --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > > > > I would like to know about how Update statistics > > works in regards to combo > > indexes. Specifically, we update stats medium on > > each column that is > > included in an index and the primary key index as > > high. We seem to > > sometimes have slowness issues with combo indexes > > even directly after the > > stats have been updated. My question is should be do > > an update stats on the > > combo index or should the individual columns be > > sufficient? > > > > Thanks > > > > Lennie > <SNIP> > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System before entering the GFM Network. If you have any queries please contact Systems Administration or IT. ________________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Not as good as using dostats with its '-f stats_script.sql' option since the many options dostats provides beyond basic stat script generation are not available. I also do not think ISA adjusts its output for optimized versus unoptimized server versions. ;-( Art S. Kagel ----- Original Message ----- From: Noel Murphy <ids@iiug.org> At: 1/25 7:18 Slightly off topic but, How good/useful/accurate is the "Generate an Update Statistics Script..." option on ISA -----Original Message----- From: ART KAGEL, .... [mailto:kagel@bloomberg.net] Sent: 24 January 2006 17:15 To: ids@iiug.org Subject: RE: Update Stats on Combo Index [6262] Congratulations! That's good to hear. Art ----- Original Message ----- From: Li.... Jarratt <ids@iiug.org> At: 1/24 12:12 Thanks Art. I can use dostats now that we finally got off of Windows and onto Linux. Lennie > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On > Behalf Of ART > KAGEL, .... > Sent: Tuesday, January 24, 2006 11:11 AM > To: ids@iiug.org > Subject: Re: Update Stats on Combo Index [6260] > > > > Almost, see my comments below > ----- Original Message ----- > From: Anup Agrawal <ids@iiug.org> > At: 1/24 11:59 > > > Lennie, > > > > Here is an example for doing the update stats. > > > > Index_1 (A,B,C) > > Index_2 (A,B,D) > > Index_3 (E) > > > <older servers yes, for newer servers skip this> update stat > medium on table > (note MEDIUM implies distributions and the DISTRIBUTIONS ONLY > clause is > neither needed for speed nor desirable) > <older servers yes>Update Stat High (A) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (E) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (C) DISTRIBUTIONS ONLY > <older servers yes>Update Stat High (D) DISTRIBUTIONS ONLY > <newer servers only substitute for the high's above> > > Update stats high (A,E,C,D) DISTRIBUTIONS ONLY > <newer servers only substitute for the medium above> > > Update stat Medium (B) > <yes>Update Stat Low (A,B,C) > <yes>Update Stat Low (A,B,D) > > Art S. Kagel > > Hope this will help. > > > > Thanks > > Anup > > --- "Jarratt, Li...." <LJarratt@co.lake.il.us> wrote: > > > > > I would like to know about how Update statistics > > works in regards to combo > > indexes. Specifically, we update stats medium on > > each column that is > > included in an index and the primary key index as > > high. We seem to > > sometimes have slowness issues with combo indexes > > even directly after the > > stats have been updated. My question is should be do > > an update stats on the > > combo index or should the individual columns be > > sufficient? > > > > Thanks > > > > Lennie > <SNIP> > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System before entering the GFM Network. If you have any queries please contact Systems Administration or IT. ________________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.