Mooyoung2020

Mooyoung2020

SQL Tunner

Oracle Join Update 정리 2

Updated:

예제 테이블 생성

샘플 테이블 생성

오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 와 DEPT라는 테이블을 기준으로 테스트해봅니다. DEPT 테이블의 부서별 위치를 가진 테이블이고 EMP 테이블의 부모테이블로 DEPTNO가 PK입니다. EMP는 고용인 테이블로서 DEPT의 자식테이블로 EMPNO가 PK이며, DEPTNO가 FK입니다. 아래의 예제를 가지고 알아봅니다.

1. 조인 업데이트 기본 개념

오라클 조인 업데이트는 2개이상의 테이블을 서로 연결하여 원하는 값으로 타겟 테이블의 값을 변경하는 방법을 이야기합니다. 그런데 사용할때 기본적인 문법이 ANSI SQL의 형태와 다르며, 최고의 성능을 내기 위해서는 원쿼리형태의 업데이트 문장을 만들어 튜닝하여 사용하여야 하며 어떤 BUG나 문제가 잘 발생하는지 이해하여만 좋은 업데이트 문장을 만들수 있습니다. 조인업데이트 관련해서는 UPDATE문장 2가지 방법, MERGE문장, 루핑형 SQL을 사용하여 데이타를 PL/SQL로 처리하는 방법, WAS를 기준으로 루핑형 SQL로 처리한는 방법등 가지 정도의 형태가 있는데 여기에서는 원쿼리형태의 조인 업데이트/MERGE문장과 업데이트 방향, 발생 에러등을 중심으로 설명하겠습니다.

오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 와 DEPT라는 테이블을 기준으로 테스트해봅니다. DEPT 테이블은 부서별 위치를 가진 테이블이고 EMP 테이블의 부모테이블로 DEPTNO가 PK입니다. EMP는 고용인 테이블로서 DEPT의 자식테이블로 EMPNO가 PK이며, DEPTNO가 FK입니다. 아래의 예제를 가지고 알아봅니다.

1. 조인 업데이트 기본 개념

오라클 조인 업데이트는 2개이상의 테이블을 서로 연결하여 원하는 값으로 타겟 테이블의 값을 변경하는 방법을 이야기합니다. 그런데 사용할때 기본적인 문법이 ANSI SQL의 형태와 다르며, 최고의 성능을 내기 위해서는 원쿼리형태의 업데이트 문장을 만들어 튜닝하여 사용하여야 하며 어떤 BUG나 문제가 잘 발생하는지 이해하여만 좋은 업데이트 문장을 만들수 있습니다. 조인업데이트 관련해서는 UPDATE문장 2가지 방법, MERGE문장, 루핑형 SQL을 사용하여 데이타를 PL/SQL로 처리하는 방법, WAS를 기준으로 루핑형 SQL로 처리하는 방법등 4가지 정도의 형태가 있는데 여기에서는 원쿼리형태의 조인 업데이트/MERGE문장과 업데이트 방향, 발생 에러등을 중심으로 설명하겠습니다.

2. 조인 업데이트와 테이블 관계

조인 업데이트 문장을 사용할때에는 두개 테이블의 관계가 매우 중요하다. 예를 들어 1 대 M의 관계를 가진 2개의 테이블에서 자식테이블인 EMP테이블의 값을 부모테이블인 DEPT테이블로 업데이트를 하면 오라클 에러가 발생합니다. 왜 발생하는가? 쉽게 이야기하면 여러 종류의 데이타를 가진 EMP가 1개의 단일 데이타를 가진 DEPT 값을 업데이트합니다면 사라지는 EMP 데이타가 다수 존재하게 됩니다. 상식적으로 용납할수 없는 것입니다. 그러나 1 대 1의 관계를 가진 부모-자식테이블이라면 자식테이블의 값으로 부모테이블의 값을 변경처리해도 에러가 발생하지 않는다. 반대로 부모테이블인 DEPT값으로 자식 테이블인 EMP 를 업데이트 하여도 에러는 발생하지 않는다. 이것이 키보존 테이블 개념을 쉽게 설명한 예입니다. 키보존 테이블 개념은 인터넷으로 검색해서 봅니다. 조인 업데이트를 사용할려면 테이블의 관계와 변경하려는 테이블이 부모인지 자식테이블 방향인지가 매우 중요한 셈입니다.이 단순한 개념 하나 가지고 이해를 잘못해 MERGE문장 사용시 오라클 에러가 발생하여 큰 금융시스템 하나를 오픈 못할뻔 한적도 있으니, 이번기회에 잘 이해하시길 바랍니다.

