We have been using the cost based optimizer since it came out (7.0.15,
7.1.3, 7.1.6) and we have found it works pretty well on both UNIX and VMS
systems if you are willing to follow a few rules:
- You will have to tune your application to use hints in about 20% of
your sql
- Update your statistics for tables that are growing on a regular basis
- Generate the statistics for tables subject to wide row count changes
when
a normal number of rows are in the table and do not regenerate them
unless
you have too (you did maintenance of the table)
- Take advantage of the sample parameter on the analyze table statement
and
use a number that will result in fully computing all tables under
40M and sample a fair percentage of larger tables on version
7.1.6 and higher. The
sample syntax works in 7.1.3, but only does the minimum estimate.
- On version 7.1.3 and lower, where the estimate results in a bad access
plan
and the table is too large to compute be prepared to have to do some
table
reorgs.
Mark Powell -- The only advise that counts is the advise that you follow
so follow your own advise
Received on Sat Apr 05 1997 - 00:00:00 CST
