Re: RULE vs CHOOSE

> > If the database tables are never analysed is there any point in
having the OPTIMISER set to CHOOSE (the default) and in fact would it slow things down? <<

Setting the database to CHOOSE (default) when your system does not normally have statistics on its tables gives you the option to use both cost and rule based queries at the statement level.

There are many times where the cost based optimizer results in better execution plans than rule based optimization. We have a vendor package that the vendor said was written to run rule based. After dropping all the statistics the system ran 15 minutes before the calls came in complaining about performance. Ten minutes later the stats were back and the performance gains were noticed immediately. If you use the cost based optimizer you will need to manually hint about 10% of your sql to make it work. You will need to watch temporary holding tables whose row count vary greatly from day to day with the cost based optimizer, but it is not that hard to get a handle on the tables that 1) should not be re-analyzed on a routine basis and 2) those that need regular analyzing.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Wed Aug 13 1997 - 00:00:00 CEST