꿈꾸는 개발자, DBA 커뮤니티 구루비

1. 데이타베이스에 데이터무결성 시행 맡기기

1-1 데이터 무결상을 클라이언트단에서 처리할 경우의 문제점

1) 어플리케이션에 무결성을 관리하기 위한 엄청난 양의 코드가 삽입되어야 한다.
2) 신규 어플리케이션이 추가될 때마다 같은 코드가 추가되어야 하면 무결성 관리를 위한 규칙 변경시 모든 어플리케이션을 수정해야 한다.
3) 어플리케이션에서 관리되는 데이터 무결성은 서버로의 왕복을 필요로 하고 서버에서도 관리되어야 하므로 느리다.

1-2 데이터베이스에서 무결성을 유지하는 경우의 장점

1) 어플리케이션에서 할 경우 <쿼리다시쓰기> (P478 참조) 와 같은 기능을 이용할 수 없다.
2) 아무리 철저하게 프로그램을 구현해도 로직상의 헛점이 생겨 어느 시점엔가 데이터 무결성이 손상된다.
(특히 유지보수시 담당자가 여러 번 바뀌다 보면 무결성 규칙이 제대로 인수인계가 안되는 경우가 많다)
3) 서버에서 시행되는 무결성이 훨씬 빠르다.
4) 데이터베이스가 보다 많은 정보를 제공한다.

1-3 어플리케이션에서 무결성 검사의 문제점 예제

1)트리거 생성

EMP테이블에서 모든 부서가 사원을 3~8명 사이만 가지도록 강제한다(0으로 줄어드는 경우 제외)


CREATE OR REPLACE TRIGGER EMPCNT
AFTER DELETE OR INSERT OR UPDATE
ON SCOTT.EMP
/*
아래 코드가 있을 경우 다음과 같은 에러가 발생한다.
ORA-04091: 테이블 SCOTT.EMP가 변화하고 있어서 트리거/함수가 이를 볼 수 없습니다.
따라서 자신의 테이블에 트리거를 생성하는 경우 개별 로우가 영향받을 때마다 실행하라는 [FOR EACH ROW] 구문은 사용할 수 없다???
*/
--FOR EACH ROW
DECLARE

BEGIN
   
   FOR IREC IN(
	 	 SELECT DEPTNO,COUNT(*) EMPS
	 	 FROM EMP
	 	 GROUP BY DEPTNO
	 	 HAVING COUNT(*)<3 OR COUNT(*)>8
	 )
   LOOP
	 RAISE_APPLICATION_ERROR(-20000,'DEPARTMENT '||IREC.DEPTNO||' HAS '||IREC.EMPS||' EMPLOYEES');
   END LOOP;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END EMPCNT;
/

2)부서테이블 점검

5명인 부서 20에 대해 3명을 삭제하도록 할 것이다.


SELECT 
	DEPTNO
	,COUNT(*)
	,CASE 
		WHEN COUNT(*) NOT BETWEEN 3 AND 8 THEN '<<<' 
		ELSE NULL
	END FLAG
FROM EMP
GROUP BY DEPTNO

---------------------
DEPTNO   CNT     FLAG
---------------------
10       3                                           
20       5                                           
30       6

3)삭제실행

오라클에 두개의 세션을 연결 한 후 각각 아래의 쿼리를 실행한다.



(세션1)
DELETE FROM EMP WHERE EMPNO IN(7369,7566) --2명삭제

(세선2)
DELETE FROM EMP WHERE EMPNO IN(7788); --1명삭제
COMMIT; --세션1에서 두명을 삭제했으므로 더 이상 삭제가 되지 않아야 하나 삭제가 됨

(세션1)
COMMIT; --세션2에서 1명을 삭제했으므로 2명 삭제는 안되어야 하나 삭제가 됨

4)원인

