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 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 completedsql> 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, autotracesql> 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 '; -- The index usage is monitored to index_name table_name mon use start_monitoring end_monitoring ------------------------- --- ------------------- ----------------- ptb_pk PTB yes 03/22/2013 17:15:37

2. index monitoring when foreign keys exist

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_infoenter Schema (I. e. scott): scottenter 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 | + condition + constraintconstraint 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 autotracesql> 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 processedsql> commit; Commit complete. -- Author: Robinson -- Blog: http://blog.csdn.net/robinson_0612SQL> 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 indexes when foreign key indexes do not exist

-- Delete the index SQL> 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. During index monitoring, if the Sub-table has a foreign key constraint and a foreign key index, DML operations on the master table will not enable the sub-Table index to be used.
B. Although the Sub-Table index is not monitored due to the master table DML operation, if the Sub-table foreign key index does not exist, DML in the primary table will produce more consistent reads (compared with foreign key indexes)
C. As can be seen from the above, foreign key indexes cannot be blindly regarded as invalid and deleted.
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 lock the entire sub-table.

 

More references

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Oracle rowid

Null Value and index (1)

Null Value and index (2)

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor

Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)