Re: Performance Question on Joins

In article <42vjhl$t16_at_toplink1.toplink.de>, Stefan Fallet <stfallet_at_mail.Herrenberg.netsurf.de> writes:
> Does anyone know wich SQL statement is better performing?
>

...snip...

Use EXPLAIN PLAN or TKPROF and find out exactly what is happening.

>
> My two Selects are:
> 1.) SELECT .... FROM A, B
> where A.key_1=B.key1 AND
> A.key_2=B.key_2 AND
> B.key_3='XXXXX';

I would use this script.
This is faster because

>
> 2.) SELECT .... FROM A
> WHERE (key_1,key_2) in (SELECT key_1,key_2 from B
> where key_3='XXXXX');

this one does not use its index on the subquery because you don't use the leading keys of the index in the where clause.

The correct usage is:

SELECT .... FROM A  WHERE (key_1,key_2) in (SELECT key_1,key_2 from B

                          where B.key_1 = A.key_1 AND
                                B.key_2 = A.key_2 AND
                                B.key_3='XXXXX');

This is also probably slower than item #1 but you would need to run it and check how much slower.

Another solution is:

SELECT .... FROM A  WHERE EXISTS (SELECT 'x' from B

                where B.key_1 = A.key_1 AND
                      B.key_2 = A.key_2 AND
                      B.key_3='XXXXX');
 

This one may be faster than the corrected #2 above.

Try them and pick the best from the EXPLAIN PLAN.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************
"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras