Consider Reverse Key Index

Well this post was inspired by seeing some reverse index in our environment to avoid contention (May be tested with small set of data and might not have observed issues), which was pretty good but as we know this has very ugly side effects. Most of us know about it but this is just an reminder to myself and use reverse key indexes with extreme caution otherwise can lead to wild wild west.

So, as mention earlier there are few very nasty side effects of reverse key index and let’s start with some test. 1st performance test case with select queries based on reverse index, oh by the way i am not going to mention that reverse key index can’t be used with range predicates as we know.

Let’s start by creating a test table from dba_objects


create table REV_TEST parallel 4 tablespace soe as
select rownum id,x.owner,x.object_name,x.object_id,x.object_type,x.created
from dba_objects x ,(select rownum from dual connect by level <=100) y;

create unique index IX_REV_TEST on REV_TEST(ID) ;
exec dbms_Stats.gather_table_stats(null,'REV_TEST',cascade=>true,degree=>4);

declare
name rev_test.object_name%type;
begin
for i in 50000..60000 loop
select object_name into name from REV_TEST where id=i;
end loop;
end;
/

Now With first test case without reverse index, here are the details for that for loop with first showing size, leaf blocks and clustering factor


Non Reverse

SEGMENT_NAME                 MB     BLOCKS
-------------------- ---------- ----------
IX_REV_TEST                1472     188416
REV_TEST              5634.4375     721208

CLUSTERING_FACTOR
-----------------
           900381

   LF_BLKS   PCT_USED
---------- ----------
    182577         90

Before Any Query

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10001      0.51       0.56          0          0          0           0
Fetch    10001      0.14       0.12        113      40004          0       10001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20003      0.66       0.69        113      40004          0       10001

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID REV_TEST (cr=4 pr=4 pw=0 time=1808 us cost=3 size=31 card=1)
         1          1          1   INDEX UNIQUE SCAN IX_REV_TEST (cr=3 pr=3 pw=0 time=1323 us cost=2 size=0 card=1)(object id 109599)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                       113        0.00          0.

As, you can see that we did approximately same amount of IO to get that equality key. Now, let’s drop this table and recreate same data but using reverse key index.


SEGMENT_NAME                 MB     BLOCKS
-------------------- ---------- ----------
IX_REV_TEST                1472     188416
REV_TEST              5634.4375     721208

CLUSTERING_FACTOR
-----------------
         83834113

   LF_BLKS   PCT_USED
---------- ----------
    182716         90

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10001      0.04       0.58          0          0          0           0
Fetch    10001      2.49       4.72      10342      40004          0       10001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20003      2.54       5.31      10342      40004          0       10001

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID REV_TEST (cr=4 pr=4 pw=0 time=3668 us cost=3 size=31 card=1)
         1          1          1   INDEX UNIQUE SCAN IX_REV_TEST (cr=3 pr=3 pw=0 time=3171 us cost=2 size=0 card=1)(object id 109598)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     10342        0.01          4.13

As, you can see from above output you’r clustering factor went to the roof, and now all your key are scattered all over the place and you have to do more IO’s to get that same work done and if this grows beyond your cache imagine what would happen, you’r cache will constantly flush in flush out blocks and even good blocks.

This is one case what about insert performance, because all we did this to avoid right hand tree contention reduction. While doing this let’s see what we have introduced here. First without reverse key


insert into rev_test select 82221812+rownum id,x.owner,x.object_name,x.object_id,x.object_type,x.created
    from dba_objects x

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.47       0.52       1622       5355      13497       91156
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.49       0.54       1622       5355      13497       91156

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       100        0.00          0.04
  db file scattered read                          1        0.00          0.00
  direct path read                                6        0.00          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        7.43          7.43

SEGMENT I/O WAIT SUMMARY
~~~~~~~~~~~~~~~~~~~~~~~~
                                                                 Wait   Times    Average        Max          Average
 #      Obj  Type  Segment Name(1)         Event Name         Time(2)  Waited  Wait Time  Wait Time  Blocks   Blocks
--- ------- ------ ---------------- ------------------------ -------- ------- ---------- ---------- ------- --------
 1:  109593 TABLE  TARAL.REV_TEST   db file sequential read     0.032      72   0.000441   0.002966      72        1
 2:      18 TABLE  SYS.OBJ$         direct path read            0.011       6   0.001901   0.006582      87     14.5

As, you can see we have inserted about 91k rows and our top io wait just not that bad, let’s see if we have reverse key index


