Re: Affect of table name order in SQL

Mike Prompovitch (prompom_at_mcln.unisysgsg.com) writes:
> We have been tring to optimize some SQL statements and discovered that
> the order of the table names in the FROM clause affected the query time.
> I thought that Oracle would determine the best way to do the query;
> freeing us from deciding how to write the SQL statement. Has anyone
> else run across this problem?

   Basically the rule based optimizer has decided that each of your tables is equally attractive (as a driving table for your query) and so has arbitrarily used the order in the FROM clause. In days of old (RDBMS 6.0) developers used their knowledge of this behaviour to tune their SQL statements.

   If you want the RDBMS to smarten up a bit you need to use the ANALYZE command on your tables and indexes so that the Oracle7 cost based optimizer can come into play.

   Now the cost based optimizer is quite a bit smarter, but keep in mind that no piece of software (i.e. optimizer) can match the knowledge of a good developer. There will be instances when the optimizer decides to do something incredibly stupid. When this happens (and it will) said good developer will place hints in their SQL to slap the optimizer about it's face a bit and tell it how the query should be done.

   Hints are documented in the Oracle7 application developers guide, along with info on the EXPLAIN and SQL TRACE facilities which help you track down problems in the first place. If you are really keen on leaning the innards of the cost and rule based optimizers, see your Oracle7 Concepts Manual.

--
   - Doug Harris
     IS Project Leader, Prices Division,
     Statistics Canada.        ## WHERE ALL_OPINIONS.OWNER = USER ##