UNIQUE 인덱스의 일부 선두 칼럼으로 PK 제약 조건을 생성하면 ORA-14196 에러가 발생합니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. c1 칼럼을 NOT NULL로 정의했습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NOT NULL, c2, c3) AS SELECT ROWNUM, ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000000');
아래와 같이 NOT NULL로 정의된 UNIQUE 인덱스의 칼럼으로 PK 제약 조건을 생성하면 PK 제약 조건 생성에 0.01초가 소요됩니다.
-- 2
CREATE UNIQUE INDEX t1_pk ON t1 (c1);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1) USING INDEX t1_pk;
경 과: 00:00:00.01
아래 t1_u1 인덱스는 c1, c2 칼럼으로 구성되어 있습니다. c1 칼럼으로 PK 제약 조건을 생성하면 ORA-14196 에러가 발생합니다.
-- 3
ALTER TABLE t1 DROP CONSTRAINT t1_pk DROP INDEX;
CREATE UNIQUE INDEX t1_u1 ON t1 (c1, c2);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1) USING INDEX t1_u1;
ORA-14196: 지정된 인덱스를 사용하여 제약 조건을 강제로 수행할 수 없습니다.
이 에러와 관련하여 Adding Primary Key on Unique Index Ora-01418 or ORA-14196 (Doc ID 577253.1) 문서에 아래와 같은 내용이 포함되어 있습니다.
We cannot use a prefix of a unique index to enforce a unique constraint. We can use a whole unique index or a prefix of a non-unique index to do that. This is the way Oracle was designed.
문ㅈ의 첫 번째 해결책은 NON-UNIQUE 인덱스의 일부 선두 칼럼으로 PK 제약 조건을 생성하는 것입니다. 키 값의 고유성을 검증하는 과정으로 인해 PK 제약 조건에 8.04초가 소요됩니다. 참고로 PK 제약 조건 생성은 병렬로 수행할 수 없습니다.
-- 4
DROP INDEX t1_u1;
CREATE INDEX t1_x1 ON t1 (c1, c2);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1) USING INDEX t1_x1;
경 과: 00:00:08.04
두 번째 해결책은 PK 제약 조건을 NOVALIDATE 상태로 생성하는 것입니다. 생성 후 PARALLEL QUERY의 병렬도를 강제 지정하여 PK 제약 조건의 VALIDATE 시간을 단축시킬 수 있습니다.
-- 5-1
ALTER TABLE t1 DROP CONSTRAINT t1_pk KEEP INDEX;
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1) USING INDEX t1_x1 NOVALIDATE;
경 과: 00:00:00.01
-- 5-2
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
ALTER TABLE t1 MODIFY CONSTRAINT t1_pk VALIDATE;
경 과: 00:00:05.09
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1;
참고로 PK 제약 조건의 VALIDATE 과정에서 아래와 같은 쿼리가 수행됩니다.
-- 6
SELECT /*+ ALL_ROWS ORDERED DYNAMIC_SAMPLING(2) */
a.ROWID, :1, :2, :3
FROM "TUNA"."T1" a
, (SELECT /*+ ALL_ROWS */
"C1"
FROM "TUNA"."T1" a
WHERE ("C1" IS NOT NULL)
GROUP BY "C1"
HAVING COUNT (1) > 1) b
WHERE ("A"."C1" = "B"."C1")
UNION ALL
SELECT /*+ ALL_ROWS ORDERED DYNAMIC_SAMPLING(2) */
a.ROWID, :1, :2, :3
FROM "TUNA"."T1" a
WHERE ("C1" IS NULL);
----------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | UNION-ALL | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) |
|* 4 | HASH JOIN BUFFERED | | Q1,03 | PCWP | |
| 5 | PX RECEIVE | | Q1,03 | PCWP | |
| 6 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 8 | INDEX FAST FULL SCAN | T1_X1 | Q1,01 | PCWP | |
| 9 | PX RECEIVE | | Q1,03 | PCWP | |
| 10 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH |
| 11 | VIEW | | Q1,02 | PCWP | |
|* 12 | HASH GROUP BY | | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | Q1,02 | PCWP | |
| 14 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH |
| 15 | HASH GROUP BY | | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 17 | INDEX FAST FULL SCAN| T1_X1 | Q1,00 | PCWP | |
|* 18 | PX COORDINATOR | | | | |
| 19 | PX SEND QC (RANDOM) | :TQ20000 | Q2,00 | P->S | QC (RAND) |
|* 20 | FILTER | | Q2,00 | PCWC | |
| 21 | PX BLOCK ITERATOR | | Q2,00 | PCWC | |
|* 22 | INDEX FAST FULL SCAN | T1_X1 | Q2,00 | PCWP | |
----------------------------------------------------------------------------------