update statistics

We are interested in getting some solid answers regarding the execution of the "update statistics" command against our Informix databases. Here is some background on our operating environment: 1. We are running Informix V731UC5 to V731UC7. 2. We are running an application that basically does not change. We are certain Informix is choosing the correct indexes within the application. 3. The structure of the tables which make up the core of our application do not change. 4. Our business and our application's core database tables have steadily grown. A table that may have once held 5 million rows may now have as many as 15 million rows. Consider this scenario: There are 2 tables, Table A and Table B that each contain exactly 15 million rows, contain exactly the same data, and were created in 1998. The information contained in systables and sysdistrib for Table A still shows Informix information from the last time update statistics was run in 1998 when Table A was initially loaded. Table B has had update statistics run on it every night since 1998. Here are our questions: 1. Are there any benefits to running update statistics on a regular basis on those core application tables, barring Informix upgrades or rebuilds of the databases? 2. Is the reason behind the recommendation to regularly run update statistics so Informix will choose the correct index path? 3. After Informix chooses the index it is going to use, does the information stored in sysdistrib play a role in retrieval time? 4. Is it possible it could take longer to run a query against Table B than against Table A because there is more data stored in sysdistrib for Table B that must be processed? Informix is already choosing the correct index path through the application. We understand the importance of running update statistics on newly added tables or those where the row count has changed dramatically over a short period of time. But, we're not completely convinced of the value of regularly running this against our core tables. We are looking for answers that reflect hard facts about real world situations as we want to develop a strategy for refining our automated running of update statistics at night. Thanks in advance, Greg Smith Database Administration DIRECTV(tm) Galaxy Latin America gdsmith@directvgla.com Sent via Deja.com http://www.deja.com/ Before you buy.

gresmi@yahoo.com wrote: > > We are interested in getting some solid answers regarding the execution > of the "update statistics" command against our Informix databases. > > Here is some background on our operating environment: > > 1. We are running Informix V731UC5 to V731UC7. > 2. We are running an application that basically does not change. We > are certain Informix is choosing the correct indexes within the > application. > 3. The structure of the tables which make up the core of our > application do not change. > 4. Our business and our application's core database tables have > steadily grown. A table that may have once held 5 million rows may now > have as many as 15 million rows. > > Consider this scenario: > > There are 2 tables, Table A and Table B that each contain exactly 15 > million rows, contain exactly the same data, and were created in 1998. > The information contained in systables and sysdistrib for Table A still > shows Informix information from the last time update statistics was run > in 1998 when Table A was initially loaded. Table B has had update > statistics run on it every night since 1998. > > Here are our questions: > > 1. Are there any benefits to running update statistics on a regular > basis on those core application tables, barring Informix upgrades or > rebuilds of the databases? It really depends on whether the relative strengths of the various key values for the columns that lead index keys have changed over time. If a transaction table, are x% of transactions still vendor Y and z% still vendor Q or is it now x+10% Y and z-5% Q? If the latter then update stats MAY help, it cannot hurt (except specific queries which would already show signs). > 2. Is the reason behind the recommendation to regularly run update > statistics so Informix will choose the correct index path? Mostly, also the correct join order for a query containing multiple tables, whether to sort or use an index to return data in order, similarly for grouping rows for a GROUP BY clause. > 3. After Informix chooses the index it is going to use, does the > information stored in sysdistrib play a role in retrieval time? No. > 4. Is it possible it could take longer to run a query against Table B > than against Table A because there is more data stored in sysdistrib for > Table B that must be processed? Only if the new stats were run at a much higher resolution than the old stats or the number of outliers had increased dramatically (which would indicate a good reason to increase the resolution next time) otherwise the amount of histogram data would be the same old and new. > Informix is already choosing the correct index path through the > application. OK > We understand the importance of running update statistics on newly added > tables or those where the row count has changed dramatically over a > short period of time. But, we're not completely convinced of the value > of regularly running this against our core tables. I'm the first to recommend running update statistics, however, I am not one for running update stats on a schedule, like daily or weekly. I run it when I see trouble, when I make a significant change, when I change the structure of a table (ie add/drop an index), when I know the nature of the keys has changed (like the database that indexes on date and we just finished the monthly load of new rows containing dates that did not exist before). > We are looking for answers that reflect hard facts about real world > situations as we want to develop a strategy for refining > our automated running of update statistics at night. Use dostats. Art S. Kagel