optimizer_index_cost_adj and hints ...
Hi all !
I've encountered a very strange problem on a very simple query...
Here is the query :
select * from bigtable where col1='XXX'
The col1 columns is indexed (with Ã* bitmap index because of the low cardinality of the column).
Both the table and index are freshly analyzed.
test1 :
When I try to explain plan, I realize that the CBO make an access full to bigtable table and it takes 1 minute to execute.
test2 :
When I add a INDEX hint (select /*+ INDEX (bigtable idx_col1) */ ...), the index is used and it takes less than 1 second to execute !
test3 :
When I set the optimizer_index_cost_adj to a low value than the default one (from 60 to 1), the explain plan shows that the index is well used, BUT the query takes one minute to execute ...
1) With exactly the same execution plan (test2 and 3) but different cost, we obtain different response time. How could it be possible if the plans are the same ?
2) Why the CBO does not choose the good plan itself (test2), whereas it's a very simple query ...
Thanks a lot in advance for your replies ...
Re: optimizer_index_cost_adj and hints ...
Quote:
Originally posted by htanga
2) Why the CBO does not choose the good plan itself (test2), whereas it's a very simple query ...
I think the problem is low cardinality, which tends to mean that a condition on that column is not very selective. If (to take an extreme case) 1% have col1='XXX' and 99% have col1='YYY' (which is a bit odd if it is CHAR(2)!) then the select for 'XXX' works best by index, 'YYY' by full table scan (probably).
(I have no answer for Q1)
Re: Re: optimizer_index_cost_adj and hints ...
Quote:
Originally posted by DaPi
I think the problem is low cardinality, which tends to mean that a condition on that column is not very selective. If (to take an extreme case) 1% have col1='XXX' and 99% have col1='YYY' (which is a bit odd if it is CHAR(2)!) then the select for 'XXX' works best by index, 'YYY' by full table scan (probably).
(I have no answer for Q1)
Unfortunatly the low cardinality is aparently not the problem ...
The predicate select about 5% of the table's rows.