대용량 데이터베이스 솔루션 2권 #05 UPDATE / Updatable JOIN-VIEW

확장 UPDATE

UPDATE table_name
   SET (col1, col2, ... colN) =                       -- 갱신대상 Column (정의역)
       (SELECT any_columns                             --  갱신값 생성
          FROM other_tables
         WHERE join_condition
           AND sub_query_column = main_query_column)
 WHERE (column, ...) IN (SELECT join_column, ...      -- 갱신대상 선택
                           FROM tables
                          WHERE conditions)

통상적으로 UPDATE 라고 하면 당연히 상수값을 이용한 UPDATE를 생각한다. 하지만 여러 TABLE을 가공하여 한방 UPDATE에 몇천건의 UPDATE가 완료되었습니다. 로 처리하는것이 가능하다. DECLARE CURSOR 보다도 몇천배이상 더 빠르다.

예제를 하나 살펴보자.

위와 같은 관계의 TABLE에서의 UPDATE 예제 SQL문이다.

UPDATE TABLE3 T3
   SET (COL3, FLD3, QTY3) = 
       (SELECT DECODE(COL2, '1', SUBSTR(COL4,1,4)),
               NVL(FLD3,0) + NVL(QTY2,0) * 100 / NVL(QTY4,1),
               DECODE(FLD4, '1', NVL(QTY2,0) * 0.01,
                            '2', NVL(QTY2,0) * 0.05 - NVL(QTY3, 0))
          FROM TABLE2 T2, TBLE4 T4
         WHERE T2.ITEM   = T3.ITEM
           AND T4.ORD_ID = T3.ORD_ID)
 WHERE ITEM IN (SELECT ITEM
                  FROM TABLE2
                 WHERE PROD IN
                       (SELECT PROD
                          FROM TABLE1
                       CONNECT BY PROD = PRIOR MPROD
                       START WITH MPROD = :PROD));

1. TABLE1을 Recursive 전개하여 특정상품의 필요 부품들을 선택하여
2. TABLE2에서 ITEM을 선정
3. 해당 ITEM을 가진 처리대상 ROW 집합을 선택하여
4. 각 ROW마다 차례로 Sub-query에서 참조 TABLE JOIN하여 DATA를 가공하여
5. 정의역 COLUMN에 UPDATE (한 후 다시 3번으로 반복)

SQL의 FAIL 방지법

- SQL ERROR : Parsing Error가 발생하여 수행되지 않음
  SELECT COL1, COL2 FRON TAB1 WHERE COL1 = 'ABC';

- SQL FAIL : SQL은 수행되었으나 결과가 NULL (공집합 : No Data Found)
  SELECT COL1, COL2 FROM TAB1 WHERE 1 = 2;

- SELECT NVL(COL1,'X') FROM TAB1 WHERE 1 = 2;
  => FAIL이 되면 SQL 자체가 NULL이 되므로 SELECT-LIST는 실행되지 않는다. 따라서 NVL는 작동하지 않고 결과는 NULL이다.
      그럼 어떻해야 할까 ? GROUP 함수가 있으면 절대 FAIL은 일어나지 않는다. 따라서 NVL은 작동을 하여 결과는 'X'가 된다.
   SELECT NVL(MIN(COL1),'X') FROM TAB1 WHERE 1 = 2;

NULL 방지대책

1. EXISTS 활용

UPDATE TABLE1 X
   SET COL1 = (SELECT NVL(X,COL1,0) + NVL(Y.FLD1, 0)  -- 이 조건이 FAIL이면 많은 COL1에 NULL이 UPDATE 된다.
                 FROM TABLE2 Y
                WHERE Y.KEY = X.KEY)
 WHERE ...
   AND EXISTS (SELECT ' '             -- TABLE2를 한번 더 읽어야 한다. NULL은 UPDATE 안되겠지만 같은 DATA를
                 FROM TABLE2 Z         -- 2번 읽게 된다. 하지만 궁여지책으로 NULL UPDATE 방지는 가능하다. 
                WHERE Z.KEY = X.KEY)

2. GROUP 함수 활용

