Performance and table order

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 Wed Jul 02 1997 - 00:00:00 CDT