Re: SQL Optimisation , FROM Clause table order, Where clause etc

In article <6q6c62$kf4$1_at_news.mel.aone.net.au>,   "Rodger Talevski" <Talevski.Rodger_at_cabs.com.au> wrote:
> Hi All
>
> I'm trying to find out if the order of the tables listed in the from clause
> will affect performance when querying from more than one table. Also should
> the Where clause start with expressions from larger tables etc.
>
> EG TABLE A 10000 Rows TABLE B 5000 rows TABLE C 100 rows
>
> SELECT A.COL1, B.COL etc etc
> FROM A, B,C
> WHERE A.COL1 = B.COL1 AND
> A.COL2 = B.COL2 AND
> B.COL4 = C.COL3
>
> I want to know if
> 1. the FROM clause table order will affect performance and
> 2. the where clause works in a similar fashion
>
>

Hi Rodger,

if your setup is using the rule-based optimizer, Oracle will scan and sort the table specified last in the from clause using any available indices. Then it will merge the records found with the ones found for the last but one and so forth. SO in order to optimize performance, make sure that the table which *returns* the smallest number of records/rows is last in your from clause. This is *not* always the table with the fewest number of records.

The way you write the where clause can also influence performance.

Consider two table : one for people with 99 records, one for jobs with 9900 records.

You want to find all the people who don't have a job to do

select p.name from people p, job j where p.name = j.name(+) and j.name is null should take roughly 50 seconds

select name from people where not name in ( select name from job ) should take roughly 6 seconds

select name from people p where not name in ( select name from job j where p.name = j.name )
should take roughly 1 second

HTH --
Oliver Willandsen - European Commission http://europa.eu.int
All remarks are my own and do not necessarily reflect official European Commission policy

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Aug 04 1998 - 04:41:25 CDT