insert into rev_test select 82312968+rownum id,x.owner,x.object_name,x.object_id,x.object_type,x.created
	from dba_objects x 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      4.76      12.94      27508       4778     282675       91156
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.78      12.97      27508       4778     282675       91156

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     25986        0.05          9.99
  db file scattered read                          1        0.00          0.00
  direct path read                                6        0.00          0.00
  log file switch completion                      1        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        3.02          3.02

SEGMENT I/O WAIT SUMMARY
~~~~~~~~~~~~~~~~~~~~~~~~
                                                                   Wait   Times    Average        Max          Average
 #      Obj  Type   Segment Name(1)          Event Name         Time(2)  Waited  Wait Time  Wait Time  Blocks   Blocks
--- ------- ------ ------------------ ------------------------ -------- ------- ---------- ---------- ------- --------
 1:  109603 INDEX  TARAL.IX_REV_TEST  db file sequential read     9.966   25917   0.000385   0.053896   25917        1
 2:  109593 TABLE  TARAL.REV_TEST     db file sequential read     0.023      59   0.000385   0.001039      59        1

Now we have introduced much bigger problem with this. So, next time if you think of reverse key index consider all this issues. If anyone have anything more to say or can provide more information then i will be happy to include it

Advertisements

Oracle 12c TABLE ACCESS BY INDEX ROWID BATCHED


Few days back exploring 12c DB on my small VM found this little new feature. Here is the output from 11gR2

SQL_ID 3a936zyv7jupf, child number 0
-------------------------------------
SELECT w.WAREHOUSE_NAME, w.LOCATION_ID, i.PRODUCT_ID,i.QUANTITY_ON_HAND
 FROM WAREHOUSES w, INVENTORIES i WHERE i.PRODUCT_ID IN (10,5,1)
 AND w.WAREHOUSE_ID = i.WAREHOUSE_ID

Plan hash value: 2661993828

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 61 (100)| | 2604 |00:00:00.09 | 40 | 30 | | | |
|* 1 | HASH JOIN | | 1 | 2700 | 105K| 61 (2)| 00:00:01 | 2604 |00:00:00.09 | 40 | 30 | 947K| 947K| 1298K (0)|
| 2 | TABLE ACCESS FULL | WAREHOUSES | 1 | 1000 | 26000 | 2 (0)| 00:00:01 | 1000 |00:00:00.01 | 6 | 6 | | | |
| 3 | INLIST ITERATOR | | 1 | | | | | 2604 |00:00:00.07 | 34 | 24 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| INVENTORIES | 3 | 2700 | 37800 | 58 (0)| 00:00:01 | 2604 |00:00:00.07 | 34 | 24 | | | |
|* 5 | INDEX RANGE SCAN | INV_PROD_IX | 3 | 2700 | | 8 (0)| 00:00:01 | 2604 |00:00:00.05 | 17 | 10 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Read more of this post

Oracle Index Monitoring

Today someone asked how to Monitor Index usage. It’s simple to turn on monitoring on index. Oracle support told there is no easy way to do it and in some term that is right too.

But the funny thing is, object usage report might be wrong while you rebuild or gather statistics on index and some other things. Well, Sometimes monitoring is not the right way to identify index usage. Mostly, this whole exercise is done for some space gain or for performance. For example in warehouse for each load they disable index and then rebuild after load and at this point of time performance degradation takes place while rebuilding indexes. This is not the part of discussion but we will include this also little bit today.

Here are some examples of this.

