Re: SQL Joins Optimization

On Mon, 27 Nov 2000 20:54:40 GMT, geenstones_at_my-deja.com wrote:

>Given the followin SQL statement:
>
>Select * from table1,table2
>where table1.field1(pk) = table2.field1(fk)
>and table1.field2 = 'test'
>
>How an I get oracle to only search the
>joined resultset where field2 = 'test'.
>

You don't get a "joined result set" in ORACLE. First thing ORACLE will do is scan the entire table1 looking for field2='test'. The result set of that is *then* joined to table2. That's how it works.

>
>It seems to be always querying the 1 million record resultset
>before the join.
>

Exactly. If you don't want that to happen, then put an index on field2 of table1.

What you can also do is make the optimiser work for you instead of against you. Make it go to table2 as the driving table of the join instead of table 1.

To do this there is a number of options, you may have to do more than one:

1- Make sure that a full complete ANALYZE has been performed on BOTH tables, and INCLUDING their indexes.

2- Insert a hint to make the optimiser start at table2, then use table1 subset only. This is done by adding /*+ ORDERED */ just after the select word, bounded by spaces. Then you reverse the order of the tables in the FROM clause. That's right: FROM TABLE2,TABLE1. Exactly like that!

3- You can also use a FIRST_ROWS hint. Just replace the ORDERED word in 2 above with the word FIRST_ROWS, then the order of clause FROM should be immaterial.

HTH Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue Nov 28 2000 - 03:51:43 CST