Playing Index Hide And Seek SQL

 
create table but_what_if_we_want_to_test (dropping_it integer, filler varchar2(30));

insert into but_what_if_we_want_to_test 
  select rownum, dbms_random.string('x', 30)
  from   dual 
  connect by level <= 100;
  
commit;

exec dbms_stats.gather_table_stats(user, 'but_what_if_we_want_to_test');
  
create index now_you_see_me on but_what_if_we_want_to_test (dropping_it);

-- the query uses the index nicely
explain plan for
  select * from but_what_if_we_want_to_test
  where  dropping_it = 1;

select * from table(dbms_xplan.display);

-- now you don't 
alter index now_you_see_me invisible;

-- the invisible index can no longer be used; a full table scan is performed
explain plan for
  select * from but_what_if_we_want_to_test
  where  dropping_it = 1;

select * from table(dbms_xplan.display);

-- peek-a-boo, I see you again!
alter index now_you_see_me visible;

-- we're back to an index range scan, without having to re-create the index!
explain plan for
  select * from but_what_if_we_want_to_test
  where  dropping_it = 1;

select * from table(dbms_xplan.display);

drop table but_what_if_we_want_to_test purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)