트리거가 실행되는 시점은 COMMIT 시점이 아닌 DELETE문 실행시점이다.
세션1에서 2명 삭제 쿼리를 날리는 경우 5-2=3명으로 규칙에 어긋나지 않으므로 실행
세션2에서 1명 삭제 쿼리를 날리는 경우 세션1에서 COMMIT을 하지 않아 테이블에 5-1=4명이 있으므로 아무런 에러없이 실행
세션1에서 COMMIT을 하는 경우 이미 트리거가 실행되었고 트리거 실행시점엔 규칙에 어긋나지 않았으므로 에러없이 실행가능

5)해결책

직렬화와 서버가 시행하는 무결성을 이용한다

(?) 오라클에서의 직렬화란 무엇인가?
(프로그램에서 객체의 직렬화란 객체 자체를 저장하거나 어딘가로 전송하고자 할 때 직렬화 기법을 이용한다)



1 DEPT 테이블에 EMP_COUNT 칼럼 추가

ALTER TABLE DEPT ADD EMP_COUNT NUMBER CONSTRAINT MUSTBE_BETWEEN_38
CHECK (EMP_COUNT BETWEEN 3 AND 8 OR EMP_COUNT=0)
DEFERRABLE INITIALLY DEFERRED --commit 단위로 제약조건을 보류한다

2 EMP_COUNT 칼럼에 해당 부서의 인원수 업데이트

UPDATE DEPT SET EMP_COUNT=(
SELECT COUNT(*) 
FROM EMP
WHERE EMP.DEPTNO=DEPT.DEPTNO)

3 EMP_COUNT 칼럼의 NOT NULL로 변경

ALTER TABLE DEPT MODIFY EMP_COUNT NOT NULL

4 트리거 생성

CREATE OR REPLACE TRIGGER DEPTCNT
AFTER DELETE OR INSERT OR UPDATE
ON SCOTT.EMP
/*
아래 코드가 없을 경우 에러
ORA-04082: 테이블 레벨 트리거에서 NEW 혹은 OLD 참조는 허용되지 않습니다
*/
FOR EACH ROW
DECLARE
BEGIN
   
   /*EMP테이블에서 DEPTNO 이외의 칼럼이 변경된 경우 리턴*/
   IF (UPDATING AND :OLD.DEPTNO = :NEW.DEPTNO) THEN
   	  RETURN;
   END IF;
   
   /*입력과 부서코드 수정이라면 카운트 증가*/
   IF (INSERTING OR UPDATING) THEN
   	  UPDATE DEPT SET EMP_COUNT=EMP_COUNT+1
	  WHERE DEPTNO = :NEW.DEPTNO;
   END IF;
   
   /*삭제와 부서코드 수정이라면 카운트 감소*/
   IF (UPDATING OR DELETING) THEN
   	  UPDATE DEPT SET EMP_COUNT=EMP_COUNT-1
	  WHERE DEPTNO = :OLD.DEPTNO;
   END IF;

   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END DEPTCNT;
/

5 실행

(세션1)
DELETE FROM EMP WHERE EMPNO IN(7369,7566)

(세션2)
DELETE FROM EMP WHERE EMPNO IN(7788)

세션1에서 COMMIT을 하지 않으면 세션2는 대기상태가 된다.
세션1에서 COMMIT을 완료하면 세션2에서 곧바로 에러가 발생한다.
따라서 원하는 결과를 얻을 수 있지만 로우 하나 하나 변경시마다 빈번한 직렬화로 인해 DB의 동시성과 확정성이 떨어진다

1-4 결론

어플리케이션에서의 데이타 무결성 검토가 무조건 나쁜 건 아니다.
날짜에는 숫자만 사용하도록 한다든지, 기간을 입력할 때 시작일은 종료일보다 클 수 없도록 제약한다는지 등의
코딩은 서버단이 불필요한 일을 하지 않도록 하는등 잇점이 있기도하다.
그러므로 어플리케이션에서의 무결성은 서버쪽 무결성의 보안수단으로 사용하는 것이 좋다.

2. 올바른 데이터 유형을 사용하라

1-1 잘못된 데이터 유형 사용례

1) 날짜,시간,수를 저장하는데 문자열을 사용한다.
2) 모든 문자열 저장시 VARCHAR2(4000)을 사용한다.
3) 모든 문자열 저장시 CHAR(2000)을 사용하여 데이타 추출시 항상 TRIM()으로 가공한다.
4) BLOB 유형에 텍스트를 저장한다.

