SQL Performance (MOSC)

MOSC Banner

CBO chooses plan with highest cost

edited Nov 20, 2013 10:52PM in SQL Performance (MOSC) 11 commentsAnswered
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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center