Oracle MODEL 정리 5

1. MODEL 기본개념

이번 칼럼도 오라클 MODEL정리 1, 2, 3, 4에 이어서 MODEL 사용법을 계속 알아봅니다. 지난번 칼럼에서는 기본적인 문법인 PRESENTV, PRESENTNNV, GREATEST에 대해 알아봤습니다. 이번칼럼에는 CV펑션에 대해 알아봅니다. 이 펑션은 정말 좋은 펑션입니다. 사용법도 많고 기능도 강력합니다. 마치 PLSQL에서 Corsur를 선언하고 루핑돌리면서 쉽게 처리하는 방법을 원쿼리 한방의 SQL로 가지고온 느낌이라고 할까? 그만큼 이해하면 쉽게 강력하게 사용할수 있습니다.

가. 예제 테이블

에제 테이블은 EMP테이블을 가지고 설명합니다. 앞 칼럼에 생성 스크립트와 테이블이 설명되어 있으니 참고하기 바랍니다. EMP테이블을 이용하여 우리가 기존의 ROLLUP방식과 UNION ALL등을 사용하여 문제를 해결했는데 MODEL을 사용하여 똑같은 조회값을 추출할수 있도록 비교 설명하도록 하겠습니다.

나. CV 펑션 기본개념

CV() 함수는 포뮬러(계산식)의 좌측 항에 정의된 Multi-Cell Reference를 우측 항으로 복사 하는 기능을 제공하는 매우 강력한 툴입니다. CV() 함수를 이용하여 매우 간결하면서도 유연한 Multi-Cell 포뮬러(계산식)를 구현할 수 있습니다. CV() 함수는 SQL join과 유사한 성격을 가지지만 훨씬 간결하고 이해하기 쉽다는 부가적인 장점을 갖고있습니다. 이것이 매뉴얼에 나온 정의입니다.

쉽게 다시 이야기하면 계산식의 = 을 중심으로 왼쪽에 있는 멀티CELL들의 값을 우측항의 계산을 위해 한개씩 복사하여 주는 기능이 있어 쉽게 코드를 작성 및 처리 가능하다는 이야기입니다. 단 CV펑션을 Single cell 표기만 가능하므로 fsales[cv(), cv(), ANY] 와 같이 ANY 와일드카드가 들어가는 표기는 아래와 같이 에러를 만나게 됩니다.

**ORA-32622 잘못된 다중셀참조

PLSQL에서 변수값을 하나 선언해놓고 루핑돌리면서 그 값을 참조한다고 생각하면 비슷한 개념이 된다.

실제 예제를 만들보자. EMP 테이블에서 SAL값에다 2배를 더한값을 FSALES 칼럼을 만들어서 넣어라.

아래의 SQL에서 fsales[ANY,ANY] = sal[cv(), cv()] 를 보게되면 CV함수 2개가 선언되어 있고 좌측항의 FSALES는 테이블에 없는 칼럼이므로 MODEL에서 새로 FSALES 칼럼을 만듭니다. 대상은 ANY, ANY이므로 DIMENSION BY 에 정의된 ENAME = ANY, JOB = ANY 의 뜻이므로 대상 CELL 전부를 CV(), CV()으로 1건씩 루핑형 처리처럼 COPY 되게 됩니다. 우측항에 SAL칼럼이 지정되어 있으므로 1건씩 ENAME, JOB이 복사되며 그때의 SAL값들에 곱하기 2가 되면서 FSALES의 해당SQL에 값을 INSERT 하게됩니다.

SELECT  DEPTNO, ENAME, JOB, sal, fsales
FROM EMP
MODEL
PARTITION BY (DEPTNO)
DIMENSION BY (ENAME,JOB) 
MEASURES (SAL, sal fsales)
RULES
( fsales[ANY,ANY] = sal[cv(), cv()] * 2 )
ORDER BY 1,2,3;

DEPTNO	ENAME	JOB	SAL	FSALES
10	CLARK	MANAGER	2450	4900
10	KING	PRESIDENT	5000	10000
10	MILLER	CLERK	1300	2600
20	ADAMS	CLERK	1100	2200
20	FORD	ANALYST	3000	6000
20	JONES	MANAGER	2975	5950
20	SCOTT	ANALYST	1100	2200
20	SMITH	CLERK	800	1600
30	ALLEN	SALESMAN	1600	3200
30	BLAKE	MANAGER	2850	5700
30	JAMES	CLERK	950	1900
30	MARTIN	SALESMAN	1250	2500
30	TURNER	SALESMAN	1500	3000
30	WARD	SALESMAN	1250	2500

