Re: Performance Question on Joins

Stefan Fallet <stfallet_at_mail.Herrenberg.netsurf.de> wrote:
>Does anyone know wich SQL statement is better performing?
 

>I have two tables A and B.
 

>create table A
>(
> key_1 varchar2(12),
> key_2 varchar2(10),

     .
     .
     .

   key_1     varchar2(12),
   key_2     varchar2(10),
   key_3     varchar2(9),
     .
     .
     .

>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';
>
>2.) SELECT .... FROM A WHERE (key_1,key_2) in
> (SELECT key_1,key_2 from B where key_3='XXXXX');
>
>Table A has about 10000 rows and table B has about 10 rows for each row
>in table A. The data in table B would permit key_3 to be unique by itself
>at the moment. We are running ORACLE 7 on Novell NetWare.
 

>Thanks
> Stefan

From a straight analysis (with out testing) Solution 1 should be faster. Oracle will attempt to directly use the indexes (index merg= e) -- which is very fast. With the second statement Oracle will likely convert the execution to be very similar to the first! There = may be minimal time difference.

These two statements do not provide the same functionality. From the first statement I would assume that you need/use columns from b= oth tables in the select phrase. With the second statement only the first table's colums are available. Given this "new" requirement=  you might try this approach:

3.) SELECT ....

      FROM A 
     WHERE exists (select 1
                     from B
                    where A.key_1=B.key1 AND 
                          A.key_2=B.key_2 AND
                          key_3='XXXXX');

In my experience, after individually testing similar contructs, Option 3 is faster.

Take Care!

Lawrence V. Rohrer
1500 Adams #105
Costa Mesa, California 92626
lrohrer_at_earthlink.net
http://www.earthlink.net/~lrohrer/index.html Received on Mon Sep 11 1995 - 00:00:00 CEST