Re: 7.22.UC1X2 Performance tuning questions....

In article <5i0dkc$i35@cssun.mathcs.emory.edu>, "Ron M. Flannery" <rflanner@speedlink.net> writes > >}> 4) System not using indexes correctly >}> >}> - Have run update statistics medium for all tables, update statistics >}> high on first column of >}> each index and run update statistics low on all other index fields. >} >}I thought "official policy" was USH for first index columns, USM for >}other index columns and USL for the rest. >} > >I'm curious as to the current strategy for update stats. I also thought >that the policy was the latter (USM for other idx and USL for rest) but >reading the 7.22 Performance Guide seems to indicate the former strategy. >Does anyone know if the recommendations changed? > The real answer as to what is required depends on the data distribution of the data within the columns. Ultimatily the problem is working out the selectivity of the index (0 to 1) which determines the benefit of using the index. i.e. how many rows will be returned vs. how many rows are in the table. If a large percentage of the table will be returned then the overhead of using the index means it is best just to sequential scan the whole table. Low i.e. no distribution data should be ok if the data is uniformly distributed between the 2nd highest and 2nd lowest values as the selectivity of the index :- number of rows returned in query nunique (number of unique value) --------------------------------- = -------------------------------- number of rows in the table nrows (number of rows in table). If the data is non-uniformly distrubuted then distibutions are required. medium creates bins (ranges of values) and stores info. per 'bin' E.g. (10 bins = 10 lots of info.). This gives more info assuming you can narrow down which bin(s) the results ly in as you can eliminate data in the used bins which may affect the final selectivity value for the index. If one value occurs a lot of times then the info at the bin level is not good enough as it is affect by the fact that this one value dominates the bin and gives inaccurate results for other values in the same bin. Therefore update statistics high is needed to get even more info. about the selectivity of the index. Most of the time you have fairly uniform data distribtions E.g. serial columns, date/datetime fields which increase very often and so give good uniform distributions. Phew! Another monster post for my forthcoming Website. -- David Williams