CBO chooses plan with highest cost
Hello,
Faced with CBO issues. Query chooses INDEX only plan with highest cost instead of plan with lowest cost.
I provided the testcase used to reproduce this issue:
>>
drop table t;
create table t as select level c1, level c2, mod(level, 10) c3 from dual connect by level<=1e6;
alter table t modify c3 not null;
create index t_c1_i on t(c1);
create index t_c2_i on t(c2);
create index t_c3_i on t(c3);
exec sys.dbms_stats.gather_table_stats( '', 'T')
explain plan for select count(*) from t where (c1 = 1 or c2 = 1) and c3 ^= 2;
Faced with CBO issues. Query chooses INDEX only plan with highest cost instead of plan with lowest cost.
I provided the testcase used to reproduce this issue:
>>
drop table t;
create table t as select level c1, level c2, mod(level, 10) c3 from dual connect by level<=1e6;
alter table t modify c3 not null;
create index t_c1_i on t(c1);
create index t_c2_i on t(c2);
create index t_c3_i on t(c3);
exec sys.dbms_stats.gather_table_stats( '', 'T')
explain plan for select count(*) from t where (c1 = 1 or c2 = 1) and c3 ^= 2;
0