Oracle Logbook

May 10, 2012

Weird pitfall at Oracle index monitoring usage.

Filed under: Uncategorized — heliodias @ 9:47 pm

Oracle 10g and below doesn´t detect correctly recursive usage of index at v$object_usage.


I deleted a row from a parent table (which made a recurse query at the index of the child table), and then I checked at v$object_usage to if the detection would be made. I found out that it didn´t as you may see below.

QL> create table l3_ajuste_b (id number , constraint l3_fk1 foreign key(id) references l3_ajuste (id))
  2  ;

SQL> create index l3_ajuste_b_ind on l3_ajuste_b(id) ;

Index created.

SQL> alter index l3_ajuste_b_ind monitoring usage;

Index altered.
SQL> delete l3_ajuste where id=2;

1 row deleted.

SQL> commit;
SQL> select USED from v$object_usage;



Then after a second attempt to delete, but this time getting a violation  of integrity constraint, somehow Oracle managed to log correctly.

SQL> delete l3_ajuste where id=1;
delete l3_ajuste where id=1
ERROR at line 1:
ORA-02292: integrity constraint (SYS.L3_FK1) violated – child record found
SQL> select USED from v$object_usage;



The bottom line, never drop indexes just because they aren´t showing at v$object_usage,

Check by yourself if they are helping any foreign key (which was my case).

Furthermore the Execution Optimizer often use the statistical information of index to create a better plan.

So you might even change by accident your executions plan, after  dropping a index that “is not in use”

My advice is use some Oracle tools to guarantee your plan stability before any drop of your indexes.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: