Oracle MODEL 정리 1

1. MODEL 기본개념

MODEL은 오라클 DBMS에서만 사용할수 있는 전용 Analytic Function입니다. 이번 칼럼부터는 몇회에 걸쳐 MODEL절의 기본 사용법에 대해 알아보도록 하겠습니다. 이 함수는 오라클 DBMS에서 사용할수 있으며 십년전에 탄생한 SQL 구문입니다. 필자의 기억에 의하면 9i버젼에 첫 발표되어 실제로는 10g DBMS부터 본격적으로 사용이 되기 시작했습니다.

10g는 2000년대 중반에 발표된 DBMS가 아닌가? 현재도 물론 사용하고 있는곳이 있긴하지만 많이는 사용되지 않는 버젼입니다. 지금은 12c가 대세인 버젼인데 10여년동안 얼마나 많이 이 함수가 발전하였겠는가?

각설하고 MODEL은 기존의 SQL문법과 다르지만 복잡한 PL/SQL 문장을 한방의 원쿼리로 작성할수 있도록 하는 기능을 가지고 있으며, 복잡한 계산을 스프레드쉬트 기능으로 많이 해결하는데, 이 MODEL절이 바로 그런 역할을 대신 해줄수 있는 함수입니다. 제조/통신/공공등의 산업군에서 복잡한 배치처리는 거의 PL/SQL문장을 사용하여 절차적으로 처리하다 보니 많은 성능상의 문제를 발생시키고 있는데 이것을 해결 해줄수 있습니다는 이야기입니다.

이 MODEL은 Forecasting sales, 기상예측, 시장점유율 예측, Simultaneous equations(연립방정식) 풀기, 시계열(time series) 분석 등에 사용할수 있습니다. 본 칼럼에서는 MODEL을 처음 접해보는 개발자들을 위해 기본문법 위주로 설명할려고 합니다. 대부분의 SQL책자들이 MODEL절 사용설명이 빈약하고 오라클 매뉴얼도 설명이 충분하지 않아, 이런 단점들을 보충해서 쉽게 설명하도록 노력하겠습니다.

가. 예제 테이블

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

나. 기본 사용법

MODEL절 이란 Old data 와 각종 Factor를 이용하여 예측 데이터를 생성하는 것입니다. Model절은 SELECT 문에 포함된 모든 구문(ORDER BY 제외)이 실행된 이후에 마지막으로 실행됩니다. 즉 정렬이 제일 뒤에 일어나는데 바로 그 전단계에 수행되는것입니다. 기본적인 SQL 실행순서는 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순입니다. 이제 MODEL을 ORDER BY 앞에 넣어 기억해야겠습니다.

기본 문법을 살펴보면

SELECT...FROM...WHERE....

MODEL [main] [reference models]
      [RETURN UPDATED ROWS], [UNIQUE SINGLE REFERENCE ]
PARTITION BY (<cols>)
DIMENSION BY (<cols>)
MEASURES (<cols>) [IGNORE NAV] | [KEEP NAV]
RULES  [UPSERT], [UPSERT ALL], [UPDATE],
       [AUTOMATIC ORDER | SEQUENTIAL ORDER]
( [UPSERT | UPDATE]
  [ITERATE (n) [UNTIL <condition>] ]
)
ORDER BY 1, 2, 3 ;

각 구간이 의미하는것을 살펴보면 아래와 같다.

• PARTITION BY : 측정값을 보고자 할때 기준이 되는 컬럼.
  
• DIMENSION BY : RULES 에서 [ ] 표현식의 기준이 되는 컬럼.
  
• MEASURES : 예측하고자 하는 값(주로 금액이나 수치)
  
• RULES : MEASURES에 정의된 컬럼값을 Update 혹은 Insert 하는 Rule
  

다. MODEL 예제 1

MODEL절로 1차원 ROLLUP을 한번 구현해봤다. FROM절 다음에 MODEL을 선언후, 계산식에 들어가는 칼럼들이 선언되는 DIMENSION BY와 에측하고자 하는값이 선언되는 MEASURES, 그리고 계산식이 들어있는 RULES을 선언하였습니다.

SELECT  DEPTNO, ENAME, JOB, SAL
FROM EMP
MODEL 
DIMENSION BY (DEPTNO ,ENAME,JOB)   
MEASURES (SAL)
RULES  
( SAL[NULL,NULL,'합계'] = SUM(SAL)[ANY, ANY,ANY]  )
ORDER BY 1, 2;

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
		합계	27125