UPDATE TABLE1 X
   SET COL1 = (SELECT NVL(X,COL1,0) + NVL(MIN(Y.FLD1), 0) -- 절대 NULL UPDATE가 발생하지 않는다.
                 FROM TABLE2 Y
                WHERE Y.KEY = X.KEY)
 WHERE ...

3 Updatable JOIN-VIEW 활용

Updatable JOIN-VIEW 활용 (수정가능 조인뷰)

JOIN-VIEW 가 뭔지는 다들 알 것이다. Updatable JOIN-VIEW는 말 그대로 UPDATE가 가능한 JOIN-VIEW를 말한다.

CREATE or REPLACE VIEW EMP_DEPT_VIEW AS
  SELECT E.ENUM, E.ENAME, E.GRADE, D.DNAME, D.LOC, D.DEPTNO
    FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

- E 와 D 가 M : 1 JOIN 이다.
- M쪽이 Key Preserved Table (키보존 테이블)이 된다.
- Key Preserved Table은 UPDATE, DELETE가 가능하다.

UPDATE EMP_DEPT_VIEW
   SET GRADE = 'A10'    -- View 상에서 E의 Column은 UPDATE가 가능하나, D의 Column은 UPDATE가 불가능하다.
 WHERE   LOC = 'SEOUL'
   AND GRADE = 'B01';

* 제한사항
- DISTINCT 처리가 포함
- GROUP 함수 사용 : SUM, MIN, MA, AVG, COUNT, STDDEV, VARIANCE, GLB 등 ...
- 집합 처리 : UNION, UINON ALL, INTERSECT, MINUS
- GROUP BY 나 HAVING을 사용
- Recursive 전개 : CONNECT BY ... START WITH 구무
- ROWNUM을 사용
- 여러 개의 VIEW와 JOIN 한 VIEW는 실행시 최종 VIEW 내로 Merge(병합)가 가능해야 함
   (여러 개의 VIEW 중 제한사항을 어긴 VIEW가 하나라도 있으면 안됨)
- JOIN된 TABLE 중에 반드시 Preserve Table 만 수정이 가능

MERGE VIEW

1. VIEW Query가 Access Query로 Merge

CREATE or REPLACE VIEW EMP10 AS
  SELECT ENUM, ENAME, GRAGE, ENTER_DATE, SAL  -- 1. VIEW Query
    FROM EMP
   WHERE DEPTNO = '10';

SELECT ENUM, ENAME                            -- 2. Access Query
  FROM EMP10
 WHERE SAL BETWEEN 1000 AND 3000;

  => 3. VIEW Query가 Access Query 로 Merge
SELECT ENUM, ENAME
  FROM EMP10
 WHERE DEPTNO = '10'
   AND SAL BETWEEN 1000 AND 3000;

2. Access Query가 VIEW Query로 Merge

CREATE or REPLACE VIEW EMP_UNION AS
  SELECT ENUM, ENAME, GRAGE, ENTER_DATE, SAL
    FROM EMP_1
   UNION ALL
  SELECT ENUM, ENAME, GRAGE, ENTER_DATE, SAL
    FROM EMP_2;

SELECT ENUM, ENAME
  FROM EMP_UNION
 WHERE DEPTNO = '20';

=> 3. (VIEW Query 내에 Updatable JOIN-VIEW의 제한사항이 포함되어 있으면)
      Access Query가 VIEW Query로 Merge

SELECT ENUM, ENAME
  FROM EMP_1
 WHERE DEPTNO = '20'
 UNION ALL
SELECT ENUM, ENAME
  FROM EMP_2
 WHERE DEPTNO = '20';

Key-preserved Table (키보존 테이블)

위 그림에서는 T1 과 T2가 1 : M 관계이므로 T2가 Key Preserved Table이 된다.
PK1 과 PK3는 같지 않으며
PK2 는 PK3 AND PK1 으로 Unique하게 식별된다.

