Re: Performance and table order

First off, the most limiting condition on your query is not against an indexed column, and an b-tree index will not help because of the cardinality. Well, I'm not going to go into that, just forget about the indexing issue. Remember, Oracle evaluates the query from bottom to top; so, what you told Oracle to do with the first query is get all the rows from T2 that are = to T1 (which is all 3000 rows) and then find all the rows from T1 that are = to 'P' (primary keys, normally about 5-10% of the rows in this table). So you are telling Oracle to retrieve 3000*650 = 1950000 rows in a very inefficient way (full table scans of both tables). The second query is telling Oracle to first get only 10% ( just the 'P's) of the smaller table T1 = 65 rows and then match them to the bigger table T2 by part of the primary key and a much smaller data-set. Estimates only, but (650*0.10) * (3000*0.20) ß (and part PK) = 39000 rows in a more efficient manner.

Sorry for the spelling and grammar, I am in a hurry. If you want a more detailed explanation, just write.

Frank Brammer
Practical Software Solutions

Werner Fangmeier <werner.fangmeier_at_lgm.lion.de> wrote in article <33BA4C2D.33CB_at_lgm.lion.de>...
> 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) ?
>
> TIA, Werner
>
  Received on Thu Jul 03 1997 - 00:00:00 CDT