1-2 잘못된 데이터 유형 사용으로 인한 문제점

1) 성능이 떨어진다.
2) 필요한 저장소가 중가된다.
3) 데이터 무결성이 침해된다.
4) 데이터베이스에 삽입되는 순간 날짜가 실제로 날짜이고, 수가 유효한 수라는 것을 검증하는 과정에서 편집내용이 손실된다. (?)

1-3 데이터무결성 침해

1) 날짜나 수에 문자열을 사용할 경우 숫자가 아닌 알파벳형 문자가 들어갈 여지가 있으며 나중에 날짜로의 변환이 불가능하거나 사칙연산이 안되는 경우가 발생한다.
2) '01/02/03' 이 입력되어 있는 경우 'yy/mm/dd' 인지 'dd/mm/yy' 인지 확인할 방법이 없다.

1-4 성능저하



1. 테이블 생성
CREATE TABLE TEST AS
SELECT
	  TO_CHAR(TO_DATE('19950101','YYYYMMDD')+ROWNUM,'YYYYMMDD') STR_DATE
	  ,TO_DATE('19950101','YYYYMMDD')+ROWNUM DATE_DATE
FROM ALL_OBJECTS

2. 인덱스 생성
CREATE INDEX T_STRDATE_IDX ON TEST(STR_DATE)
CREATE INDEX T_DATEDATE_IDX ON TEST(DATE_DATE)

3. 통계정보 생성
ANALYZE TABLE TEST COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS

4. STR_DATE 인덱스 사용

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT * FROM TEST
  2  WHERE STR_DATE BETWEEN '20001231' AND '20010101';

STR_DATE DATE_DAT
-------- --------
20001231 00/12/31
20010101 01/01/01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=313 Bytes=4695)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=313 Bytes=4695)

   2    1     INDEX (RANGE SCAN) OF 'T_STRDATE_IDX' (NON-UNIQUE) (Cost=2 Card=313)

5. DATE_DATE 인덱스 사용

SQL> SELECT * FROM TEST
  2  WHERE DATE_DATE BETWEEN TO_DATE('20001231','YYYYMMDD')
  3  AND TO_DATE('20010101','YYYYMMDD');

STR_DATE DATE_DAT
-------- --------
20001231 00/12/31
20010101 01/01/01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Bytes=15)

   2    1     INDEX (RANGE SCAN) OF 'T_DATEDATE_IDX' (NON-UNIQUE) (Cost=2 Card=1)

6. 해설

1) DATE_DATE 인덱스를 사용하는 경우 이 칼럼이 날짜형이므로 20001231과 20010101 사이에는 오직 하루만 존재한다는 걸 
옵티마이져가 알고 있으므로 딱 두건의 인덱스만 찾는다.
2) 그러나 STR_DATE의 경우 문자형이므로 20001231과 20010101 사이에는 20001232~20010100 라는 상당히 많은 값이 
존재할 수 있으므로 이 사이값을 찾아 인덱스를 뒤져야 한다. 경우에 따라서는 인덱스 스캔이 아닌 테이블 
풀 스캔이 일어나기도 한다.
3) 따라서 이로인한 성능차이가 발생하게 되며 그 차이는 검색조건의 범위가 넓으면 넓을 수록 더욱 격차가 벌어지게 된다.

1-5 저장소 요구의 증가

1. 고정길이 문자열이든 가변길이 문자열이든 적절하고 구체적인 길이를 할당하여 사용하다록 한다.
2. 모든 고정길이 문자열 칼럼을 CHAR(2000) 으로 정해버리면 저장소가 낭비될 뿐아니라 공백값을 제거하기 위해 항상 TRIM()을 사용해야 한다.
3. 가변길이일지라도 모든 칼럼을 VARCHAR2(4000)을 사용하면 잘못되면 터무니없는 RAM이 할당될 수 있으며 또한 사용자가 실수이든 의도적이든 4000바이트 값을 다 입력해 버리는 경우 저장소의 낭비가 올 수 있다.