Oracle index monitoring and foreign key Indexing

The Oracle monitoring index feature allows us to roughly determine whether an index is used. This is because the index is monitored when statistics are collected in Oracle 10 Gb, which is not produced by SQL statements. In the case of 11g, there is no type. Second, if a sub-table has a foreign key, will the index be monitored when the primary table is operated? This topic is described below.

1. General Index Monitoring

-- Demo Environment
SQL> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

-- Create a master table
SQL> create table ptb (deptno number constraint ptb_pk primary key, dname varchar2 (20 ));

Table created.

-- Copy data from scott. dept account
SQL> insert into ptb select deptno, dname from dept;

4 rows created.

SQL> commit;

Commit complete.

-- Enable index monitoring
SQL> alter index ptb_pk monitoring usage;

-- Collect statistics for the master table
SQL> exec dbms_stats.gather_table_stats ('Scott ', 'ptb', cascade => true );

PL/SQL procedure successfully completed

SQL> select * from v $ object_usage where index_name = 'ptb _ pK ';

INDEX_NAME TABLE_NAME MON Use START_MONITORING END_MONITORING
---------------------------------------------------------------------------------------------------
PTB_PK ptb yes no 03/22/2013 17:15:37

-- Pay attention to the above situation. When collecting statistical information, indexes are not monitored for use, and 10 Gb indexes are monitored.
-- Enable autotrace below
SQL> set autot trace exp;

SQL> select * from ptb where deptno = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3991869509

Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 12 | 1 (0) | 00:00:01 |
| 1 | table access by index rowid | PTB | 1 | 12 | 1 (0) | 00:00:01 |
| * 2 | index unique scan | PTB_PK | 1 | 0 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

SQL> set autot off;
SQL> select * from v $ object_usage where index_name = 'ptb _ pK'; -- the index usage is monitored

INDEX_NAME TABLE_NAME MON Use START_MONITORING END_MONITORING
---------------------------------------------------------------------------------------------------
PTB_PK ptb yes 03/22/2013 17:15:37

1 2 3 Next Page