Re: Help with composite indices

Peter Whittleton wrote: > > Irwin Goldstein wrote: Hi all, I'm wondering whether the recommendations below are really from Informix. UPDATE STATISTICS HIGH and MEDIUM generate data distributions forthe optimizer. This information must be read if they are available. If the optimizer generates the best query plan, even without this distribution, this additional information will slow down the query. Normally data distribution will be neccessary in about 5% to 10% of all your queries. Second, if a composite index is made up of two attributes, the optimizer knows the selectivity of the second attribute, even if there is no data distribution available. > > > > Check out http://www.objectsoft.com (shameless plug) for a > > script which automatically performs an appropriate sequence > > of UPDATE STATISTICS commands for specified tables. > > > > This script is great, but it doesn't reflect the latest > recommendations from Informix (for ODS 7.10.UD1 onward). > > These are (summarised): > > 1. Run UPDATE STATISTICS MEDIUM DISTRIBUTIONS ONLY for each > table or for the entire database > > 2. Run UPDATE STATISTICS HIGH DISTRIBUTIONS ONLY for: > - lead key in every index > - join columns > - columns frequently accesses via an equality filter > - the first column to distinguish a composite index from > other composite indexes on a table, and all columns > preceeding it > > 3. Run UPDATE STATISTICS LOW for the database > > Note, a lot of this is to reduce the time taken to update > the statistics, not to improve query performance. > > Peter Whittleton (whittle@ssax.com) Bye Stefan