RE: Index Full Scan -- Strange Issue

Waleed,

I don't have the SQL with me since I am at home. And with the way that company is, possibly even submitting the actual SQL might be considered giving away trade secrets (even if I changed table and column names) ;-) Pretty paranoid about things they are. That's why I presented a "dummy" SQL statement to illustrate the nature of the query.

But I can guarantee you that the index in question (a single column BMI) is not referenced in the WHERE clause, and, columns not satisfied by any of the indexes are included in the SELECT, thus requiring the table be accessed.

Regarding the optimizer_index_cost_adj, I'm not sure how that would apply here but it is at 30. Yeah, dial it lower and index access is weighted a bit differently than FTS nudging the CBO to lean towards index access (same with the caching parameter). But that doesn't seem to play into why a full index scan on an un-needed index on a column with no criteria is favored. Sure, maybe to return ordered results and be able to skip a sort step, but that doesn't apply here since there is not sort step. And I can also see forcing that to take advantage of a trailing column with criteria in a concatenated index -- done that with great success. But, these are single column indices.

The multi_block_read_count is 32, which the higher that goes the less costly an FTS looks which would tilt it towards an FTS. These are things to ponder but I'm not sure they are playing a role in what I am seeing. I really need to do a 10053 trace and see what the heck the CBO is thinking. First thing I will do in the morning.

Anyway, though none of these ideas seem to apply, they are things I will consider. Thanks for throwing some ideas out there.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Khedr,
> Waleed
> Sent: Wednesday, March 13, 2002 7:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Index Full Scan -- Strange Issue
>
>
> Did you check if they have any of these Oracle init.ora parameters that
> favor index usage like OPTIMIZER_INDEX_COST_ADJ?
>
> Also may be the DB_FILE_MULTIBLOCK_READ_COUNT is set to a very low value.
>
> I have seen this when a table has huge number of indexes.
>
> Are you sure the used index has no columns that are part of the WHERE
> clause?
>
> Why do not you post the sql here?
>
> Regards,
>
> Waleed
>
> -----Original Message-----
> Sent: Wednesday, March 13, 2002 7:48 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> 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).

-- 
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).