Re: Performance and table order

Werner Fangmeier wrote:
>
> Hi All,
>
> My Environment: Oracle 7.3, SQL*Net/Plus 2.3 on Windows NT 4.0.
>
> I'm just getting into the Oracle mysteries and found an
> interesting and irritating issue:
>
> Consider the following query:
>
> select t1.constraint_name,
> t1.table_name,
> t2.column_name as pk_column,
> t1.status as pk_status
> from user_constraints t1,user_cons_columns t2
> where t1.constraint_name=t2.constraint_name
> and t1.constraint_type = 'P';
>
> In my user_constraints table there are about 650 records, in
> user_cons_columns about 3000 records.
>
> After entering this select in SQL*Plus, I had to cancel the
> select after 2 hours! SQL*Plus at this time already showed
> 284 result records, but still was not finished.
>
> I played around with this query, and at last came to changing
> table order: instead of the 'From' clause above I used
>
> from user_cons_columns t2,user_constraints t1
>
> This resultet in a DRAMATIC performance increase; the result came
> in SECONDS! To repeat it:
>
> The same query needed HOURS with table order t1,t2; it needed only
> SECONDS with order t2,t1.
>
> Can anyone explain this to me ? How can I determine the optimal
> table order in a query (if this is possible) ?

Welcome to the wonderful world of tuning Oracle SQL queries!

I have found the best performance by following these tips:

1. The where clause should be designed from the bottom up - placing the most limiting condition as the last condition - preferably based on a fairly unique indexed column.
2. If you are able to build the query using tip (1), then be careful not to use another indexed column as the next condition up from the bottom because Oracle will first merge the two indexes in memory - this will slow down the query big time. Instead, disable the second column's index by adding a zero to it (in the case of a numeric or date column) or adding an empty string to a char column.

   EXAMPLE: if both the orderDT and lName columns are indexed, disable

             one of them:

             ...
             WHERE
               lName+'' > 'R'
               AND orderDT between '01-MAY-96' AND '05-MAY-96';

3) Join multiple tables using an indexed column in the subordinate

   table.

   EXAMPLE: if table t1 is indexed on custID and table t2 is indexed

             on orderDT, join thusly:

             ...
             FROM 
               t1, 
               t2
             WHERE
               t1.custID = t2.custID
               AND t2.orderDT BETWEEN '01-MAY-96' AND '05-MAY-96';

4) Order the tables in the FROM clause in the same order as your

   tables are used in your WHERE clause, i.e. from the bottom up.

There are MANY other tips for tuning SQL SELECTs, but these are good for starters. For a good overview, read the O'Reily book "Oracle Performance Tuning."

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||