Re: How to use index

Actually the whole sql is a join with 2 tables:

select

	a.INDEXED_COLUMN1,
	b.INDEXED_COLUMN2,
	b.COLUMN2
	b.COLUMN3
	b.COLUMN4

from
 tableA a, tableB b         

where

	a.INDEXED_COLUMN1 IN ('A','B','C','D') and
	a.INDEXED_COLUMN2=b.INDEXED_COLUMN1 and
        b.INDEXED_COLUMN2 > SYSDATE

TableA has a composite index for column2 and column1 (a.INDEXED_COLUMN2, a.INDEXED_COLUMN1). TableB has a composite index for column1 and column2 (b.INDEXED_COLUMN1, b.INDEXED_COLUMN2)

The optimizer does not use the index in tableB when the 2 tables joined together. Instead, it does a full scan. The trace is listed below:

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4 Bytes=124)    1 0 HASH JOIN (Cost=5 Card=4 Bytes=124)

   2    1     INDEX (FAST FULL SCAN) OF 'IX_TABLE_A_COL2_COL1' (NON
          -UNIQUE) (Cost=1 Card=4 Bytes=24)

   3    1     TABLE ACCESS (FULL) OF 'TABLE_B' (Cost=3 Card=16 Bytes=4
          00)

Statistics

          0  recursive calls
          8  db block gets
        111  consistent gets
          0  physical reads
          0  redo size
        854  bytes sent via SQL*Net to client
       1039  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          4  rows processed

Thanks Received on Fri Nov 07 2003 - 08:00:53 CET