FK 가 걸려 있는 테이블에 index 가 없을 경우의 사례 이다.
한 블로그에서 의 비교 실험한 결과를 참고 하면
아래와 같이 구성된 상태에서
testing_table <= 부모 테이블
testing_table1 <= 자식 테이블
testing_table1 에 FK 키를 걸고 인덱스를 걸지 않은 상태에서 DML( delete) 를 진행 했을때의 비교를 보여준다 .
1. 인덱스를 걸지 않은 상태에서의 DML(delete 작업을 진행 한경우다) 작업 진행시
SQL> delete from testing_table;
84770 rows deleted.
Elapsed: 00:58:42.60
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 191 (1)|
| 1 | DELETE | TESTING_TABLE | | | |
| 2 | INDEX FULL SCAN| SYS_C00142723 | 1 | 13 | 191 (1)|
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
87891 recursive calls
847612 db block gets
2244796030 consistent gets
12660 physical reads
145525560 redo size
824 bytes sent via SQL*Net to client
782 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
84770 rows processed
db block gets는 CURRENT mode에 있는 block의 데이터를 읽은 숫자이다. (수정할 데이터를 읽을때 읽기)
current mode라는 것은 과거의 시점이 아닌 바로 지금의 data를 읽는 것을 말한다.
consistent gets 읽기 일관성이 보장되는 상황에서 읽는 숫자이다. 데이터를 수정하지 않고 단지 읽기만 하기때문에 lock이
발생하지 않는다. 즉 DML문에서는 데이터를 찾기 위한 부분이 consistent gets에 나타나고 수정하기 위한 부분이 db block gets에 나타난다.
여기서는 인덱스를 걸지 않을 경우 수정 할 데이터를 찾기 위해서 많은 블럭을 읽고 있다.
데이터를 찾고 그 다음 데이터를 수정할 부분을 다시 읽는것이다.(Segment header나 block을 읽음)
2. 인덱스를 걸은 상태에서
SQL> -- Create an index on the child table (FK)
SQL> create index testing_table1_idx(id) on testing_table1 tablespace users; <= 인덱스를 걸어 준다.
SQL> delete from testing_table;
84770 rows deleted.
Elapsed: 00:00:40.91
SQL> delete from testing_table;
84770 rows deleted.
Elapsed: 00:00:35.72
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 191 (1)|
| 1 | DELETE | TESTING_TABLE | | | |
| 2 | INDEX FULL SCAN| SYS_C00142723 | 1 | 13 | 191 (1)|
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
85437 recursive calls
1102357 db block gets
170504 consistent gets
126026 physical reads
181334020 redo size
838 bytes sent via SQL*Net to client
782 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
84770 rows processed
한사이트에서 이렇게 도 언급하고 있다. table lock 은 잘모르겠지만 table full scan 이 발생하는 건 맞는것 같다.
Without an index on the child table's foreign key, table-level locking may occur when a parent row is updated
foreign key 에 인덱스가 없을경우 부모 테이블에 업데이트 발생시 table-level의 locking 이 발생한다