Re: sql using full table scan instead of an index

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
(888) 512-2048


"ryan" <ryanbobko_at_yahoo.com> 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