sql using full table scan instead of an index

[ryan]

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

[LKBrwn_DBA]

Try giving it a HINT:


select /*+ INDEX(CHILD pkey) */ CHILD.*
from CHILD, PARENT
where PARENT.value=100
and CHILD.pkey=PARENT.pkey
and PARENT.vdate between date1 and date2;
--
Posted via http://dbforums.com

[mcstock]

evidently the optimizer has decided the full table scan is faster than the
indexes for this query

your selection criteria is on the smaller table -- the traditional rule of
thumb for using an index is 2% or less of rows returned. likely, your
parent.value and parent.vdate predicates are not very selective

oracle is pretty fast at full table scans, all things being equal, with
multiblock reads and such.

consider how you'd have to do this 'manually' -- is i'm looking for a
reasonable large number of occurrences of a value in a book, do i want to
flip thru the pages (since i'm a good speed reader) or do i want to flip
back and forth from the index, often visiting the same page (i.e. data
block) multiple times

if you had some selection criteria on the CHILD table, you'd more likely see
index usage

--
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
[phone]
"ryan" <ry*******@yahoo.com> wrote in message
news:29**************************@posting.google.c om...

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

[Mark D Powell]

ry*******@yahoo.com (ryan) wrote in message news:<29**************************@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

Ryan, it would have been nice to see the actual explain plan. I am
not sure from your post if Oracle is driving on the child table or
performing the FTS in a loop.

Based on the way the query is written I would guess that Oracle should
drive on the Parent table entering it with either the index on value
or the index on the date range. Value appears to be a constant. If
this is true and if 100 is selective then a histogram on this column
might be useful if Oracle did not chose to drive on this. The access
by vdate would appear to use bind variables so Oracle does not know
what percentage of the table will have to be scanned.

You did not tell us what type of join Oracle chose to do? Did Oracle
chose a hash join? That would pretty much require a FTS of child if
Oracle drove on Parent.

HTH -- Mark D Powell --

[ryan]

ry*******@yahoo.com (ryan) wrote in message news:<29**************************@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