Oracle index monitoring and foreign key Indexing

Oracle index monitoring and foreign key index Oracle monitoring index features provide us with a general condition to determine whether the 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 [SQL] -- demo environment SQL> select * from v $ version where rownum <2; BANNER alias Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production -- create master table SQL> create Table ptb (deptno number constraint ptb_pk primary key, dname varchar2 (20); table created. -- from scott. dept account copy data 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 metrics --- ------------------- --------------------- PTB_PK ptb yes no 03/22/2013 17:15:37 -- note that when collecting statistics, the index is not monitored, in 10 Gb, it will be monitored-enable autotrace 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 | explain SQL> set autot off; SQL> select * from v $ object_usage where index_name = 'ptb _ pK '; -- index usage is monitored to INDEX_NAME TABLE_NAME MON Use START_MONITORING END_MONITORING indexing --- ------------------- when PTB_PK ptb yes 03/22/2013 17:15:37 2. Monitoring Index conditions when foreign keys exist [delphi] SQL> create table ctb (id number, name varchar2 (30), deptno number); Table created. -- add a foreign key constraint for the sub-table SQL> alter Table ctb add constraint ctb_fk foreign key (deptno) references ptb (deptno) 2 on delete set null; table altered. -- fill the data SQL for the sub-Table> begin 2 for I in 1 .. 1000000 3 loop 4 insert into ctb values (I, 'name _ '| to_char (I), 10); 5 end loop; 6 commit; 7 end; 8/PL/SQL procedure successfully completed. -- create an index SQL> create Index I _ctb_fk_deptno on ctb (deptno) nologging; index created based on foreign keys. -- collect statistical information of sub-tables SQL> exec dbms_stats.gather_table_stats ('Scott ', 'ctb', cascade => true); PL/SQL procedure successfully completed. SQL> @ dba_table_info Enter Schema (I. e. SCOTT): SCOTT Enter Schema (I. e. EMP ): CTB + rule + | table information | + rule + Owner Table Name Tablespace Last Analyzed # of Rows --------------- scott ctb GOEX_SYSTEM_TBL 22-MAR-2013 17:26:02 1,000,731 + rule + | straints | + response + Constraint Name Type Column Name Search Conditio R/Constraint Name Delete Rule Status certificate ----------- ------------------ --------------- certificate ----------- CTB_FK Referential deptno scott. PTB_PK set null enabled -- the number of rows listed in the preceding statistics is 1,000,731 more than the actual number. SQL> select count (*) from ctb; COUNT (*) ---------- 1000000 SQL> select * from ctb where rownum <3; id name deptno ---------- 1045 name_1045 10 1046 name_1046 10 -- enable the SQL statement for the monitoring foreign key index> alter index I _ctb_fk_deptno Monitor; index altered. SQL> select index_name, monitoring, used from v $ object_usage where index_name = 'I _ CTB_FK_DEPTNO'; INDEX_NAME mon use cases --- I _CTB_FK_DEPTNO yes no -- enable autotrace SQL> set autot trace stat; SQL> delete from ptb where deptno = 20; delete a record from the master table 1 row deleted. statistics limit 172 recursive cballs 7 db block gets 31 consistent gets ---> at this time, the consistent reading is 31 1 physical reads 780 redo size 1114 bytes sent via SQL * Net to client 1184 bytes received via SQL * Net from client 4 SQL * Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed SQL> commit; Commit complete. -- Author: Robinson -- Blog: SQL> set autot off; -- The following query shows that the foreign key index is not used in SQL> select index_name, monitoring, used from v $ object_usage where index_name = 'I _ CTB_FK_DEPTNO '; INDEX_NAME mon use ------------------------------ --- I _CTB_FK_DEPTNO yes no 3. Monitor index conditions when NO foreign key Index exists [SQL] -- delete index SQL on foreign key> drop Index I _ctb_fk_deptno; index dropped. SQL> set autot trace stat; SQL> delete from ptb where deptno = 30; -- delete record 1 row deleted from the master table again. statistics ---------------------------------------------------------- 186 recursive cballs 6 db block gets 3502 consistent gets -- at this time, the consistent reading is 3502, 100 times more than the last time 0 physical reads 740 redo size 2065 bytes sent via SQL * Net to client 1479 bytes encoded ed via SQL * Net from client 6 SQL * Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed 4. Summary a. If the sub-table has a foreign key constraint and a foreign key index, the DML operation on the primary table does not enable the sub-Table index to use B. Although the Sub-Table index is not monitored due to the DML operation on the primary table, if the Sub-table foreign key index does not exist, DML on the primary table generates more consistent reads (relative to foreign key indexes) c. As you can see, foreign key indexes are not monitored, you cannot blindly consider that the index is invalid and delete d. If the sub-table does not have an index, updating the primary key of the master table and deleting a row in the master table will cause the entire sub-table to be locked.