| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index Full Scan -- Strange Issue
Been a it busy but am following up. Waleed was on target with the question
on the optimizer_index_cost_adj (I owe you a beer, or your favorite
beverage). This particular DB has optimizer_index_cost_adj set to 1. I
originally said I thought it was 30 or 40, not sure why so low but there is
some history there about why they did that.
Recap. Assume a table has 30 columns. 3 not null single column BMI's exist on columns A, B, and C, not on D. Here is a sample query:
Select *
>From Table
Where D='ABCDE'
Note that there is no index on D, but, there are on columns A, B, and C. However, no criteria specified for any of those columns. The CBO chose to do a full index scan using the BMI on indexed column A, then getting each row from that BMI and filtering on column D criteria. I have yet to be able to duplicate in a test environment but understanding how a particular value is derived, touched on later, would help me get there.
We know the optimizer_index_cost_adj is used to tilt the scales either way regarding full table scan or index usage, but I would have assumed (yeah, I know ;-)) that this *only* applied when making the decision between using an index with criteria for the index versus a full table scan. Wrong! For example, if the WHERE clause had been "B=1", an indexed column, I could see the value for optimizer_index_cost_adj impacting the calculated cost. But in the case above with no criteria on an index, index access paths were still considered (10053 trace shows them being considered). And, with the value for optimizer_index_cost_adj at 1, the index access path was chosen. Set to 2, full table scan was chosen.
Here are some snippets from the 10053 trace files showing this.
Value of 1 for optimizer_index_cost_adj:
<<<Snip>>>
Access path: tsc Resc: 9696 Resp: 9696
<<<Snip>>>
Access path: index (no sta/stp keys)
INDEX#: 82652 TABLE: RENAMED_IT!!!
CST: 297 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00
******** Bitmap access path accepted ********
Cost: 4924 Selectivity: 1
Table scan cost of 9696. The CST of the full index scan was calculated at 297. Then, the total cost bumped to 4924. Includes additional cost for hitting the table I assume but how is this number derived? Value of it bumps higher and higher as the value of the optimizer_index_cost_adj is increased as illustrated in the following.
Value of 2 for optimizer_index_cost_adj:
<<<Snip>>>
Access path: tsc Resc: 9696 Resp: 9696
<<<Snip>>>
Access path: index (no sta/stp keys)
INDEX#: 82652 TABLE: RENAMED_IT!!!
CST: 297 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00
******** Bitmap access path rejected ********
Cost: 9849 Selectivity: 1
The index cost is still 297, but the calculated cost is now 9849, more than the table scan cost of 9696. As you continue to bump up the value for optimizer_index_cost_adj, you see the index cost stay the same, but that final cost for the index based access to continue to rise. Anyone know that calculation? I left out major parts of the 10053 trace but I have been trying to come up with some correlation between various values for how that 4924 and 9849 got calculated. No luck so far.
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 Larry
> Elkins
> Sent: Thursday, March 14, 2002 6:28 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Index Full Scan -- Strange Issue
>
>
> Waleed,
>
> Yeah, Ian's idea got me thinking. If you ask the question "When is it more
> efficient to access *all* rows in a table via an index versus a full table
> scan?", a HWM way up there and a small number of rows seems plausible. And
> when someone asks "why does a full table scan take so long when
> there are no
> rows?" the first idea we all think of is the HWM. So it is
> logical to extend
> that thinking to the first question? So, what would happen if I create a
> table with a few million rows and have an index on a column in the table.
> Then, I delete all the rows, drop and re-create the index, analyze, and
> issue a select * from table with no criteria? The index would be in good
> shape and we would have the HWM way up there. Would an index full scan be
> used? Can't complete the test -- I have to go to work and the delete is
> still running :-(
>
> But it is something to consider and something I can come back to. The goal
> here is to duplicate the case. And I now have some more question about how
> the tables in question have been handled. This is fun!
>
> 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 11:48 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Index Full Scan -- Strange Issue
> >
> >
> > Larry,
> >
> > I think Ian's idea might be correct. When you have a HWM the optimizer
> > will make its mind based on segments' sizes. Having high percentage of
> > deleted rows and fragmented segment in the table, leads the optimizer to
> > read te table indirectly using the index. Why this index and not the
> > others?
> > It could be also related to status of the index: Size, percentage of
> > deleted rows, clustering factor(very important), etc.
> >
> > Regards,
> >
> > Waleed
-- 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).Received on Sat Mar 16 2002 - 06:48:20 CST
 |
 |