SQL-tuning

Hello All!

I cannot understand the following behavior of ORACLE-optimizer

SELECT a FROM tab1 WHERE (a,b,c,d,e,f,g,h) IN  (SELECT a,b,c,d,e,f,g,h FROM tab2);

In tab1 - 300000 records, in tab2 - 0
both tables have primary key (a,b,c,d,e,f,g,h)

Result:

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'TAB1'
   3    1     INDEX (UNIQUE SCAN) OF 'SYS_C00439582' (UNIQUE)


----------------------------------------------------------

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

'SYS_C00439582' - primary key in tab2

If I delete any field from a,b,c,d,e,f,g,h, for example:

SELECT a FROM tab1 WHERE (a,b,c,d,e,f,g) IN (SELECT a,b,c,d,e,f,g FROM tab2);

Result is absolutely differently:

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 NESTED LOOPS

   2    1     VIEW
   3    2       SORT (UNIQUE)
   4    3         TABLE ACCESS (FULL) OF 'īTAB2'
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
   6    5       INDEX (RANGE SCAN) OF 'I9_TAB1' (NON-UNIQUE)


----------------------------------------------------------

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

Here index I9_TAB1 - any index in tab1 (c, d, e, f). I could understand that in the first case optimazer wants to take index from tab2,
because all fields are explicitly listed. I do not understand however, why it leads to full scan on tab1.

Thank you in advance
Andrei Received on Wed Jul 31 2002 - 11:51:48 CDT