Index Full Scan -- Strange Issue

Listers,

For the second time in two weeks, I have seen a query (2 different ones) where the CBO choose an INDEX FULL SCAN diving into each and every row via the index. For this query there is no criteria on the indexed column being used (though there are on some other indexed columns), nor is there anything causing a *sort* operation. I've probably seen a handful of times, and am familiar with the concept, of where the CBO might choose to do a full index scan even with no criteria on the column to retrieve the rows from the table in sorted order to avoid a sort step. It decides the extra block reads are less costly than the calculated cost of doing an FTS and a sort operation. But in this case, there is no order by, no group by, no union, nothing that would cause a sort, and, no criteria on the column.

A plan similar to the following (used a hint to get this plan on a test table here at home) is generated:

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=340 Card=100000 Bytes=1100000)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=340 Card=100000 Bytes=1100000)

   2 1 INDEX (FULL SCAN) OF 'CODE_MASTER_PK' (UNIQUE) (Cost=188 Card=100000)

And the query would be like:

select ....
>From tablea

where col1 = 1
  and col2 = 2
  and col3 = 3

And an index, let's say on col4, would be used.

And the strange thing is that there are indexes on other columns with criteria being specified on those columns. Now I can understand if the CBO calculates the cost of an FTS as being less expensive than indexed access for those other columns, but, I don't think I have *ever* seen a case like this where a full index scan with no criteria on the index and no sort operation going on was the method chosen to get each row from the table (with filtering applied on the rows via other criteria). One would think an FTS would be better as opposed to accessing every *row* via rowid through the index.

Any ideas? Maybe I've overlooked something really obvious. Solaris 2.7 running 64 bit Oracle 8.1.7.2.1. And the indices are single column BMI's, so I'm sure that complicates things a bit. I'll have to dig into that a bit more. By the way, a DBA rebuilt the table and indices over the weekend and did an analyze compute. And in the case we first noticed this, the same strange plan is still used. I guess we will do a 10053 trace and see if that turns up anything (though I'm not particularly skilled at interpreting 10053 traces).

Maybe someone will have the obvious reason so I can slap myself in the forehead and go Duh!

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).