Re: Tuning SQL statements

In article <3shdfp$3e0_at_news.cais.com>, <randyd_at_cais3.cais.com> writes:
> Path: aimnet.com!news.sprintlink.net!news.cais.com!cais3.cais.com!randyd
> From: randyd_at_cais3.cais.com (Randy Dewoolfson)
> Newsgroups: comp.databases.oracle
> Subject: Re: Tuning SQL statements
> Date: 24 Jun 1995 16:11:37 GMT
> Organization: Capital Area Internet Service info_at_cais.com 703-448-4470
> Lines: 50
> Message-ID: <3shdfp$3e0_at_news.cais.com>
> References: <MARY.95Jun22085844_at_shark>
> NNTP-Posting-Host: cais3.cais.com
> X-Newsreader: TIN [version 1.2 PL2]
>
> Mary E. Hunt (mary_at_mitra.com) 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.
 

> : Thanks in advance.
 

> : Mary Hunt
>
> Its hard to generalize how to optimize all queries but heres some
> things to look for.
>
> The Query parser operates in reverse order, that is, the last items
> are executed first. So a good idea is to place the most restrictive
> elements of your where cluse at the bottom.
> If you have several where conditions, then the one that will
> 'weed out' the most records should go last, etc.
>
> I haven't found that hte order of the tables makes much difference,
> for this you should check the EXPLAIN PLAN table for order.
> If you get the same table being scanned multiple times, then
> you should juggle the orders, or more probably, restructure the whole
> statement, and see if this makes a difference.
>
> The other thing to do with EXPLAIN PLAN is to check that the tables
> in your query are being accessed efficiently... That is try to
> eliminate FULL TABLE SCANs and get everything down to ROW ACCESS
> or INDEXED access. This can be tricky but worth it.
>
> Hope it helps...
> Randy
>
> --
> ..uu. ----------------------
> .?$" '?i . I Randy DeWoolfson I
> .T^M ._at_" d9 . f ,.un. b, i I--------------------I
> " Z :#" M `8 U < .dP"``"# `M _at_" I randyd_at_cais.com I
> &H?` Xl _R $5. $ ?* _at_ 'P,#" I--------------------I
> ,d#^*L :RP'~$b f`$L:M Xf .f' dH` I ,\//. I
> & 'M ,P `E M "$ Mux~ n!` I |o o| I
> dk `h" ' j " y" *~ I====oOO==(_)==Ooo===I
>
>

Two very good sources that I have used are:

Tuning Oracle from the Oracle Press
Oracle Performance Tuning by O'Reilly Received on Wed Aug 30 1995 - 00:00:00 CEST