Some musings ... Another reason why Oracle might do a full index scan is that the query can be satisfied by visiting the index only, but then you wouldn't have the table lookup. Full Index Scans are scattered reads which read more blocks at a time than the one block read by the sequential read of a range scan, but that would be slower than an FTS, unless the table has lots of wasted space below the highwater mark.
Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----Original Message----- Sent: Wednesday, March 13, 2002 4: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: MacGregor, Ian A. 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).