다. CV 펑션을 더쉽게

위의 설명을 이해할수 있는가? 다시 더 쉽게 설명해보겠습니다.

EMP 테이블의 10번 부서에서 SAL값에다 2배 를 더한값을 FSALES 칼럼을 만들어서 넣어라.

fsales[10,ANY,ANY] CELL은 10부서의 ENAME, JOB이 ANY인 CELL들을 대상으로 한다. 10번부서 전부인 셈이다. 이 CELL들이 우측항으로 복사되어 나번 SQL처럼 하나씩 하나씩 COPY되면서 2배로 곱하기 되어 FASLES 칼럼에 INSERT 하게 된다. 좀더 이해가 되는가? 더이상은 쉽게 설명을 못하겠다. 나번 SQL을 유심히 보시기 바랍니다.

가. SQL
SELECT DEPTNO, ENAME, JOB, sal, fsales 
FROM EMP 
MODEL 
DIMENSION BY (DEPTNO ,ENAME,JOB) 
MEASURES (SAL, 0 fsales) 
RULES 
( fsales[10,ANY,ANY] = sal[cv(), cv(), cv()]*2  
) 
ORDER BY 1,2,3;    

나. SQL
1) fsales[10,‘CLARK’, ‘MANAGER’]  = sal[10,‘CLARK’, ‘MANAGER’] * 2 
2) fsales[10,‘KING’, ‘PRESIDENT’] = sal[10,‘KING’, ‘PRESIDENT’] * 2 
3) fsales[10, ‘MILLER’, ‘CLERK’]  = sal[10, ‘MILLER’, ‘CLERK’] * 2 

DEPTNO	ENAME	JOB	SAL	FSALES
10	CLARK	MANAGER	2450	4900
10	KING	PRESIDENT	5000	10000
10	MILLER	CLERK	1300	2600
20	ADAMS	CLERK	1100	0
20	FORD	ANALYST	3000	0
20	JONES	MANAGER	2975	0
20	SCOTT	ANALYST	1100	0
20	SMITH	CLERK	800	0
30	ALLEN	SALESMAN	1600	0
30	BLAKE	MANAGER	2850	0
30	JAMES	CLERK	950	0
30	MARTIN	SALESMAN	1250	0
30	TURNER	SALESMAN	1500	0
30	WARD	SALESMAN	1250	0

정리하자면 fsales[10,ANY,ANY] = sal[cv(), cv(), cv()]*2

CV펑션은 3단계로 작동됩니다.

• CV펑션을 사용 하였을경우 = 의 왼쪽항에 정의된 범위의 값들이 한건씩 오른쪽 CV펑션으로 카피된다.
  
• = 의 오른쪽항에 한건씩 카피된 값들과 sal 칼럼을 더해 최종값들이 결정된다.
  
• 최종타겟값에 2를 곱해서 FSALES 셀에 값을 넣어준다.
  

라. CV 펑션과 ORDER BY

포뮬러가 2개이고(fslaes값을 대상으로 함) JOB이 CLERK인 팀원들은 4배가 곱해지고 A로 시작하거나 SALESMAN인 경우 2배가 곱해지도록 MODEL절을 작성하라.

대상 CELL들을 fsales[ANY,JOB IN (‘CLERK’,’SALESMAN’)], fsales[ANY,SUBSTR(JOB,1,1) in (‘A’,’C’)] 이렇게 정의했습니다. SQL에 경험있으신분들은 금방 이해 하실거라 생각합니다.

CV펑션의 대상 CELL들의 값들이 여러개 일경우, 위의 경우 IN절과 SUBSTR이 사용되어 여러개의 중복된 대상 값들이 나올수 있습니다. 이럴경우 ORDER BY를 넣어 처리순서를 요구할때가 있다. 이럴때 ORDER BY절을 넣지 않으면, 아래의 에러를 만나게 된다. 중복된 값들이 있고 For, IN, SUBSTR 혹은 Between 문장이 명시될경우 Order by 절이 필요합니다.

