Principles and experiments of Oracle foreign key index creation

Principles and experiments of Oracle foreign key index creation

In the project, we require that all tables with a primary-child relationship use foreign key constraints to ensure the correct relationship between the primary and child tables. We do not recommend that you control this relationship by the application.

However, it is found that sometimes developers may not be aware of the need to define indexes for foreign key columns when submitting SQL statements, or it is unclear why foreign key columns need to be indexed, some so-called "collections" on the Internet will also index foreign key columns, including Master TOM, who once said:

The leading cause of the deadlock is that the foreign key is not indexed (the second cause is that the bitmap index on the table is updated concurrently ). In the following two cases, Oracle adds a full table lock to the child table after modifying the parent table:
1) if the primary key of the parent table is updated (if the relational database principle is followed, that is, the primary key should be unchangeable, this is rare), because there is no index on the foreign key, therefore, the sub-table is locked.
2) if a row in the parent table is deleted, the entire sub-table will also be locked (because there is no index on the foreign key ).

Therefore, from any perspective, it is necessary to have a good understanding of the principle of why the foreign key needs to create an index, or what is the problem if the foreign key does not create an index?

First, let's take a look at how the official Concept describes this issue.
(Reference: E11882_01/server.112/e40540/consist.htm # CNCPT88978)

Indexes and Foreign Keys
As a rule, foreign keys should be indexed. The only exception is when the matching unique or primary key is never updated or deleted. Indexing the foreign keys in child tables provides the following benefits:
(1) Prevents a full table lock on the child table. Instead, the database acquires a row lock on the index.
(2) Removes the need for a full table scan of the child table. As an illustration, assume that a user removes the record for department 10 from the departments table. If employees.department_id is not indexed, then the database must scan employees to see if any employees exist in department 10.
Locks and Foreign Keys
Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.
Locks and Unindexed Foreign Keys
When both of the following conditions are true, the database acquires a full table lock on the child table:
(1) No index exists on the foreign key column of the child table.
(2) A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.
Suppose that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id. Figure 9-3 shows a session modifying the primary key attributes of department 60 in the departments table.
In Figure 9-3, the database acquires a full table lock on employees during the primary key modification of department 60. This lock enables other sessions to query but not update the employees table. For example, employee phone numbers cannot be updated. The table lock on employees releases immediately after the primary key modification on the departments table completes. If multiple rows in departments undergo primary key modifications, then a table lock on employees is obtained and released once for each row that is modified in departments.
Locks and Indexed Foreign Keys
When both of the following conditions are true, the database does not acquire a full table lock on the child table:
(1) A foreign key column in the child table is indexed.
(2) A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.
A lock on the parent table prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or child table during the primary key modification. This situation is preferable if primary key modifications occur on the parent table while updates occur on the child table.
Figure 9-4 shows child table employees with an indexed department_id column. A transaction deletes department 280 from departments. This deletion does not cause the database to acquire a full table lock on the employees table as in the scenario described in “Locks and Unindexed Foreign Keys”.
If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, the deletion of department 280 can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.

According to the instructions in the official documents,

You do not need to create an index for a foreign key only when the unique key or primary key is not updated or deleted. Creating an index for a sub-table foreign key has two benefits:
(1) Avoid table locks on sub-tables. Instead, the database will obtain the row locks on the index.
(2) Avoid full table scanning on sub-tables. Assume that the record with the primary table id = 10 is deleted. If the department_id foreign key of the employees sub-table is not indexed, the employees sub-table is scanned in the whole table, to check whether there is a record with department id = 10.
When the following two conditions are met, the table lock of the sub-table is obtained:
(1) The foreign key of the sub-table does not have an index.
(2) modify the primary key of the primary table (for example, delete a row of records or modify the primary key value) or merge multiple row records of the primary table. Inserting records into the primary table does not obtain the table lock of the subtable.
The table lock of the sub-table is released only when the primary key value is modified.

Is the effect true? We use experiments for verification.

Create test table

SQL> create table t1  2  (id number,     3   name varchar2(1)  4  );Table created.SQL> alter table t1 add constraint pk_t1 primary key (id);Table altered.SQL> create table t2  2  (id number,  3   t1_id number,  4   name varchar2(1)  5  );Table created.SQL> alter table t2 add constraint pk_t2 primary key (id);Table altered.SQL> alter table t2 add constraint fk_t2 foreign key (t1_id) references t1(id);Table altered.SQL> insert into t1 values(1, 'a');1 row created.SQL> insert into t1 values(2, 'b');1 row created.SQL> insert into t2 values(1, 1, 'c');1 row created.SQL> commit;Commit complete.SQL> select * from t1;ID N---------- - 1 a 2 bSQL> select * from t2;ID  T1_ID N---------- ---------- - 1  1 c 

T1 is the primary table, t2 is the sub table, and t1_id column of t2 is the foreign key. For details, refer to id Primary Key column of Table t1.

Lab 1

Session 1 deletes the record with the primary table id = 2:

SQL> delete from t1 where id = 2;1 row deleted. 

Session 2: delete the record with the sub-Table id = 1:

SQL> delete from t2 where id = 1;1 row deleted. 

As described in the document, deleting a row of records in the master table locks the entire table in the sub-table. Why? Let's continue with lab 2.

Lab 2

Session 1 deletes a record with the sub-Table id = 1 (sid: 150 ):

SQL> delete from t2 where id = 1; 1 row deleted. or update t2 set name = 'C' where id = 1;

Session 2 deletes the record with the primary table id = 2 (sid: 144 ):

SQL> delete from t1 where id = 2; 

This session is in the hang state.

SQL> select object_name from dba_objects where object_id = 76828;OBJECT_NAMET1SQL> select object_name from dba_objects where object_id = 76830;OBJECT_NAMET2 

We can see that session 1 has two tmlocks, one TX lock, and session 2 has two tmlocks.

The hidden knowledge points here are the meanings of ID1 and ID2 columns in the v $ lock view. They are described in the Reference manual, but they are basically useless.

Refer to secooler's article (http://m.blog.itpub.net/519536/viewspace-693689/), see MOS: 29787.1, learn more about ID1 and ID2:

For the tmlock, ID1 indicates the table object, and ID2 is generally 0.

For the TX lock, ID1 indicates Decimal RBS & slot. The rollback segment number and slot number of the current transaction (in Decimal format, RBS and slot combination, according to 0 xRRRRSSSS RRRR = RBS number, SSSS = slot definition, 16-bit high represents the RBS value, corresponds to the XIDUSN field in VTRANSACTION, 16-bit low represents the slot value, corresponds to the XIDSLOT field in VTRANSACTION ), ID2 indicates the Decimal WRAP number and serial number.

Therefore, two tmlocks, where object_id = 76828 corresponds to table T1, and object_id = 76830 corresponds to table T2.

For the records in the TX line, the rollback segment number, slot number, and serial number of the current transaction can be calculated as follows.

SQL> select trunc(589843/65536) from dual;TRUNC(589843/65536)  9SQL> select mod(589843,65536) from dual;MOD(589843,65536)   19SQL> select XIDUSN,XIDSLOT,XIDSQN from V$TRANSACTION where XIDSQN=715;XIDUSN  XIDSLOT   XIDSQN 9            19               715 

Further, we can find some details from session 2 in hang, that is, the 10046 event of the primary table deletion operation:

PARSING IN CURSOR #11135800 len=25 dep=0 uid=90 oct=7 lid=90 tim=1458405691531128 hv=2708121416 ad='526137f8' sqlid='9kjcfz6hqp9u8'delete from t1 where id=2WAIT #11135800: nam='enq: TM - contention' ela= 4202458 name|mode=1414332420 object #=76830 table/partition=0 obj#=76830 tim=1458405695733683PARSING IN CURSOR #11130048 len=68 dep=1 uid=0 oct=3 lid=0 tim=1458405695736681 hv=4142137182 ad='57d9d9f0' sqlid='cpbvjc7vf7zuy' select /*+ all_rows */ count(1) from "TEST"."T2" where "T1_ID" = :1END OF STMTPARSE #11130048:c=2000,e=2221,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1458405695736680STAT #11128324 id=2 cnt=1 pid=1 pos=1 obj=76830 op='TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=111 us cost=2 size=0 card=82)' 

Two conclusions can be drawn from this section:

(1) When deleting the primary table record, a tmlock contention occurs. It must be noted that, in the trc file, this information is written after session 1 executes commit or rollback releases the sub-table. We can see that session 1 is executed, this causes a wait when session 2 executes the tmlock that needs to request table T2.

(2) When deleting a primary table record, the sub-Table record will be retrieved Based on the foreign key field, select/+ All_rows/Count (1) from "TEST ". "T2" where "T1_ID" =: 1, which means that if the foreign key does not have an index, this SQL statement performs a full table scan, this is also proved by table access full T2.

Lab 3

The operation procedure is the same as that in Experiment 2, except that session 2 changes from the delete operation to the update primary key operation: update t1 set id = 3 where id = 2

The effect is the same as that in Experiment 2, where session 2 is in the hang state, and in the event 10046, it is the same as in Experiment 2:

PARSING IN CURSOR #11123668 len=29 dep=0 uid=90 oct=6 lid=90 tim=1458407400035758 hv=1728227981 ad='5278e0fc' sqlid='43bqtdxmh5and' 97 update t1 set id=3 where id=2PARSING IN CURSOR #11109876 len=68 dep=1 uid=0 oct=3 lid=0 tim=1458407409907499 hv=4142137182 ad='57d9d9f0' sqlid='cpbvjc7vf7zuy'105  select /*+ all_rows */ count(1) from "TEST"."T2" where "T1_ID" = :1 

Lab 4

Session 1 deletes records with the sub-Table id = 1:

SQL> delete from t2 where id = 1;1 row deleted. 

Session 2 updates the non-primary key fields of the primary table id = 2 record:

SQL> update t1 set name = 'c' where id = 2;1 row updated. 

The hang is not displayed. You can perform this operation.

Lab 5

Session 1 insert a new table record:

SQL> insert into t2 values(2, 1, 'c');1 row created. 

Session 2 Insert a new record to the master table:

SQL> insert into t1 values(3, 'c');1 row created. 

The hang is not displayed. You can perform this operation.

Lab 6

Create a foreign key index and check the effect,

SQL> create index idx_t2_id on t2(t1_id);Index created. 

Session 1 deletes records with the sub-Table id = 1:

SQL> delete from t2 where id = 1;1 row deleted. 

Session 2 deletes the record with the primary table id = 2:

SQL> delete from t1 where id = 2; 

There is no hang at this time,

There is indeed no lock:

View 10046 events:

PARSING IN CURSOR #12183444 len=27 dep=0 uid=90 oct=7 lid=90 tim=1458479427981508 hv=3481522657 ad='57ded014' sqlid='87pqrfv7s7ng1'delete from t1 where id = 2END OF STMTPARSE #12183444:c=1999,e=2469,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1636297587,tim=1458479427981505EXEC #12183444:c=0,e=365,p=0,cr=1,cu=6,mis=0,r=1,dep=0,og=1,plh=1636297587,tim=1458479427982059STAT #12183444 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  T1 (cr=1 pr=0 pw=0 time=298 us)'STAT #12183444 id=2 cnt=1 pid=1 pos=1 obj=76829 op='INDEX UNIQUE SCAN PK_T1 (cr=1 pr=0 pw=0 time=26 us cost=0 size=3 card=1)'WAIT #12183444: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1458479427983901 

There is indeed no lock contention.

Summary:

(1) The foreign key does not have an index, which may lead to table locks in the sub-table, but the premise is as follows:
A. The sub-table can be deleted or modified.
B. You can delete a primary table or update a primary key.
If the preceding conditions are met, the hang status of the master table operation is displayed.

(2) If the foreign key is not indexed, deleting the primary table record or the associated query of the primary table sub-table will scan the entire table of the sub-table.

(3) No insert operation is performed on the primary table or sub-table, regardless of the sequence. No locks or hang status is generated.

(4) only when an external key is used to create an index, the operation in (1) will not be in the lock or hang status, and the operation in (2) will be able to use the index.

Through the above experiments, at least no index is created for external keys. With some perceptual knowledge, we should have a deeper understanding of why external keys should be indexed.