Let’s start with how v$object_usage columns are.

  • Create two Different Users test1 & test2
  • TEST1@taral > @desc sys.v$object_usage;
    
    Col# SEG_COL_ID Column Name                    Null?      Type
    ---- ---------- ------------------------------ ---------- -------------------------
       1          1 INDEX_NAME                     NOT NULL   VARCHAR2(30)
       2          2 TABLE_NAME                     NOT NULL   VARCHAR2(30)
       3          3 MONITORING                                VARCHAR2(3)
       4          4 USED                                      VARCHAR2(3)
       5          5 START_MONITORING                          VARCHAR2(19)
       6          6 END_MONITORING                            VARCHAR2(19)
    
    6 rows selected.
    
    TEST1@taral > @view sys.v$object_usage;
    
    Owner      VIEW_NAME                      View Text
    ---------- ------------------------------ ----------------------------------------------------------------
    SYS        V$OBJECT_USAGE                 select io.name, t.name,
                                              decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
                                              decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
                                              ou.start_monitoring,
                                              ou.end_monitoring
                                              from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
                                              where io.owner# = userenv('SCHEMAID')
                                              and i.obj# = ou.obj#
                                              and io.obj# = ou.obj#
                                              and t.obj# = i.bo#
    
    
    First Test Case With Two Different Users
    =========================================
    drop table test1.xx purge;
    create table test1.xx as select object_id,object_name from dba_objects;
    create index test1.xx_idx on test1.xx(object_id);
    select * from v$object_usage;
    
    TEST1@taral > select * from v$object_usage;
    
    no rows selected
    
    TEST1@taral > alter index test1.xx_idx MONITORING USAGE;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 10:57:15
    
    TEST1@taral > select * from test1.xx; --Lines Not Displayed
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 10:57:15
    
    select * from test1.xx where object_id=41;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES YES 10/20/2010 10:57:15
    
    But if i tried from different User (Because o.owner# = userenv('SCHEMAID'))
    
    TEST2@taral > select * from v$object_usage;
    
    no rows selected
    
    
    Second Test Case With Explain Plan
    =========================================
    set autotrace off;
    drop table test1.xx purge;
    create table test1.xx as select object_id,object_name from dba_objects;
    create index test1.xx_idx on test1.xx(object_id);
    select * from v$object_usage;
    alter index test1.xx_idx MONITORING USAGE;
    select * from v$object_usage;
    SET AUTOTRACE TRACEONLY EXPLAIN;
    select * from test1.xx where object_id=41;
    set autotrace off;
    select * from v$object_usage;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 15:32:32
    
    TEST1@taral > SET AUTOTRACE TRACEONLY EXPLAIN;
    TEST1@taral > select * from test1.xx where object_id=41;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2275028263
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XX     |     1 |    79 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | XX_IDX |     1 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    TEST1@taral > set autotrace off;
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES YES 10/20/2010 15:32:32
    
    
    Third Test Case With Index Rebuild
    -----------------------------------
    set autotrace off;
    drop table test1.xx purge;
    create table test1.xx as select object_id,object_name from dba_objects;
    create index test1.xx_idx on test1.xx(object_id);
    select * from v$object_usage;
    alter index test1.xx_idx MONITORING USAGE;
    select * from v$object_usage;
    alter index test1.xx_idx rebuild;
    select * from v$object_usage;
    ALTER INDEX test1.xx_idx NOMONITORING USAGE;
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 15:38:58
    
    TEST1@taral > alter index test1.xx_idx rebuild;
    
    Index altered.
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             NO  YES 10/20/2010 15:38:58
    
    TEST1@taral > alter index test1.xx_idx MONITORING USAGE;
    
    Index altered.
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_IDX                         XX                             YES NO  10/20/2010 15:39:50
    
    So, this query will solve this issue
    
    SELECT * FROM v$object_usage WHERE (monitoring='YES' AND used='YES') OR (used='YES' AND end_monitoring IS NOT NULL) ;
    
    

    As, in starting i said we will see some examples of how this can’t be useful.

    Let’s say there are 5-6 indexes on one table and i had turned on monitoring for all this indexes. Some query only hit this index once and that change the status of index. So, my report from v$object_usage will say i have to keep this index even though it has been used only once. So, this won’t say how many times this index has been really used. Also, there is other danger involved with this is you might end up deleting some index which you might need as monitoring may not give you whole picture.

     

     

    
    set autotrace off;
    drop table test1.xxf purge;
    drop table test1.xx purge;
    create table test1.xx (object_id number constraint xx_pk primary key, object_name varchar2(128)) ;
    insert into test1.xx select rownum,object_name from dba_objects where rownum < 2001; commit; select * from v$object_usage; alter index test1.xx_pk MONITORING USAGE; set autotrace on; select * from test1.xx where object_id=41; select * from test1.xx where object_id=100; set autotrace off; select * from v$object_usage; TEST1@taral > set autotrace off;
    TEST1@taral > drop table test1.xxf purge;
    
    Table dropped.
    
    TEST1@taral > drop table test1.xx purge;
    
    Table dropped.
    
    TEST1@taral > create table test1.xx (object_id number constraint xx_pk primary key, object_name varchar2(128)) ;
    
    Table created.
    
    TEST1@taral > insert into test1.xx select rownum,object_name from dba_objects where rownum < 2001; 2000 rows created. TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral > select * from v$object_usage;
    
    no rows selected
    
    TEST1@taral > alter index test1.xx_pk MONITORING USAGE;
    
    Index altered.
    
    TEST1@taral > set autotrace on;
    TEST1@taral > select * from test1.xx where object_id=41;
    
     OBJECT_ID OBJECT_NAME
    ---------- --------------------------------------------------------------------------------------------------------------------------------
            41 FET$
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 266797879
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     1 |    79 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XX    |     1 |    79 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | XX_PK |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    TEST1@taral > select * from test1.xx where object_id=100;
    
     OBJECT_ID OBJECT_NAME
    ---------- --------------------------------------------------------------------------------------------------------------------------------
           100 FIXED_OBJ$
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 266797879
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     1 |    79 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XX    |     1 |    79 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | XX_PK |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    TEST1@taral > set autotrace off;
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XX_PK                          XX                             YES YES 10/20/2010 16:50:20
    

    So, this is not telling you how many times this index has been used.

    
    create table test1.xxf (object_id number, object_type varchar2(128) , constraint xxf_fk foreign key(object_id ) references xx(object_id));
    insert into test1.xxf select decode(mod(rownum,1000),0,1,mod(rownum,1000)) object_id, stage.object_type from dba_objects stage;
    commit;
    create index test1.xxf_fk on test1.xxf(object_id);
    exec dbms_stats.gather_table_stats(user,'XX');
    exec dbms_stats.gather_table_stats(user,'XXF');
    alter index test1.xxf_fk monitoring usage;
    select * from v$object_usage;
    set autotrace on;
    delete test1.xx where object_id = 2000;
    commit;
    set autotrace off;
    select * from v$object_usage;
    
    TEST1@taral > create table test1.xxf (object_id number, object_type varchar2(128) , constraint xxf_fk foreign key(object_id ) references xx(object_id))
    
    Table created.
    
    TEST1@taral > insert into test1.xxf select decode(mod(rownum,1000),0,1,mod(rownum,1000)) object_id, stage.object_type from dba_objects stage;
    
    72302 rows created.
    
    TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral > create index test1.xxf_fk on test1.xxf(object_id);
    
    Index created.
    
    TEST1@taral > exec dbms_stats.gather_table_stats(user,'XX');
    
    PL/SQL procedure successfully completed.
    
    TEST1@taral > exec dbms_stats.gather_table_stats(user,'XXF');
    
    PL/SQL procedure successfully completed.
    
    TEST1@taral > alter index test1.xxf_fk monitoring usage;
    
    Index altered.
    
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XXF_FK                         XXF                            YES NO  10/20/2010 16:50:23
    XX_PK                          XX                             YES YES 10/20/2010 16:50:20
    
    TEST1@taral > set autotrace on;
    TEST1@taral > delete test1.xx where object_id = 2000;
    
    1 row deleted.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 203824999
    
    ----------------------------------------------------------------------------
    | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | DELETE STATEMENT   |       |     1 |     4 |     1   (0)| 00:00:01 |
    |   1 |  DELETE            | XX    |       |       |            |          |
    |*  2 |   INDEX UNIQUE SCAN| XX_PK |     1 |     4 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OBJECT_ID"=2000)
    
    Statistics
    ----------------------------------------------------------
            178  recursive calls
              7  db block gets
             35  consistent gets
              0  physical reads
            116  redo size
            679  bytes sent via SQL*Net to client
            611  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral > set autotrace off;
    TEST1@taral > select * from v$object_usage;
    
    INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------------------ --- --- ------------------- -------------------
    XXF_FK                         XXF                            YES NO  10/20/2010 16:50:23
    XX_PK                          XX                             YES YES 10/20/2010 16:50:20
    
    

    If you see now foreign key index is not been used from v$object_usage. So, it’s safe to delete it.

    
    TEST1@taral > drop index test1.xxf_fk;
    
    Index dropped.
    
    TEST1@taral > set autotrace on;
    TEST1@taral > delete test1.xx where object_id = 1999;
    
    1 row deleted.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 203824999
    
    ----------------------------------------------------------------------------
    | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | DELETE STATEMENT   |       |     1 |     4 |     1   (0)| 00:00:01 |
    |   1 |  DELETE            | XX    |       |       |            |          |
    |*  2 |   INDEX UNIQUE SCAN| XX_PK |     1 |     4 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OBJECT_ID"=1999)
    
    Statistics
    ----------------------------------------------------------
            190  recursive calls
              7  db block gets
            217  consistent gets
              0  physical reads
            732  redo size
            679  bytes sent via SQL*Net to client
            611  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    TEST1@taral > commit;
    
    Commit complete.
    
    TEST1@taral >
    

    See how much now more work we have to do after removing index which was useful. So, don’t rely on v$object_usage. It’s not a metric.



    Reference: 

  • Secrets of the Oracle Database – Norbert Debes
  • Tim Hall