가. SQL
SELECT  DEPTNO, ENAME, JOB, SAL, fsales
FROM EMP
MODEL
PARTITION BY (DEPTNO)
DIMENSION BY (ENAME,JOB) 
MEASURES (SAL,sal fsales) 
RULES
(fsales[ANY,JOB IN ('CLERK','SALESMAN')] order by sal DESC  
       = fsales[cv(),cv()] * 2 ,
 fsales[ANY,SUBSTR(JOB,1,1) in ('A','C')] order by sal DESC 
       = fsales[cv(),cv()] * 2 
 --한번 더 2배로 해준다
 )
ORDER BY 1,2,3;

DEPTNO	ENAME	JOB	SAL	FSALES
10	CLARK	MANAGER	2450	2450
10	KING	PRESIDENT	5000	5000
10	MILLER	CLERK	1300	5200
20	ADAMS	CLERK	1100	4400
20	FORD	ANALYST	3000	6000
20	JONES	MANAGER	2975	2975
20	SCOTT	ANALYST	1100	2200
20	SMITH	CLERK	800	3200
30	ALLEN	SALESMAN	1600	3200
30	BLAKE	MANAGER	2850	2850
30	JAMES	CLERK	950	3800
30	MARTIN	SALESMAN	1250	2500
30	TURNER	SALESMAN	1500	3000
30	WARD	SALESMAN	1250	2500

마. IGNORE NAV & Keep NAV

만일 어떤 CELL을 표기했는데 잘못 표기하는 경우가 있을수있다. 이 CELL값을 계산에 포함하게 되면 NULL값을 계산하는것과 동일한 개념이 됩니다.

그래서 우리는 CELL값이 없다면 어떻게 처리하라는것을 계산식에 알려줘야 합니다. IGNORE NAV는 존재하지않거나 값이 없는 필드를 0으로 간주하여 처리하라는 것이고 Keep NAV는 NULL로 인식하여 처리하라는 것이다. 두개의 처리방식은 분명 차이가 있습니다.

SELECT  DEPTNO, ENAME, JOB, SAL, fsales
FROM EMP
MODEL 
DIMENSION BY (DEPTNO, ENAME, JOB) 
MEASURES (SAL,sal fsales)  
RULES
( fsales[ANY,ANY, JOB IN ('CLERK', 'SALESMAN')] 
   order by ENAME,JOB DESC  
   = fsales[cv(), cv(),cv()] * 2 + sal[10,'ING','PRESIDENT']  
 )
ORDER BY 1,2,3;

DEPTNO	ENAME	JOB	SAL	FSALES
10	CLARK	MANAGER	2450	2450
10	KING	PRESIDENT	5000	5000
10	MILLER	CLERK	1300
20	ADAMS	CLERK	1100
20	FORD	ANALYST	3000	3000
20	JONES	MANAGER	2975	2975
20	SCOTT	ANALYST	1100	1100
20	SMITH	CLERK	800
30	ALLEN	SALESMAN	1600
30	BLAKE	MANAGER	2850	2850
30	JAMES	CLERK	950
30	MARTIN	SALESMAN	1250
30	TURNER	SALESMAN	1500
30	WARD	SALESMAN	1250

2. 결언

이번 칼럼은 CV펑션을 설명하였습니다. CV펑션이 매뉴얼에 어렵게 되어있어 처음 보는 분들은 잘 이해하기 어렵습니다. CV펑션을 잘 활용하면 우리가 PLSQL로 복잡한 계산식들을 정의하여 루핑형SQL 처리를 하는것을 한번에 해결해줄수 있습니다. ANSI SQL 99의 함수들이나 이 MODEL절의 목적은 어렵고 난해한 처리로직을 한방의 SQL을 사용하여 빠르고 완벽하게 처리하는데 목적이 있습니다.

요즘 흔히들 사용하는 방식인 WAS 단으로 데이타를 가져가서 처리하는 방식은 가장 최악의 처리방식이고,성능적으로도 좋지않으며, Consistent/Current Mode와 DBMS의 Isolation level 차이로 인해 Wrong Results가 나타나게 됩니다.

데이타를 WAS(콘테이너, 도커, APP서버등)로 가져가서 처리하는 동안 테이블의 값들이 변경되면 당연히 가져간 값들은 Wrong Results가 됩니다. 데이타를 가져 가는동안 해당 테이블에 LOCK을 걸어야 하는데, 이것을 안하면 Wrong Results 발생, LOCK을 걸게 되면 동시성 저하로 이어져서 심각한 문제가 발생합니다. 이 문제는 향후 성능관리 방법론을 연재할때 이야기 하도록 하겠습니다.