ORACLE - DAY 15

BUMSOO·2024년 8월 20일
아키텍처오라클 DBA

제약조건 체크 여부

CONSTRAINT test_id_pk PRIMARY KEY NOT DEFERRABLE INITIALLY IMMEDIATE (기본값)
CONSTRAINT test_id_pk PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE
CONSTRAINT test_id_pk PRIMARY KEY DEFERRABLE INITIALLY DEFERRED

  • NOT DEFERRABLE(기본값) : 지연할 수 없다. primary key, unique 제약조건인 경우 unique index 생성

  • DEFERRABLE : 지연할 수 있다. primary key, unique 제약조건인 경우 non index 생성.
    세션레벨에서 제약조건의 체크 여부를 즉시 또는 지연해서 체크하는 set constraints 명령어를 수행할 수 있다.

  • INITIALLY IMMEDIATE(기본값) : DML 작업을 수행하는 순간 즉시 제약조건을 체크한다.

  • INITIALLY DEFERRED : DML 작업을 수행하고 난 후 COMMIT을 수행하는 순간 제약조건을 체크한다. 제약조건을 위반한 행이 있는 경우 트랜잭션 전체 롤백된다.

SET CONSTRAINTS ALL IMMEDIATE; : 세션레벨에서 DEFERRABLE하게 생성한 모든 제약조건을 즉시 체크하는 방법
SET CONSTRAINTS ALL DEFERRED; : 세션레벨에서 DEFERRABLE하게 생성한 모든 제약조건을 COMMIT을 수행할 때 체크하는 방법
SET CONSTRAINTS 제약조건이름 IMMEDIATE; : 세션레벨에서 DEFERRABLE하게 생성한 특정한 제약조건을 즉시 체크하는 방법

제약조건 체크여부 실습

<기본값>

CREATE TABLE hr.test(
    id number CONSTRAINT test_id_pk PRIMARY KEY NOT DEFERRABLE INITIALLY IMMEDIATE,
    sal number CONSTRAINT sal_ck CHECK (sal > 1000) NOT DEFERRABLE INITIALLY IMMEDIATE);

SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

- NOT DEFERRABLE로 설정하면 DEFERRABLE 컬럼값이 NOT DEFERRABLE로 되고 INITIALLY IMMEDIATE로 설정하면 DEFERRED 컬럼값이 IMMEDIATE로 된다.
- 추가로 NOT DEFERRABLE 설정시 PRIMARY KEY으로 생성되는 INDEX도 UNIQUE INDEX로 생성된다.

<DEFERRABLE 적용>

CREATE TABLE hr.test(
    id number CONSTRAINT test_id_pk PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE,
    sal number CONSTRAINT sal_ck CHECK (sal > 1000) DEFERRABLE INITIALLY IMMEDIATE);
    
    
SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

- DEFERRABLE 컬럼이 DEFERRABLE로 변경되고 INDEX도 NONUNIQUE로 변경된걸 확인할 수 있다.
- 이 상태에서 DML 작업을 해도 즉시 제약조건을 체크한다.

<DEFERRABLE INITIALLY DEFERRED 적용>

CREATE TABLE hr.test(
    id number CONSTRAINT test_id_pk PRIMARY KEY DEFERRABLE INITIALLY DEFERRED,
    sal number CONSTRAINT sal_ck CHECK (sal > 1000) DEFERRABLE INITIALLY DEFERRED);
    
    
SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

INSERT INTO hr.test(id,sal) VALUES(1,2000); -- 제약조건 확인은 parse 단계의 semantic 단계에서 확인된다.
INSERT INTO hr.test(id,sal) VALUES(1,2000);
INSERT INTO hr.test(id,sal) VALUES(2,1000);
SELECT * FROM hr.test;
COMMIT;

- INITIALLY DEFERRED로 설정시 DML조작이 다 끝나고 COMMIT 할때 제약조건을 체크한다.
- 위반사항이 있으면 전체 ROLLBACK 시켜버린다.

세션레벨에서 제약조건을 즉시 또는 지연 하고 싶으면 무조건 DEFERRABLE로 설정되어 있어야 한다

CREATE TABLE hr.test(
    id number CONSTRAINT test_id_pk PRIMARY KEY DEFERRABLE INITIALLY DEFERRED,
    sal number CONSTRAINT sal_ck CHECK (sal > 1000) DEFERRABLE INITIALLY DEFERRED);

SET CONSTRAINTS ALL IMMEDIATE; -- 세션레벨에서만 즉시 적용

INSERT INTO hr.test(id,sal) VALUES(1,2000); -- 제약조건 확인은 parse 단계의 semantic 단계에서 확인된다.
INSERT INTO hr.test(id,sal) VALUES(1,2000);
INSERT INTO hr.test(id,sal) VALUES(2,1000);
SELECT * FROM hr.test;