계산식중에서 SAL[NULL,NULL,’합계’] 같은 형식은 우리가 흔히 이야기하는 좌표형식입니다. 좌표형식이란 경도위도와 비슷한 개념으로 SAL[10, CLARK, MANAGER] 좌표는 2450을 가르키는 좌표를 말합니다. SAL칼럼의 10, CLARK, MANAGER 레코드와 만나는 지점. 즉 2450값이 들어있는 CELL을 이야기 하는 것입니다. 다시 예를 들면 SAL[10 , ‘CLARK’, ‘MANAGER’]은 DIMENSION BY에 정의된 (DEPTNO ,ENAME,JOB) 와 결합하여 아래와 같이 해석됩니다. 이해가 되는가? 이 표기법은 대소문자 구분, 속성 TYPE 구분, DIMENTION 개수와 칼럼 개수 일치해야하는 조건이 있습니다.

SAL[10 , ‘CLARK’, ‘MANAGER’] = SAL[DEPTNO=10, ENAME=‘CLARK’,JOB=‘MANAGER’]

위의 예는 SQL문장의 일반적인 WHERE절과 동일하다. 이 셀이 가르키는 값은 2450입니다. 가만히 보면 이 표기법은 단일 CELL을 가르키고 있습니다. 이를 Positional Cell Reference(Single Cell Reference) 이라 어렵게 부르며 오직 Single Cell 에 대한 접근하며, Cell 생성이 가능하다. 어떤때 생성이 되냐면 SAL[NULL,NULL,’합계’] 과 값이 테이블에 저 CELL이 없을경우 그때 추출되는 결과값에 ‘합계’라는 CELL값이 생성됩니다.

반대로 다수의 CELL에 접근 가능한것을 Symbolic Cell Reference(Multi Cell Reference)이라 부르며 다수의 셀에 대한 접근 및 업데이트가 가능하나, Cell 생성이 안되는 특징이 있습니다. SUM(SAL)[ANY, ANY,ANY] 이 대표적인 예로, SAL를 SUM하라는 이야기인데 대상 CELL들이 와일드카드인 ANY, ANY, ANY로 선언되어 있습니다. 즉 좌표가 SAL와 만나고 모든 DEPTNO ,ENAME,JOB 레코드와 만나는 CELL 은 어느것일까? 결국 14개의 SAL값을 SUM 하라는것입니다.

여기서는 SUM을 사용한 예를 들었는데 오라클에서 제공하는 대부분의 함수를 사용 가능하다. 오라클 매뉴얼을 보면 OLAP Aggregate(inverse distribution function, hypothetical rank, distribution function 등), Statistical aggregate, 사용자 정의 Aggregate 함수 등을 포함하는 모든 종류의 Aggregate 함수를 사용할 수 있습니다고 나옵니다.

라. 와일드카드, 에러처리 설명

위에서 이야기한 Wild Card를 설명합니다. 와일드 카드는 보통 * 를 많이 사용하는데 MODEL절에서는 ANY, IS ANY 를 사용하여 표시합니다. 10g버젼에서의 ANY는 = 의 양쪽에 전부 사용가능하고 IS ANY는 = 의 왼쪽에만 사용가능(10g)하다. 좀 어려운 이야기지만, ANY, IS ANY를 왼쪽에 사용하는경우 멀티셀 처리이므로 INSERT 방지효과도 있어 자주 사용합니다.

많이 나타는 오라클 에러로는

**ORA-01722 수치가 부적합합니다.
SAL[‘합계’,NULL, NULL]=SUM(SAL)[ANY,ANY,ANY]
이 에러는 Number타입에 Char타입과 비교할떄 발생 하는것입니다.

**ORA-00947 값의 수가 충분치 않습니다.
SAL[‘합계’,NULL, NULL]=SUM(SAL)[ANY,ANY]
이 에러는 Dimension By에 정의된 개수와 틀림으로 발생 하는것입니다.

2. 결언

힘든 칼럼인것 같습니다. 사내 오프라닝 강좌로 고급SQL활용(Advanced SQL Part 2)를 강의할때 항상 이 MODEL절을 처음 배우는 개발자들은 경력에 상관없이 힘들게 느끼는것 같았습니다. 강사가 제대로 설명을 못해서 그럴수도 있지만 기본 문법이 기존의 SQL과 좀 틀려 새로운 문법을 많이 익혀야 해서 그런것 같습니다. 그래서 대부분 MODEL절은 처음 접할때 상당히 혼란을 느낍니다. 어렵고 이해하기 힘들다고 이야기합니다. 그래도 잘 생각하면 정말 대단한 기능입니다. 벌써 나온지 10년이 넘은것이지만 모르고 있는 개발자들이 많은것 같다. 다음 칼럼부터 본격적으로 사용예를 알아봅니다.