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

Stefan,
Depends whether you use rule-based optimization or cost-based optimization. In both cases the subquery is probably transformed to a join. If it isn't 2 is certainly slower than 1, as a subquery is performed once for each row returned by the main query. If you use rule based optimization, statement 1 is correct, as it has the smallest table (B) as the driving table (rule-based reads from right to left). If you use cost-based optimization, you have to change the sequence of tables in your from clause in 'from B, A', as cost-based reads from left to right.
You might want to check out what actually happens. There are several methods to do this. You can use the statement 'EXPLAIN PLAN' to produce an analysis of the statement. How to enable it is described in Oracle concepts ch. 13. Alternatively you can enable tracing in your sqlplus session (alter session set sql_trace = true), leave sql*plus, find your trace-file in your ORA_HOME\rdbms71\trace directory, and use tkprof to make a report on this. This will give you actual I/O consumption. The basic syntax for tkprof is:
tkprof <trace-filename> explain=<username/password>

Hope this helps

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe

All standard disclaimers apply! Received on Mon Sep 11 1995 - 00:00:00 CEST