⚠️단 주의할점은 DEFERRABLE로 설정할 경우 INDEX는 NONUNIQUE로 설정되기 때문에 UNIQUE인덱스에 비해 I/O에 대한 손해를 볼 수 밖에 없다. NONUNIQUE로 설정되는건 추후 DML 작업으로 제약조건이 걸린 컬럼의 유일성은 깨질 수 있기 때문에 당연하다고 생각된다.

제약조건 DISABLE, ENABLE

데이터를 이관 후 제약조건을 설정할때 데이터의 품질 이슈로 제약조건 설정이 안되는 경우가 있다.
그럴 경우 DISABLE과 ENABLE EXCEPTIONS INTO를 활용하여 해결 할 수 있다.

CREATE TABLE hr.test (
    id number,
    name char(10),
    sal number);

INSERT INTO hr.test(id,name,sal) VALUES(1,'A',1000);
INSERT INTO hr.test(id,name,sal) VALUES(2,'B',100);
INSERT INTO hr.test(id,name,sal) VALUES(1,'A',2000);
COMMIT;
SELECT * FROM hr.test;

- 처음 테일블을 제약조건 없이 생성 후 중복되는 데이터를 insert 하였다.

ALTER TABLE hr.test ADD CONSTRAINT test_id_pk PRIMARY KEY(id);

SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';


- 그 후 primary key 제약조건을 생성하려고 하면 당연히 기존 중복 데이터 때문에 오류가 발생하고 생성되지 않는다.

ALTER TABLE hr.test ADD CONSTRAINT test_id_pk PRIMARY KEY(id) DISABLE;
SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';


- 제약조건을 생성할때 DISABLE 옵션을 활용하여 생성하면 실제 제약조건이 실행되지는 않는 껍데기 뿐인 제약조건을 생성 할 수는 있다.

- DISABLED 된 상태를 ENABLE로 변경해야 하는데 그러기 위해서는 제약조건이 위반된 데이터를 따로 담아 놓을 수 있는 exceptions 테이블이 필요하다.

- local 서버에서 $ORACLE_HOME/rdbms/admin/utlexpt1 스크립트를 실행시켜 주면 exceptions 테이블이 자동으로 만들어진다.

- 자동으로 만들어지는 exceptions와 똑같은 구조의 테이블을 수동으로 만들어도 상관은 없다.

- exceptions 테이블은 local에 로그인 되어있는 계정 tablespace에 만들어지기 때문에 되도록 데이터 이관 tablespace와 동일한 계정으로 로그인 후 생성하는 편이 좋다.

ALTER TABLE hr.test ENABLE CONSTRAINT test_id_pk EXCEPTIONS INTO sys.exceptions;

SELECT * FROM exceptions;

- ENABLE CONSTRAINT 제약조건이름 EXCEPTIONS INTO 예외처리테이블이름 구문을 사용하여도 오류메시지는 나오지만 exceptions 테이블에 제약조건 위반 데이터가 들어온건 확인 할 수 있다.


- 아직까지 여전히 status와 valisated 값은 사용할 수 없다.

SELECT rowid, id, name, sal 
FROM hr.test a 
WHERE EXISTS (SELECT 1 FROM sys.exceptions WHERE row_id = a.rowid);


- 서브쿼리를 통해 제약조건 위반 데이터가 원본테이블에서 어떤 데이터인지를 확인할 수있다.

UPDATE hr.test
SET id = 3
WHERE rowid = 'AAASGIAADAAASENAAA';

COMMIT;

ALTER TABLE hr.test ENABLE CONSTRAINT test_id_pk EXCEPTIONS INTO sys.exceptions;

SELECT * FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT * FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

- 제약조건 위반 데이터를 변경 후 ENABLE CONSTRAINT 하면 정상적으로 제약조건의 상태가 ENABLED로 변경된걸 확인할 수 있다.
- 인덱스도 정상적으로 UNIQUE INDEX가 생성되었다.(제약조건 생성시 기본값으로 NOT DEFERRABLE로 설정되어 있기 때문이다.)

제약조건 상태

  • ENABLE VALIDATE : 기존데이터를 검증하고 제약조건을 활성화 한다. 검증이 돌아갈때는 LOCK이 걸린다.
  • ENABLE NOVALIDATE : 기존데이터를 검증하지 않고 제약조건을 활성화 한다. 하지만 PK제약조건이나 UNIQUE 제약조건에는 사용할 수 없다. UNIQUE 인덱스 조건을 위반하기 때문이다.
  • DISABLE VALIDATE : 기존데이터를 검증하고 제약조건을 비활성화 한다. 제약조건이 걸린 테이블의 DML 작업을 수행할 수 없도록 할때 사용한다.
  • DISABLE NOVALIDATE : 기존데이터를 검증하지 않고 제약조건을 비활성화 한다.
ALTER TABLE hr.test DISABLE CONSTRAINT test_id_pk;

- DISABLE의 기본값은 DISABLE NOVALIDATE 이다
- DISABLE 설정시 인덱스는 사라진다.

ALTER TABLE hr.test ENABLE CONSTRAINT test_id_pk;

- ENABLE의 기본값은 ENABLE VALIDATE 이다

CREATE TABLE hr.test (
    id number,
    name char(10),
    sal number);
    
    
