Re: Tuning SQL statements

mary_at_mitra.com (Mary E. Hunt) wrote:

>Can someone please point me to a good reference that has guidelines to
>write SQL statements. I know for instance that you order the tables
>from biggest to smallest in the FROM part of the statement.
 

>Does it make a difference the order of the conditions in the where
>clause? What should go first etc... If someone could either post
>something or tell me where to find info about this I would appreciate
>it.
 

>I would like to give my programmers these guidelines for when they
>write SQL statements in their applications.

I don't know about ordering the tables, but around here the method of choice is to concatenate an empty string to the columns that we DON'T want an indexed to be used on. These seems to work in either cost based or rule based optimization. For example, if I'm writing a multitable join that uses 'value' as it's only known constant, the following query would probably give the optimal path;

SELECT *
  FROM tablea, tableb, tablec

 WHERE tablea.col1 = 'value'
   AND tablea.foreign_key||'' = tableb.primary_key
   AND tableb.foreign_key||'' = tablec.primary_key

Doing queries this way seems to keep the optimizer from getting "confused" about which indexes to use.

Hope this is worth something.

John Fruetel
jfruetel_at_ainet.com Received on Sun Jun 25 1995 - 00:00:00 CEST