[Fk_index] whether there is an index difference in the foreign key

There are two main problems: 1. Blocking during DML operations, ii. Speed problems during DML operations I. Blocking problems when the foreign key is not indexed and the field associated with the foreign key in the primary table is updated, level 4 lock (S) needs to be added to the sub-table. When indexes are available, when updating the foreign key association fields of the master table, 2 must be added to the sub-table.

There are two main problems: 1. Blocking during DML operations, ii. Speed problems during DML operations I. Blocking problems when the foreign key is not indexed and the field associated with the foreign key in the primary table is updated, level 4 lock (S) needs to be added to the sub-table. When indexes are available, when updating the foreign key association fields of the master table, 2 must be added to the sub-table.

The difference between adding an index to a foreign key and without an index:

There are two main problems: 1. Blocking during DML operations and 2. Speed problems during DML operations

I. Blocking

When the foreign key is not indexed and the field associated with the foreign key of the master table is updated, a four-level lock (S) must be applied to the sub-table; when the sub-table is indexed, when updating the foreign key association fields of the primary table, two levels of locks (RS) must be added to the sub-table ). When the child table already has RX, the S lock cannot be compatible, resulting in a blocking of updating the master table. If the sub-table does not have a lock, the operation to update the master table will not be blocked (we will not commit after the update). At this moment, if the foreign key does not have an index, Level 4 lock (S) it is added in an "instant", and it is not easy to observe the release. In the case of an index, the level 2 Lock (RS) added to the sub-table will always exist, until the master table is updated for session rollback or submission.

Whether data is inserted into a sub-table with or without a foreign key index requires a level 2 Lock (RS) for the master table. Will this operation be blocked, check whether the records in the master table have the RX lock. Here is the test with delete, and other DML statements are the same.

1. Create two tables and insert data to simulate the experiment environment

BALLONTT @ PROD> create table dept (deptno number, dname varchar2 (10 ));

BALLONTT @ PROD> alter table dept add constraint pk_dept primary key (deptno );

BALLONTT @ PROD> create table emp (empno number, ename varchar2 (10), deptno number );

BALLONTT @ PROD> alter table emp add constraint fk_emp foreign key (deptno) references dept (deptno );

BALLONTT @ PROD> insert into dept values (01, 'A ');

BALLONTT @ PROD> insert into dept values (02, 'bb ');

BALLONTT @ PROD> insert into dept values (03, 'cc ');

BALLONTT @ PROD> insert into dept values (04, 'dd ');

BALLONTT @ PROD> commit;

Commit complete.

BALLONTT @ PROD> insert into emp (empno, deptno) values (111,01 );

BALLONTT @ PROD> insert into emp (empno, deptno) values (222,02 );

BALLONTT @ PROD> commit;

Commit complete.

2. Confirm the table information

BALLONTT @ PROD> select * from dept;

DEPTNO DNAME

--------------------

1 aa

2 bb

3 cc

4 dd

BALLONTT @ PROD> select empno, deptno from emp;

EMPNO DEPTNO

--------------------

111 1

222 2

BALLONTT @ PROD> select a. object_id, a. object_name, l. session_id from

2 all_objects a, v $ locked_object l

3 where a. object_id = l. object_id;

No rows selected

3. Execute the following DML operation in session 1 (session_id = 125) (when the emp table does not have an index)

BALLONTT @ PROD> insert into emp (empno, deptno) values (333,3 );

1 row created.

View locked object information

BALLONTT @ PROD> select a. object_id, a. object_name, l. session_id from

2 all_objects a, v $ locked_object l

3 where a. object_id = l. object_id;

OBJECT_ID OBJECT_NAME SESSION_ID

------------------------------------------------

9752 EMP 125

9750 DEPT 125

BALLONTT @ PROD> select sid, type, id1, id2, lmode, request from v $ lock where sid = 125;

Sid ty ID1 ID2 LMODE REQUEST

------------------------------------------

125 TM 9750 0 2 0

125 TM 9752 0 3 0

125 TX 65558 105 6 0

For the insert operation of emp, add the two-mode table lock (that is, the RS lock) to dept (id: 9750) and the EMP (id: 9752) apply table-Level Lock RX (LMODE 3) and row-Level Lock X (LMODE 6)

4. Perform DML on the table where the primary key is located in session 2 (session_id = 113) to check whether the table is blocked.

BALLONTT @ PROD> update dept set deptno = 10 where deptno = 3; --- Blocking

View lock Information

BALLONTT @ PROD> select sid, type, id1, id2, lmode, request from v $ lock where sid in (113,125 );

Sid ty ID1 ID2 LMODE REQUEST

----------------------------------------------------

125 TM 9750 0 2 0

125 TM 9752 0 3 0

113 TM 9750 0 3 0

113 TM 9752 0 0 4

125 TX 65558 105 6 0

(9750 represents dept and 9752 represents emp.) To update dept, you must add the table-Level Lock RX to the table dept and apply the S lock (REQUEST 4) to the EMP table ). But at this time, the EMP has the RX lock caused by the insert operation, which is incompatible with the S lock. Therefore, the update operation on DEPT is blocked because the S lock cannot be obtained.

Update dept set deptno = 16 where deptno = 4;-- Same as blocking, for the above reasons.

5. Terminate Session 2, roll back session 1, and add an index to the foreign key of the EMP table

BALLONTT @ PROD> create index ind_emp on emp (deptno );

6. Repeat the above three steps and execute the following statement in session 2(With foreign key indexes, the following statement requires the RS lock on the sub-table first, and then apply for the master table RX lock, whether the sub-table is blocked depends on whether there is an RX lock on the record to which the RS lock is applied to the sub-table, and whether the record to which the base table needs to be locked by the RX lock has an RS lock)

BALLONTT @ PROD> update dept set deptno = 10 where deptno = 4; -- no blocking

View the lock information:

BALLONTT @ PROD> select sid, type, id1, id2, lmode, request from v $ lock where sid in (113,125 );

Sid ty ID1 ID2 LMODE REQUEST

----------------------------------------------------

125 TM 9750 0 2 0

125 TM 9752 0 3 0

113 TM 9750 0 3 0

113 TM 9752 0 2 0

113 TX 327726 105 6 0

125 TX 262156 107 6 0

6 rows selected.

Session 1 (sid: 125) creates three locks for emp insertion. (As described above)

Session 2 (sid: 113) also has three locks for the dept update operation, which are the two locks RX, and TX brought about by the regular update on the dept table. The third lock is the RS lock applied to the child table EMP. The RS lock is compatible with the existing RX lock on EMP, so it will not be blocked.

Update dept set deptno = 16 where deptno = 3; -- blocking

2. Speed problems during DML operations ·

When you use on delete cascade to DELETE a record in the parent table, if the foreign key in the child table does not use an index, this operation will scan the entire table of the child table, in fact, this full table scan is not required. Even worse, if you delete records from multiple parent tables, a full table scan will be performed for each record to be deleted. You can imagine the impact on performance!

The connection query between the parent table and the child table is similar. When performing this connection query, if the index is not used by the external key, the query speed is greatly reduced.

We can see that, We should create an index on the foreign key.