Top 60 Oracle Blogs

Recent comments

August 2013

OIC(A) again – 2

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


drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id, x))
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;

Here’s the plan:

Reuse Of Empty Index Leaf Blocks (Free Four)

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked: “Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?” […]