FK로 인한 DML 성능 저하

FK는 두 가지 원인으로 인해 DML 성능 저하가 발생한다. 첫 번째는 FK 인덱스 유지 비용이고, 두 번째 이유는 부모 테이블에 대한 룩업 작업이다. 이 중 후자가 성능 저하의 주요 원인이다.

테스트를 위해 아래와 같이 테이블을 생성하자. t1은 부모 테이블, t2는 테이블 인덱스와 FK가 없으며, t3는 인덱스, t4는 인덱스와 FK가 존재한다.

-- 1
DROP TABLE t1 CASCADE CONSTRAINT PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 100000');
CREATE TABLE t2 (c1 NUMBER);
CREATE TABLE t3 (c1 NUMBER);
CREATE TABLE t4 (c1 NUMBER);

CREATE INDEX t3_x1 ON t3 (c1);
CREATE INDEX t4_f1 ON t4 (c1);

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);
ALTER TABLE t4 ADD CONSTRAINT t4_f1 FOREIGN KEY (c1) REFERENCES t1 (c1);

아래는 t2, t3, t4 테이블에 10만건을 입력한 결과다. 쿼리 2-1은 입력 작업에 2936개의 블록 I/O가 발생했다. 쿼리 2-2는 인덱스 유지를 위해 6538개(=9474-2936)의 블록 I/O가 추가로 발생했다. 쿼리 2-3은 부모 테이블에 대한 룩업 작업에 20만 개(=209K-9474)의 블록 I/O가 추가로 발생했다. t1_pk 인덱스가 2레벨이므로 입력 건수의 2배만큼 블록 I/O가 발생했고, 버퍼 피닝이 동작하지 않았다.

-- 2-1
INSERT INTO t2 SELECT ROWNUM FROM XMLTABLE ('1 to 100000');

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                       |      1 |      0 |    2936 |
|   1 |  LOAD TABLE CONVENTIONAL            |                       |      1 |      0 |    2936 |
|   2 |   COUNT                             |                       |      1 |    100K|       3 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |      1 |    100K|       3 |
-------------------------------------------------------------------------------------------------

-- 2-2
INSERT INTO t3 SELECT ROWNUM FROM XMLTABLE ('1 to 100000');

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                       |      1 |      0 |    9474 |
|   1 |  LOAD TABLE CONVENTIONAL            |                       |      1 |      0 |    9474 |
|   2 |   COUNT                             |                       |      1 |    100K|       3 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |      1 |    100K|       3 |
-------------------------------------------------------------------------------------------------

-- 2-3
INSERT INTO t4 SELECT ROWNUM FROM XMLTABLE ('1 to 100000');

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                       |      1 |      0 |     209K|
|   1 |  LOAD TABLE CONVENTIONAL            |                       |      1 |      0 |     209K|
|   2 |   COUNT                             |                       |      1 |    100K|       3 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |      1 |    100K|       3 |
-------------------------------------------------------------------------------------------------