Tuning question

I have a large table of about 9 million rows, and I tried a query like the following:

select a.mdb_raw_log, b.mdb_raw_log from   mdb_raw a, mdb_raw b, mdbrawsearch c
where

	c.mdb_raw_id = b.mdb_raw_id and 
	c.mdb_mstr_id = a.mdb_mstr_id and
	a.mdb_raw_file_num='1' and

mdbrawsearch is defined as (mdb_raw_id, mdb_mstr_id), and is a relatively small table (of about 40000 rows).

mdb_raw_id is the primary key of the table mdb_raw and there is a composite index on (mdb_mstr_id, mdb_raw_file_num).

The explan plan output is as follow:

OPERATION                      OPTIONS
------------------------------ ------------------------------
OBJECT_NAME                            ID  PARENT_ID
------------------------------ ---------- ----------
SELECT STATEMENT
                                        0
MERGE JOIN
                                        1          0
SORT                           JOIN
                                        2          1
NESTED LOOPS
                                        3          2
TABLE ACCESS                   FULL
MDBRAWSEARCH                            4          3

TABLE ACCESS                   BY INDEX ROWID
MDB_RAW                                 5          3

INDEX                          RANGE SCAN
I_MDB_RAW_ID_FN                         6          5

SORT                           JOIN
                                        7          1

TABLE ACCESS                   FULL
MDB_RAW                                 8          7

I wonder why the merge join of the mdb_raw table is not using the composite index but resort to a full table scan?

Any help is welcome!

Fred Received on Mon Jun 03 2002 - 20:46:50 CDT