RE: update statistics

Greg Answers to questions: 1. If the data is static then no. If the data changes - number of rows, distribution pattern of a column that is included in an index - yes. 2. Yes. 3. This is purely so the optimiser can chose the correct path to perform the sql. 4. Why does sysdistrib have more data in it for table B? Under your scenario described it wont. The data from the last update stats is held. MW -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of gresmi@yahoo.com Sent: Wednesday, September 20, 2000 11:19 AM To: informix-list@iiug.org Subject: 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.