가. 부모-자식 테이블 관계 조인 업데이트 테스트

실제로 아래의 테스트 테이블을 만들고 조인업데이트 문장을 테스트해봅니다. 먼저 첫번쨰 문장으로 테스트를 해보면 정상적으로 작동함을 알수있습니다. 그러나 이때

  1. 부모 테이블의 PK를 생성하지 않고 테스트해보면 11g DBMS 이상 버젼에서는 에러가 발생하며 수행이 되지 않는다. 즉 반드시 PK가 필요하다. 그러나 10g 이하 버젼 DBMS에서는 DEPT_PK가 생성되어 있지 않더라도 /+bypass_ujvc/ 힌트를 사용하면 정상적으로 작동됩니다. 단 오라클(주)에서 이 힌트를 보장하지 않는다.

  2. 아래의 첫번째 문장은 EMP20을 업데이트하는 문장으로 DEPT20에서 하나의 데이타를 EMP20의 여러개의 데이타를 한꺼번에 업데이트하는 문장입니다. 이 문장은 이상없이 작동됩니다. 반대로 EMP20의 여러개의 데이타를 한개의 DEPT20 테이블로 업데이트하게 되면 아래의 ORA-01779 에러가 발생하며 업데이트가 되지 않는다.

ORA-01779 cannot modify a column which maps to a non key-preserved table

좀 복잡하더라도 여러가지 테스트를 하면서 어떤경우 에러가 발생하는지 잘 익혀봅니다.

  1. 두번째 문장은 DEPT_PK의 존재유무와 상관없이 잘 동작합니다. 그러나 WHERE조건이 없으므로 실행계획상 필터처리가 되어 다량의 데이타 처리를 하여야할 경우 수행속도가 많이 저하될것으로 생각됩니다.
--테스트 테이블 생성. EMP와 DEPT는 많이 사용하는 테이블이므로 임시 테이블 생성,

Create table EMP20 as select * from EMP nologgnig;
Create table DEPT20 as select * from DEPT nologgnig;
alter table dept20 add constraint dept_pk20 primary key(deptno );    

1. 첫번째 테스트 문장
UPDATE  --/*+bypass_ujvc*/ 
    (SELECT  B.ename, A.loc
     FROM dept20 A, emp20 B
     WHERE A.deptno = B.deptno) 
SET ename = loc;

2. 2번째 테스트 문장
UPDATE emp20 a
SET a.ename = (SELECT b.loc
                     FROM dept20 b
                     WHERE b.deptno = a.deptno);
select * from emp20;

--ALTER TABLE dept20 drop CONSTRAINT dept_pk20; --pk를 삭제하고 테스트도 해봅니다

나. /+bypass_ujvc/ 힌트설명

/+bypass_ujvc/ 힌트는 Constraint를 피해서 Join Update를 할수 있도록 해주는 힌트입니다.
  Join Update 라 함은 1:1 혹은 1:M 로 묶인 상태에서 1쪽 집합의 조인하는 컬럼이 UK혹은 PK로 설정되어 있어야 합니다. 이런경우 /+bypass_ujvc/ 구문은 없어도 쿼리가 동작합니다.

