In-depth understanding of Oracle index (20): whether the foreign key should be indexed

First, express my position, I absolutely support foreign keys and must add indexes!
Although this requirement has been lowered in the higher version of Oracle, the following causes are still involved:

① Deadlock

The primary cause of the deadlock is that the foreign key is not indexed.
Whether updating the primary key of the parent table or deleting a parent table record, a table lock is applied to the child table.
This will not need to lock more rows, thus affecting concurrency


② On Delete Cascade

Each deleted parent row scans the entire table of the child table once.
For example:
EMP is a sub-table of Dept.
Delete dept where deptno = 10 will be cascaded to EMP


③ Query a sub-table from the parent table

For example:
EMP is a sub-table of Dept.
Select *
From Dept, EMP
Where EMP. deptno = Dept. deptno and
Dept. dname =: x


In addition, verify that the sub-table is locked because the foreign key is not indexed. You can use the following methods:

Alter table <child table name> disable table lock;

Therefore, any update or delete of the table lock caused by the parent table will receive the following error:

Error at line 1:

ORA-00069: cannot acquire lock -- table locks disable for <child table name>

Here is a simple test of the foreign key not indexed:

Create a table: HR @ orcl> Create Table t_father (ID number, name varchar2 (25), primary key (ID); HR @ orcl> Create Table t_sun (FID number, name varchar2 (25), foreign key (FID) References t_father (ID); HR @ orcl> select table_name, constraint_name, status, r_constraint_name from user_constraints where owner = 'hr' and table_name in ('t_ father ', 't_ Sun'); table_name constraint_name status r_constraint_name ----------------------- ------- Certificate -------- effect_father then enabledt_sun sys_c005497 enabled sys_c005495: HR @ orcl> insert into t_father select rownum, rownum | 'A' from dual connect by rownum <1000; HR @ orcl> insert into t_sun select rownum, rownum | 'B' from dual connect by rownum <1000; HR @ orcl> commit; HR @ orcl> exec dbms_stats.gather_table_stats (ownname => 'hr', Tabname => 't_ father '); HR @ orcl> exec dbms_stats.gather_table_stats (ownname => 'hr', tabname => 't_ Sun '); run the following Tom script to check tables with no foreign keys indexed: column columns format A30 using tablename format A15 word_wrappedcolumn constraint_name format A15 word_wrappedselect table_name, constraint_name, cname1 | nvl2 (cname2 ,', '| cname2, null) | nvl2 (cname3,', '| cname3, null) | nvl2 (cname4,', '| cname4, null) | | Nvl2 (cname5, ',' | cname5, null) | nvl2 (cname6, ',' | cname6, null) | nvl2 (cname7 ,', '| cname7, null) | nvl2 (cname8,', '| cname8, null) columns from (select B. table_name, B. constraint_name, max (decode (Position, 1, column_name, null) cname1, max (decode (Position, 2, column_name, null) cname2, max (decode (Position, 3, column_name, null) cname3, max (decode (Position, 4, column_name, null )) Cname4, max (decode (Position, 5, column_name, null) cname5, max (decode (Position, 6, column_name, null) cname6, max (decode (Position, 7, column_name, null) cname7, max (decode (Position, 8, column_name, null) cname8, count (*) col_cnt from (select substr (table_name, 1, 30) table_name, substr (constraint_name, 1, 30) constraint_name, substr (column_name, 1, 30) column_name, position from user_cons_col Umns) A, user_constraints B where. constraint_name = B. constraint_name and B. constraint_type = 'R' group by B. table_name, B. constraint_name) cons where col_cnt> All (select count (*) from user_ind_columns I where I. table_name = cons. table_name and I. column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8) and I. column_position <= cons. col_cnt group by I. index_name)/hr @ orcl> /Table_name constraint_name columns ------------------------------ ------------------------------------------ t_sun sys_c005497 FID test: 1) session_a: HR @ orcl> select Sid from V $ session where Sid in (select Sid from V $ mystat where rownum = 1); Sid -------- 159hr @ orcl> Delete t_sun where FID = 998; 1 row deleted.2) session_ B: HR @ orcl> select Sid from V $ session where Sid in (select Sid from V $ mystat whe Re rownum = 1); Sid ---------- 142 HR @ orcl> Delete t_sun where FID = 123; 1 row deleted.3) session_a: HR @ orcl> Delete t_father where id = 555; ---- the table lock requested for the sub-table is not available. If the sub-table is locked by Hang, use the following script to query the database lock: select. sid | decode (request, 0, ': holder', ': Waiter') sess_id, blocking_session blocker, lmode, request,. type, C. object_name, decode (row_wait_obj #,-1, 'holder of lock !!! ', Dbms_rowid.rowid_create (1, row_wait_obj #, row_wait_file #, row_wait_block #, row_wait_row #) row_id, nvl (SQL _fulltext, 'holder of lock !!! ') Sqltext from V $ lock a, V $ locked_object B, all_objects C, V $ Session D, V $ SQL ewhere (id1, Id2,. type) in (select id1, Id2, type from V $ lock where request> 0) and. SID = B. session_id and B. object_id = C. object_id and D. SID =. sid and D. SQL _hash_value = E. hash_value (+) sys @ orcl>/sess_id blocker lmode request ty object_name row_id sqltext -------------------------------------------------------- -- ---------- -- ------------------------------ ------------------ Wait 159: Waiter 142 3 5 TM t_sun holder of lock !!! Delete t_father where id = 555159: Waiter 142 3 5 TM t_father holder of lock !!! Delete t_father where id = 555142: holder 3 0 TM t_sun holder of lock !!! Holder of lock !!! 142: holder 3 0 TM t_father holder of lock !!! Holder of lock !!!

By David Lin
2013-06-07
Good luck