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),
> .
> .
> .
>);
>create unique index iA on A (key_1,key2);
>create table B
>(
> key_1 varchar2(12),
> key_2 varchar2(10),
> key_3 varchar2(9),
> .
> .
> .
>);
>create unique index iB on B (key_1,key_2,key_3);
 

>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.

Your index on B should have the most specific column on the leading edge. i.e., "create unique index iB on B (key_3,key_1,key_2)". Received on Wed Sep 13 1995 - 00:00:00 CEST