René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

July 26, 2005: On verifying if an index is used

Sometimes, an index is created but never used. These indexes serve then no purpose but use space. Fortunatly, it's possible to monitor indexes during a time period. After this period, Oracle can be queried for which indexes on that table have been used.
Let's create a table:
create table ix_mon (
  a varchar2(10),
  b varchar2(10),
  c varchar2(10),
  d varchar2(10)
);
We're filling some random values into the table:
begin

  for i in 1 .. 100 loop
    insert into ix_mon values (
      dbms_random.string('a', 10),
      dbms_random.string('a', 10),
      dbms_random.string('a', 10),
      dbms_random.string('a', 10));
  end loop;

end;
/
An index is created on each column:
create index ix_mon_a on ix_mon (a);
create index ix_mon_b on ix_mon (b);
create index ix_mon_c on ix_mon (c);
create index ix_mon_d on ix_mon (d);
Now, I start to monitor the four indexes:
alter index ix_mon_a monitoring usage;
alter index ix_mon_b monitoring usage;
alter index ix_mon_c monitoring usage;
alter index ix_mon_d monitoring usage;
v$object_usage can be queried which indexes were used:
select index_name, used from v$object_usage where table_name = 'IX_MON';
Of course, none is used:
INDEX_NAME                     USE
------------------------------ ---
IX_MON_A                       NO
IX_MON_B                       NO
IX_MON_C                       NO
IX_MON_D                       NO
Three queries, that will each use the respective index:
select count(*) from ix_mon where a='1234567890';
select count(*) from ix_mon where c='1234567890';
select count(*) from ix_mon where d='1234567890';
Again querying v$object_usage:
select index_name, used from v$object_usage where table_name = 'IX_MON';
This time, it reports that ix_mon_b has not been used:
INDEX_NAME                     USE
------------------------------ ---
IX_MON_A                       YES
IX_MON_B                       NO
IX_MON_C                       YES
IX_MON_D                       YES
Lastly, the monitoring is turned off:
alter index ix_mon_a nomonitoring usage;
alter index ix_mon_b nomonitoring usage;
alter index ix_mon_c nomonitoring usage;
alter index ix_mon_d nomonitoring usage;
Of course, in real life, the period while monitoring is turned on will last longer, typically a day or a week.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.