Re: Query Performance question

"Pedio" <pedio_at_iname.com> wrote in message news:8i8dtm$4pb2_at_imsp212.netvigator.com...
> Hi,
>
> I have question on the SQL query performance issue. I really need to know
> which statement would perform better. I know they might have different on
> bulky database, yet, I don't have enough dataset to perform testing as the
> system just in development stage. Would anyone please tell me which of
 the
> following statement would perform faster and how the searching critea
 runs?
>
> 1.
> select * from table_A where Key1 in (select Key1 from table_B)
> and key2 in (select Key2 from table_C)
>
> 2.
> select * from table_A a, table_B b, table_C c where
> a.Key1 = b.Key1 and a.Key2 = c.Key2
>
> Thanks in advance!
>
> Davy
>
>
>
>

Here you are...
Took about 15 minutes! Easy, not?!

Assuming in table_a key1 and key2 are unique and the primary key, key1 is primay key in table_b, key2 is primary key is table_c

Running Personal Oracle 8.1.5, rule based optimizer, resultst created with
set autotrace on explain stat
SQL> /      KEY1 KEY2
--------- ---------

     2138 1369

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'TABLE_A'
   4    2       INDEX (UNIQUE SCAN) OF 'SYS_C00824' (UNIQUE)
   5    1     INDEX (UNIQUE SCAN) OF 'SYS_C00825' (UNIQUE)

Statistics

          0  recursive calls
          4  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1138  bytes sent via SQL*Net to client
        737  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from table_A a, table_B b, table_C c where   2 a.Key1 = b.Key1 and a.Key2 = c.Key2   3 /

     KEY1 KEY2 KEY1 KEY2
--------- --------- --------- ---------

     2138 1369 2138 1369

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'TABLE_B'
   4    2       INDEX (RANGE SCAN) OF 'SYS_C00823' (UNIQUE)
   5    1     INDEX (UNIQUE SCAN) OF 'SYS_C00825' (UNIQUE)

Statistics

          0  recursive calls
          4  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1253  bytes sent via SQL*Net to client
        723  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> spool off

Next time, if you want to get responses, please provide more information.

Regards,

Sybrand Bakker, Oracle DBA Received on Wed Jun 14 2000 - 00:00:00 CDT