Re: How to use index
Date: 7 Nov 2003 10:56:51 -0800
Message-ID: <4b5394b2.0311071056.10e7f4ed_at_posting.google.com>
wingkei_leung_at_hotmail.com (Ken) wrote in message news:<63029411.0311062300.493ad399_at_posting.google.com>...
> 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.
Since the index on tableB is column1,column2 but your query has no value for the beginning of that index (ie the where clause has no values for tableB.column1). So the optimizer cannot compute a value or even a range to use in the index.
>... 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
Given the plan shown above, as long as tableB remains the smaller of the two tables, the hash join will be plenty fast. (You're going to need a LOT more rows than that to have any concern about performance.)
lastly, consider doing a more detailed design and make some primary keys (you get the index automatically.
HTH,
ed
Received on Fri Nov 07 2003 - 19:56:51 CET