TABLE 1	TABLE 2	Key-preserved Table	비고
1	1	T1, T2	한번에 1개의 Table만 UPDATE 가능, DELETE 불가
1	M	T2
M	1	T1
M	M	없음
1(+)	1	T2	무조건 OUTER 쪽은 Key-preserved 자격 상실 (NULL이 될 수 있으니깐)
1(+)	M	T2
M(+)	1	없음

* 예제

CREATE or REPLACE VIEW EMP_DEPT_VIEW1 AS      -- M 쪽인 E가 Outer Join 되지 않았으므로 Key-preserved Table
  SELECT E.ENUM, E.ENAME, E.GRAGE,
         D.DNAME, D.LOC, D.DEPTNO
    FROM EMP E, DEPT D
   WHERE E.DEPTNO = D.DEPTNO(+);

CREATE or REPLACE VIEW EMP_DEPT_VIEW2 AS     -- M 쪽인 E가 Outer Join 되었으므로 Key-preserved Table 자격 상실
  SELECT E.ENUM, E.ENAME, E.GRAGE,
         D.DNAME, D.LOC, D.DEPTNO
    FROM EMP E, DEPT D
   WHERE E.DEPTNO(+) = D.DEPTNO;

CREATE or REPLACE VIEW FAMILY_SAL_VIEW AS     -- 상수조건 부여로 M:M 을 M:1로 만들더라도  SELECT F.ENUM, F.ENAME, F.BIRTHDAY,          --  F는 Key-preserved Table이 될 수 없음
         S.SALARY                              -- Optimizer는 SQL 상의 개수가 아니라
    FROM FAMILY F, SALARY S                    -- System Dictionary 상의 Key를 보고 판단함
   WHERE F.ENUM = S.ENUM
     AND S.SAL_DATE = '201408';

JOIN VIEW의 Updatable 확인

INDEX 와 Updatable JOIN-VIEW의 관계

- Key-preserved Table에 어떤 INDEX를 만드냐에 따라서 다른 Table이 Driving 될 때의 성능을 결정한다.
- 가능한 모든 Table이 선행 Table이 될 수 있도록 INDEX를 구성 하는게 좋다. 하지만 이러긴 어렵다.
- 적절한 역할분담이 이루어지게 INDE를 구성해야 한다.

Updatable JOIN-VIEW의 활용

1 SECURITY 관리

- 의미는 없지만 함수를 사용한 Column은 UPDATE가 불가능하다.

2. 확장 UPDATE에서 활용

UPDATE TAB3 T3
   SET (COL3, FLD3, QTY3) =
       (SELECT DECODE(COL1, '1', COL2, ...),
               NVL(QTY3,0) + NVL(QTY2,0),
               FLD4 * 0.01
          FROM TAB2 T2, TAB1 T1
         WHERE T2.ITEM   = T3.ITEM
           AND T1.ORD_ID = T3.ORD_ID)
 WHERE ITEM IN
       (SELECT ITEM
          FROM TAB2
         WHERE COL2 = :val1
           AND FLD2 LIKE '201407%');            -- TAB2가 2번 Access된다. 이런 경우 한방 SQL로 만드는 방법은 없다.


CREATE or REPLACE UPT_TEST_VIEW as             -- 먼저 3개의 Table로 JOIN-VIEW를 만든 뒤 
  SELECT a.ITEM, a.FLD1, a.COL1, ...
         b.COL2, b.qty2, ...
         c.COL3, c.FLD3, c.QTY3, ...
    FROM TAB1, a, TAB2 b, TAB3 c
   WHERE a.ORD_ID = c.ORD_ID
     AND b.ITEM   = c.ITEM;

UPDATE UPT_TEST_VIEW                             -- VIEW 를 UPDATE하면 TAB2를 한번만 읽고도 가능하다.
   SET COL3 = DECODE(COL1, '1', COL2, ...)       -- (Oracle 7.3 이후부터 가능)
       FLD3 = NVL(QTY3,0) + NVL(QTY2,0),
       QTY3 = FLD4 * 0.01
 WHERE COL2 = :val1
   AND FLD2 LIKE '201407%';

대용량 데이터베이스솔루션 2

작가

이화식

출판

엔코아컨설팅

발매

1999.11.20

평점

리뷰보기