Re: sql using full table scan instead of an index

ryanbobko_at_yahoo.com (ryan) wrote in message news:<29c46df8.0310310838.16ea7773_at_posting.google.com>...
> 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

Hi again,
Thanks for all the advice. My solution to the problem was to add a hint forcing the index on CHILD.pkey to be used. Deleting the statistics on the table had the same effect. I'm just starting the load process, and it's also possible that my selection criteria wasn't selective enough. Once I get more data in the tables, I re-enable the CBO and see if it does a better job. Until then, thanks again,

ry Received on Mon Nov 03 2003 - 16:08:00 CET