update statistics

hi, for the leading column, i want to do the update statistics high, I have two method to run it. method one: update statistics high for <table name>(col1,col2); method two update statistics high for <table name>(col1); update statistics high for <table name>(col2); Do method one is quickly than method two? because only one time table scan.method two need two time table scan. thanks.

In general, yes, method #1 will be faster. On some older server versions, particularly servers prior to 7.31xD2 & 9.30xC3, this was not true because before then the server scanned for each column separately anyway. Modern servers do a single scan for multiple columns and resort the same data once for each column. Dostats uses your method #1. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 20, 2016 at 7:29 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > hi, > > for the leading column, i want to do the update statistics high, I have two > method to run it. > > method one: > > update statistics high for <table name>(col1,col2);> > method two > > update statistics high for <table name>(col1);> > update statistics high for <table name>(col2);> > Do method one is quickly than method two? because only one time table > scan.method two need two time table scan. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f9a8c45a8730529cd69e5

On the other hand, you can always install Art's utilities and use dostats. http://www.iiug.org/software/index_DBA.html Look for utils2_ak --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Wednesday, January 20, 2016 18:45 PM > To: ids@iiug.org > Subject: Re: update statistics [36425] > > In general, yes, method #1 will be faster. On some older server > versions, particularly servers prior to 7.31xD2 & 9.30xC3, this was not > true because before then the server scanned for each column separately > anyway. Modern servers do a single scan for multiple columns and resort > the same data once for each column. Dostats uses your method #1. > > Art > > Art S. Kagel, President and Principal Consultant ASK Database > Management www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on the IIUG, nor any other organization > with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Jan 20, 2016 at 7:29 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > > > hi, > > > > for the leading column, i want to do the update statistics high, I > > have two method to run it. > > > > method one: > > > > update statistics high for <table name>(col1,col2);> > > > method two > > > > update statistics high for <table name>(col1);> > > > update statistics high for <table name>(col2);> > > > Do method one is quickly than method two? because only one time table > > scan.method two need two time table scan. > > > > thanks. > > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a113f9a8c45a8730529cd69e5 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Actually guys, the latest release of utils2_ak is on my own web site, I haven't had a chance to upload the last couple of releases to the IIUG site. Go to: http://www.askdbmgt.com/my-utilities and click on the download button there. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 21, 2016 at 9:35 AM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > On the other hand, you can always install Art's utilities and use dostats. > > http://www.iiug.org/software/index_DBA.html > > Look for utils2_ak > > --EEM > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Art Kagel > > Sent: Wednesday, January 20, 2016 18:45 PM > > To: ids@iiug.org > > Subject: Re: update statistics [36425] > > > > In general, yes, method #1 will be faster. On some older server > > versions, particularly servers prior to 7.31xD2 & 9.30xC3, this was not > > true because before then the server scanned for each column separately > > anyway. Modern servers do a single scan for multiple columns and resort > > the same data once for each column. Dostats uses your method #1. > > > > Art > > > > Art S. Kagel, President and Principal Consultant ASK Database > > Management www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on the IIUG, nor any other organization > > with which I am associated either explicitly, implicitly, or by > > inference. Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > > > On Wed, Jan 20, 2016 at 7:29 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > > > > > hi, > > > > > > for the leading column, i want to do the update statistics high, I > > > have two method to run it. > > > > > > method one: > > > > > > update statistics high for <table name>(col1,col2);> > > > > > method two > > > > > > update statistics high for <table name>(col1);> > > > > > update statistics high for <table name>(col2);> > > > > > Do method one is quickly than method two? because only one time table > > > scan.method two need two time table scan. > > > > > > thanks. > > > > > > > > > > > > > > *********************************************************************** > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a113f9a8c45a8730529cd69e5 > > > > > > *********************************************************************** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1132f2145cb24a0529da3077

A very big factor here too is the use of PDQPRIORITY and the MGM settings (DS_TOTAL_MEMORY for example). If you combine multiple columns into one statement, the engine will make as many passes as needed as a function of sort memory ... ie: memory available when using PDQPRIORITY and taken from the DS_TOTAL_MEMORY allocation. If there is enough sort memory available, the number of passes can be reduced to as low as 1. If there is not sufficient sort memory available, it will still make as many passes as needed, up to the number of columns in the list. As Art mentioned, older versions of IDS would do 1 pass per column anyway. I've spent a lot of time with clients cleaning up old upd stats code to take advantage of this ... one client had a 1B+ row table, 12 columns indexed, and thus had 12 "upd stats high" statements - 1 for each column. And yes, it took years (well ok - a long time). Combining them into 1 statement, increasing DS_TOTAL_MEMORY and SHMVIRTSIZE (where the slice of DS_TOTAL_MEMORY comes from when needed), turned on PDQ and I could get it to run in hours. SET EXPLAIN will tell the whole story if you set it for the upd stats sqls. There are other factors to consider here and they have been well documented by us old-timers and IBM'ers in white papers, etc. But the PDQ usage/tuning part is still one of the "great mysteries" when I visit clients and and before long we're at a whiteboard. And yes ... many of my clients use dostats and have for years. Thanks- Mark Scranton The Mark Scranton Group mark@markscranton.com