Oracle MODEL 정리 3
Updated:
1. MODEL 기본개념
이번 칼럼도 오라클 MODEL정리 1, 2에 이어서 MODEL 사용법을 계속 알아봅니다. 지난번 칼럼에서는 기본적인 문법, 멀티셀, 싱글셀 개념과 Sequential Order에 대해 알아보았다. 이번 칼럼에서도 여러가지 그룹함수를 이용해서 MODEL절에 대해 더 알아보도록 하겠습니다.
가. 예제 테이블
에제 테이블은 EMP테이블을 가지고 설명합니다. 앞 칼럼에 생성 스크립트와 테이블이 설명되어 있으니 참고하기 바랍니다. EMP테이블을 이용하여 우리가 기존의 ROLLUP방식과 UNION ALL등을 사용하여 문제를 해결했는데 MODEL을 사용하여 똑같은 조회값을 추출할수 있도록 비교 설명하도록 하겠습니다.
나. MODEL예제 3 Formula 권한 부여
EMP 테이블에서 부서별 합계를 구해봅니다. 그러면 아래의 가항에 해당하는 MODEL절이 나올것입니다. 이상태에서 10번 부서만 UPDTAE 권한만 부여하여 CELL이 생성
안되게 할수 있습니다. 이렇게 하는 이유는 불필요한 CELL이 만들어 지는것을 사전에 방지하기 위함입니다.
RULES을 이루고 있는 각 Formula(계산식)마다 이런 권한을 부여할수 있고, 또한 RULES 차원에서 UPDATE, UPSERT(UPDATE + INSERT), UPSERT ALL 을 부여하여 Formula들의 권한을 제어할수 있습니다. 12c DB에서 테스트해본결과 RULES 차원에서 UPDATE를 기술한경우 각부서별 합계 및 총합계 셀이 전부 만들어지지 않으며 UPSERT, UPSERT ALL을 RULES차원에서 기술해주면 각 Formula들이 CELL에 대한 UPSERT 권한을 가진다. 디폴트는 UPSERT ALL입니다.
가. UPSERT ALL SQL
SELECT DEPTNO, ENAME, SAL
FROM EMP
MODEL
--PARTITION BY ()
DIMENSION BY (DEPTNO,ENAME)
MEASURES (SAL)
RULES --update, upsert, upsert all
(update SAL[NULL, '10번합계'] = SUM(SAL)[DEPTNO=10,ANY],
SAL[NULL, '20번합계'] = SUM(SAL)[DEPTNO=20,ANY],
SAL[NULL, '30번합계'] = SUM(SAL)[DEPTNO=30,ANY],
SAL[NULL, '총합계'] = SUM(SAL)[DEPTNO IN (10,20,30),ANY]
)
ORDER BY 1, 2, 3 ;
나. UPDATE로 권한제어 SQL
SELECT DEPTNO, ENAME, SAL
FROM EMP
MODEL
--PARTITION BY ()
DIMENSION BY (DEPTNO,ENAME)
MEASURES (SAL)
RULES --update, upsert, upsert all
(update SAL[NULL, '10번합계'] = SUM(SAL)[DEPTNO=10,ANY],
SAL[NULL, '20번합계'] = SUM(SAL)[DEPTNO=20,ANY],
SAL[NULL, '30번합계'] = SUM(SAL)[DEPTNO=30,ANY],
SAL[NULL, '총합계'] = SUM(SAL)[DEPTNO IN (10,20,30),ANY]
)
ORDER BY 1, 2, 3 ;
| DEPTNO | ENAME | JOB | SAL |
|---|---|---|---|
| 10 | CLARK | MANAGER | 2450 |
| 10 | KING | PRESIDENT | 5000 |
| 10 | MILLER | CLERK | 1300 |
| 20 | ADAMS | CLERK | 1100 |
| 20 | FORD | ANALYST | 3000 |
| 20 | JONES | MANAGER | 2975 |
| 20 | SCOTT | ANALYST | 1100 |
| 20 | SMITH | CLERK | 800 |
| 30 | ALLEN | SALESMAN | 1600 |
| 30 | BLAKE | MANAGER | 2850 |
| 30 | JAMES | CLERK | 950 |
| 30 | MARTIN | SALESMAN | 1250 |
| 30 | TURNER | SALESMAN | 1500 |
| 30 | WARD | SALESMAN | 1250 |
| 10번합계 | 8750 | ||
| 20번합계 | 8975 | ||
| 30번합계 | 9400 | ||
| 총합계 | 27125 |
다. ORA-32638 에러 처리
MODEL정리 2편에서 UNIQUE SINGLE REFERENCE 이라는 옵션을 이야기하였습니다. 이번장에서 좀 더 확실하게 이야기해봅니다. 2편에서 PARTITION BY와 DIMENSION BY 항목에 들어가는 칼럼을 전부 더했을때 각 레코드들이 UNIQUE하게 식별되어야 합니다. 아래의 결과값을 보면 20번부서 1100값과 30번부서 1250 값이 서로 중복되어 있습니다. 이때 UNIQUE SINGLE REFERENCE 옵션을 넣지않으면 아래의 에러를 만나게 됩니다.
ORA-32638 MODEL차원의 주소지정이 고유하지않음
업무적으로 레코드가 UNIQUE 하지 않은 경우라도 MODEL을 사용할려고 한다면, 이 에러때문에 SQL을 사용할수 없으면 안되니 UNIQUE SINGLE REFERENCE 옵션을 사용하여 MODEL이 디폴트로 각 레코드간의 UNIQUE CHECK을 하는것을 막아서 MODEL을 사용할수 있게 합니다. Defalut 값인 UNIQUE DIMENSION 을 사용하면 전체 데이터에 대하여 UNIQUE CHECK 를 합니다. UNIQUE SINGLE REFERENCE 를 사용하면 RULES 절에서 참조되는 ROW에 대해서만 UNIQUE CHECK 를 하지 않습니다.
SELECT DEPTNO, SAL, fsales
FROM EMP
MODEL --UNIQUE SINGLE REFERENCE
DIMENSION BY (DEPTNO)
MEASURES (SAL, sal fsales)
RULES ( fsales[any]= 20 )
ORDER BY 1,2,3;
| DEPTNO | SAL | FSALES |
|---|---|---|
| 10 | 1300 | 20 |
| 10 | 2450 | 20 |
| 10 | 5000 | 20 |
| 20 | 800 | 20 |
| 20 | 1100 | 20 |
| 20 | 1100 | 20 |
| 20 | 2975 | 20 |
| 20 | 3000 | 20 |
| 30 | 950 | 20 |
| 30 | 1250 | 20 |
| 30 | 1250 | 20 |
| 30 | 1500 | 20 |
| 30 | 1600 | 20 |
| 30 | 2850 | 20 |
라. MODEL예제 4 CASE IS PRESENT
이번에는 RULES에 CASE와 IS PRESENT를 사용하여 처리 로직을 구현해봅니다. PARTITION BY는 생략되어 있고 DIMENSION BY 에 DEPTNO, JOB이 있습니다. 중복된값이 존재하므로 UNIQUE SINGLE REFERENCE 옵션을 주어 UNIQUE CHECK을 하지 않도록 합니다.
RULES에는 FSALES(ANY, ANY) 가 왼쪽항에 정의되어 있는데 기존 테이블에 없는 CELL이라서 필요한 건수만큼 FSALES 칼럼을 만들어 넣는다. 결과값을 보면 FSALES 칼럼이 만들어진것을 확인할수 있습니다. 우측항을 보면
CASE WHEN fsales[10,'ER'] IS PRESENT
THEN fsales[10,'CLERK']
ELSE 0
END <br/>
위의 걔산식의 문장을 분석해보면, fsales[10,’ER’] CELL이 있으면 fsales[10,’CLERK’] CELL의 값을 가져오고, 없으면 0 으로 채우라는 이야기입니다. fsales[10,’ER’] CELL이 없으므로 결과값 1처럼 FSALES가 전부 0 으로 처리되어 나온다. 정확한 CELL값인 fsales[10,’MANAGER’] 을 넣게되면 결과값 2번처럼 FSALES가 1300으로 나오는것을 볼수있습니다.
위의 계산식의 문장을 분석해보면, fsales[10,’ER’] CELL이 있으면 fsales[10,’CLERK’] CELL의 값을 가져오고, 없으면 0 으로 채우라는 이야기입니다. fsales[10,’ER’] CELL이 없으므로 결과값 1처럼 FSALES가 전부 0 으로 처리되어 나온다. 정확한 CELL값인 fsales[10,’MANAGER’] 을 넣게되면 결과값 2번처럼 FSALES가 1300으로 나오는것을 볼수있습니다.
가. SQL 1
SELECT DEPTNO, JOB, sal, fsales
FROM EMP
MODEL UNIQUE SINGLE REFERENCE
DIMENSION BY (DEPTNO, JOB)
MEASURES (sal, sal as fsales)
RULES
( fsales[any,any] = CASE WHEN fsales[10,'ER'] IS PRESENT
THEN fsales[10,'CLERK']
ELSE 0
END )
ORDER BY 1,2,3;
나. SQL 2
SELECT DEPTNO, JOB, sal, fsales
FROM EMP
MODEL UNIQUE SINGLE REFERENCE
DIMENSION BY (DEPTNO, JOB)
MEASURES (sal, sal as fsales)
RULES
( fsales[any,any] = CASE WHEN fsales[10,'MANAGER'] IS PRESENT
THEN fsales[10,'CLERK']
ELSE 0
END )
ORDER BY 1,2,3;
가. 1번 SQL 결과값
| DEPTNO | JOB | SAL | FSALES |
|---|---|---|---|
| 10 | CLERK | 1300 | 0 |
| 10 | MANAGER | 2450 | 0 |
| 10 | PRESIDENT | 5000 | 0 |
| 20 | ANALYST | 1100 | 0 |
| 20 | ANALYST | 3000 | 0 |
| 20 | CLERK | 800 | 0 |
| 20 | CLERK | 1100 | 0 |
| 20 | MANAGER | 2975 | 0 |
| 30 | CLERK | 950 | 0 |
| 30 | MANAGER | 2850 | 0 |
| 30 | SALESMAN | 1250 | 0 |
| 30 | SALESMAN | 1250 | 0 |
| 30 | SALESMAN | 1500 | 0 |
| 30 | SALESMAN | 1600 | 0 |
나. 2번 SQL결과값
| DEPTNO | JOB | SAL | FSALES |
|---|---|---|---|
| 10 | CLERK | 1300 | 1300 |
| 10 | MANAGER | 2450 | 1300 |
| 10 | PRESIDENT | 5000 | 1300 |
| 20 | ANALYST | 1100 | 1300 |
| 20 | ANALYST | 3000 | 1300 |
| 20 | CLERK | 800 | 1300 |
| 20 | CLERK | 1100 | 1300 |
| 20 | MANAGER | 2975 | 1300 |
| 30 | CLERK | 950 | 1300 |
| 30 | MANAGER | 2850 | 1300 |
| 30 | SALESMAN | 1250 | 1300 |
| 30 | SALESMAN | 1250 | 1300 |
| 30 | SALESMAN | 1500 | 1300 |
| 30 | SALESMAN | 1600 | 1300 |
2. 결언
이번 칼럼도 MODEL절에 대한 기본 사용법을 알아보았다. 역시 공대출신의 어쩔수없는 빈약한 글솜씨의 한계를 느끼게 되는 칼럼인것 같습니다. 오늘 UNIQUE SINGLE REFERENCE 옵션을 사용하지 않은경우 만나게되는 에러에 대해 이야기했는데 이 에러를 왜 먼저 이야기 하냐면 개발자들이 이 에러때문에 MODEL공부를 포기하는것을 좀 봤기 때문입니다. 그리고 가능한 쉽게 설명할려고 하다보니 좀 지루해지는 느낌이 듭니다.
이번 칼럼도 MODEL절에 대한 기본 사용법을 알아보았습니다. 역시 공대출신의 어쩔수없는 빈약한 글솜씨의 한계를 느끼게 되는 칼럼인것 같습니다. 오늘 UNIQUE SINGLE REFERENCE 옵션을 사용하지 않은경우 만나게되는 에러에 대해 이야기했는데 이 에러를 왜 먼저 이야기 하냐면 개발자들이 이 에러때문에 MODEL공부를 포기하는것을 봤기 때문입니다. 이미 알고 계신분들의 이해를 바랍니다.