그러나 대부분의 경우 여러 개의 테이블을 조인하거나 엑셀등의 데이터를 Data import 해서 만들어진 테이블들과 조인을 하는 경우가 많기에 UK 혹은 PK로 설정하고 업데이트를 실행하기가 쉽지 않다. 그때 이 힌트를 사용하여 쿼리를 실행하여야 했으며 11g에서는 없어졌습니다.

다. MERGE문장으로 처리

위의 조인 업데이트문장들을 merge문장으로 변경하여 처리해봅니다. 역시 동일한 개념으로 처리하면 되는데 부모테이블을 자식테이블의 2개이상의 테이타를 사용하여 처리하면 에러가 발생하며, 부모테이블의 데이타를 자식테이블의 데이타를 업데이트하면 정상적으로 작동됩니다 이때 데이타간 관계는 1 대 M입니다. 단 여기서 자식테이블과 부모테이블간 관계를 1 대 1로 만들어 처리하면 문제없이 수행됩니다.

1) 첫번째 문장
MERGE INTO emp20 e 
USING dept20 d 
ON (e.deptno = d.deptno)
WHEN MATCHED THEN UPDATE SET e.ename = d.loc;  
--이상없이 동작함.  Dept_pk 생성유무와 상관없음

2) 두번째 문장
MERGE INTO dept20 e 
USING emp20 d 
ON (e.deptno = d.deptno)
WHEN MATCHED THEN UPDATE SET e.loc = d.ename; 
 -- 1-m관계에서 1쪽을 update 할수없다. ora-30926 에러 발생
 -- ora-30926에러는 1-M관계 업데이트시 1쪽을 업데이트할때 발생함을 알수있습니다

3) 세번째 문장
MERGE INTO dept20 e 
USING 
(SELECT DISTINCT deptno,(select ename from emp20 where rownum=1) ename FROM emp20) d 
 ON (e.deptno = d.deptno)
 WHEN MATCHED THEN UPDATE SET e.loc = d.ename; 
-- 테스트를 하기 위해 만든예제로 ROWNUM=1 을 사용하여 데이타관계를 1-1관계로 만든후 업데이트시 이상없이 동작함.

라. 키보존 테이블 개념

조인된 결과 집합을 통해서도 중복되는 값없이 Unique하게 식별이 가능한 테이블 뷰에 rowid를 제공하는 테이블로 설명됨. User_updatable_columns 뷰를 참고하면 어떤것이 키보본 테이블인지 알수있음. 여기에서는 설명을 하지 않고있으니 인터넷을 참조하기 바람.

여기에서는 부모-자식간 키보존 테이블 개념을 쉽게 설명하기 위해 무리수를 좀 두어 설명하고 있습니다. 유교문화권의 나라에서는 부모가 자식의 교육을 위해 어느정도 체벌을 하는것이 용납됩니다. 그리고 1 대 M 관계에서 자식이 부모를 체벌할수 없다. 그러나 데이타의 관계가 1 대 1 로 평등해지면 자식이 부모를 업데이트 하는게 가능하다. 좀 무리수를 둔 설명이지만 쉽게 업데이트 가능 데이타 관계를 알수있게 해줍니다.

3. 결언

조인 업데이트 개념 및 처리방식은 매우 중요하며 위의 방식들은 오라클에서 사용하는것을 설명하였습니다. 조인업데이트를 사용할때는 얼마나 많은 데이타를 처리하는지가 중요하다. 소량의 데이타(몇만건 이하수준)를 처리할때에는 NL조인 위주의 조인업데이트나 MERGE문장을 사용하여 처리하고 다량의 데이타(몇십만건에서 몇천만건수준)를 처리할때에는 해쉬조인이 가능한 조인업데이트나 MERGE문장으로 처리하면 됩니다.

결론적으로 업무 복잡도에 따라 루핑형 SQL 처리형태 뿐만 아니라, 조인업데이트, MERGE 문장등 원쿼리형태 SQL로 다양하게 처리할수 있도록 능력을 키워 봅니다.

You May Also Enjoy