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.


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