Search

Top 60 Oracle Blogs

Recent comments

OICA

OIC(A) again – 2

Continuing from the previous post, here is one more case when adjusting optimizer_index_cost_adj may hurt you.


Setup:

drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id, x))
as
select trunc(rownum/10)
     , mod(rownum, 10)
     , s1.text
  from all_source s1, all_source s2
 where rownum <= 1e6;

exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1', cascade=>true, no_invalidate=>false)

alter session set optimizer_index_cost_adj = 100;
alter session set optimizer_index_caching  = 0;

explain plan for select * from t1 where x = :1;
@xp

Here’s the plan:

OIC(A) again

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.


Here’s a simple table T1 holding 1M rows with the indexed column X having 10 distinct values: