Weird pitfall at Oracle index monitoring usage.

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

Scenario:

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;

USE
—
NO

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;

USE
—
YES

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.