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 [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