ALTER TABLE hr.test ADD CONSTRAINT sal_ck CHECK(sal > 0) DISABLE VALIDATE; -- DISABLE VALIDATE은 DML을 막아버린다.

INSERT INTO hr.test(id,name,sal) VALUES(5,'D',2000);

- 제약조건에 DISABLE VALIDATE 옵션을 설정하면 해당테이블에 대한 모든 DML 작업을 불허한다.

번외) TABLE READ ONLY

10g 버전까지는 DISABLE VALIDATE를 이용하여 테이블의 DML을 막아 왔는데 11g 부터는 테이블에 READ ONLY 옵션 설정이 가능해졌다.

CREATE TABLE hr.test (
    id number ,
    name char(10),
    sal number);

ALTER TABLE hr.test READ ONLY;
/*ALTER TABLE hr.test READ WRITE; -- 기본값 */

INSERT INTO hr.test(id,name,sal) VALUES(1,'A',1000);

SELECT table_name, read_only FROM dba_tables WHERE owner = 'HR' ;

SQL*Loader

  • SQL*Loader는 외부 파일에서 오라클 데이터베이스 테이블로 데이터를 로드한다.

conventional load

  • insert와 똑같은 작동방식
  • 제약조건에 걸린다.
  • 자동으로 commit 한다.

  1. insa.ctl 컨트롤 파일안에 내용 입력
    vi insa.ctl
    LOAD DATA
    INFILE insa.dat
    INSERT
    INTO TABLE test
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    (ID,NAME,PHONE)

  2. insa.dat 데이터 파일안에 데이터 내용 입력
    vi insa.dat
    1,"JAMES","010-9999-0000"
    2,"GRACE","010-7777-7777"
    3,"SCOTT","010-8888-8888"
    3,"LUCAS","010-9999-9999"

  3. 데이터 이관
    #conventional load
    sqlldr hr/hr control=insa.ctl

  1. insa.log, insa.bad 파일 생성

log 파일 : 데이터 이관 내용을 상세히 보여줌
bad 파일 : 데이터 이관시 실패한 데이터 보관

<log파일>

<bad파일>

direct path load

  • 제약조건을 체크하지 않는다
  • save 방식

sqlldr hr/hr control=insa.ctl direct=true

SELECT * FROM hr.test;

- 제약조건을 무시하고 데이터가 전체 다 INSERT 되었다.

SELECT owner,constraint_name, status,deferrable,deferred,validated FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT index_name, status FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

- PRIMARY KEY 제약조건을 위반하고 데이터를 INSERT 하였기 때문에 PK제약조건은 DISABLE NOVALIDATE로 변경되었다

- INDEX는 UNUSABLE로 변경되어 테이블 조회 시점에 실행계획은 FULL TABLE SCAN으로 발생하고 DML 작업은 수행할 수 없는 상태로 빠진다.

- DROP INDEX를 하면 DML수행이 가능하다. 단 SELECT는 어쩔수 없이 FULL TABLE SCAN을 해야한다.

실무방법

1.데이터 이관받을 테이블 생성

CREATE TABLE hr.test(
    id number CONSTRAINT test_id_pk PRIMARY KEY ,
    name varchar2(30),
    phone varchar2(15));

2.제약조건이 있는 컬럼 전부 disable로 변경

ALTER TABLE hr.test DISABLE CONSTRAINT test_id_pk; -- idex 삭제, status disable, validated not validated

SELECT owner,constraint_name, status,deferrable,deferred,validated FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT index_name, status FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

3.direct path load 실행
sqlldr hr/hr control=insa.ctl direct=true

  1. 데이터 이관 확인
SELECT * FROM hr.test;

  1. 제약조건 위반 데이터 exceptions 테이블에 쌓아놓기
ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id_pk EXCEPTIONS INTO hr.exceptions;

SELECT * FROM hr.exceptions;

  1. 위반된 데이터 확인 후 수정
SELECT rowid, id, name, phone 
FROM hr.test a
WHERE EXISTS (SELECT row_id
            FROM hr.exceptions
            WHERE row_id = a.rowid);
 
UPDATE hr.test
SET id = 4
WHERE rowid = 'AAASHGAAHAAAAFrAAD';

  1. 데이터 수정 확인
SELECT * FROM hr.test;

  1. excetions 테이블 초기화
TRUNCATE TABLE hr.exceptions;
  1. 제약조건 ENABLE로 변경
ALTER TABLE hr.test ENABLE VALIDATE CONSTRAINT test_id_pk EXCEPTIONS INTO hr.exceptions;

  1. 제약조건, 인덱스 확인
SELECT owner,constraint_name, status,deferrable,deferred,validated FROM dba_constraints WHERE owner = 'HR' AND table_name = 'TEST';
SELECT index_name, status FROM dba_indexes WHERE owner = 'HR' AND table_name = 'TEST';

profile
BUMSOO
이전 포스트

ORACLE - DAY 14

다음 포스트

ORACLE - DAY 16

0개의 댓글