sql using full table scan instead of an index

Hi Everyone,
I'm having trouble convincing myself that Oracle is executing a query of mine as efficiently as possible, and I'm looking for a little guidance.

The situation is pretty simple. I just have two tables, PARENT and CHILD. PARENT(
  pkey int primary key,
  value int,
  vdate date
)

CHILD(
  pkey int references PARENT(pkey),
  cdata VARCHAR2
)

In this simple example, I have indices on every column except cdata. The query is:

select CHILD.*
from CHILD, PARENT
where PARENT.value=100
and CHILD.pkey=PARENT.pkey
and PARENT.vdate between date1 and date2

An explain plan shows that this query always does a FTS on CHILD, and I can't really see why. I guess I don't really understand the optimizer well, but it would seem like a FTS isn't necessary because of the index on CHILD.pkey. In fact, if I change the * to just CHILD.pkey, the index is used as expected.

This is in Oracle 9.2.0.3. Both tables and all their indices have been analyzed, and CHILD has approximately 1M rows in it, while PARENT has about 200K. The whole thing's running on Windows 2000.

I'd appreciate any insights you may have, ry Received on Fri Oct 31 2003 - 17:38:41 CET