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

Source: Internet
Author: User
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
Good luck

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.