|
원문 출처 : http://doug.burns.tripod.com/decode.html
번역 : 정재익
서론
이 글은 오라클의 SQL 에 구현되어 있는 그렇게 흥분되는 기능은 아닌 함수중의 하나에 대한 것이다. 그렇지 않은가? DECODE() 가 그렇게 특수한 함수가 아니더라도, 이 함수는 프로그래밍시에 SQL 에 많은 강력한 힘을 주는 함수이다. 이 함수는 우리들의 SQL code 에서 자료를 추출한 후에, PL/SQL 이나 3 GL 을 사용하지 않고도, 작지만 유용한 procedural logic 을 제공한다. 수년에 걸쳐서 많은 오라클 버전이 바뀌는 동안 여러가지 버전의 클라이언트 툴을 사용했으나 이것은 바뀌지 않았다. 나는 이 문서에서 이에 대한 기본적인 technique 을 알려 주고자 한다.
나는 더 이상 프로그래머가 아니며, 함수나 수식, 그리고 구문 다이어그램 등은 나를 아주 짜증나게 한다. 그럼 왜 나는 이런 technique 에 흥미를 느끼는가? 모든 DBA 는 일시적으로는 SQL-Performance 자문을 해 줄수 밖에 없다. 3-GL 을 이용해서 재개발 하는 이유도 여기에 있다. DECODE 는 이러한 복잡한 레포트를 충분히 빠른 시간내에 실행시키는 유일한 방법이다.
이 문서는 각각의 SQL 구문에서 DECODE 가 어떻게 여러개의 SQL 구문을 사용하지 않고, 한번만에 자료를 추출하고, 이것을 분해해서, 여러분들의 보고형식에 맞추어 주는가를 설명해 줄 것이다. 많은 경우에 performance 는 선형적으로 비례한다. 이 말은 전체 테이블을 스캔하는 10개의 분리된 SQL 문장 대신에 전체 테이블을 한번만 스캔하는 SQL 구문을 사용한다면, 그 퍼포먼스는 그만큼 좋아 진다는 것이다. 나는 이런 경우를 많이 경험했고, 그 결과는 아주 환상적이며, 내가 어떻게 이러한 technique 를 사용했는가에 대해 놀라곤 한다. 내가 이 문서를 작성할때 한 reviewer 가 "왜 그렇게 자주 사용되지 않는가?" 하고 물었었다. 나는 아직 그 해답을 찾지는 못했다. 그러나 이 글이 그러한 상황들을 좀더 낫게 하는 데 도움이 되길 바란다.
내가 구문 다이어그램을 아주 귀찮아 한다고 말했던가? 그래서 때로는 기술적으로 그렇게 상세한 설명이 없을수도 있다.
정의
먼저 주의해야 할 것은 DECODE() 함수가 오라클 SQL 언어 참고메뉴얼의 Expr section 에 정의되어 있다는 것이다. 여기서 우리는 일차적으로 DECODE() 의 강력함을 느낄수 있는데, 왜냐하면 이 함수를 어떤 표현식 (SELECT, WHERE 또는 ORDER BY 절과같은) 내에서도 사용할 수 있다는 것을 의미한다.
함수형 정의는 다음과 같다:
[color=ORANGE]
decode([i]expr,search1,result1[,search2,result,...][.default][/i])
[/color]
여기서...
expr 은 유효한 모든 표현식을 말한다. 컬럼 이름, 상수, bind 변수, 함수 호출, 또는 산술식 등등을 포함할수 있다. (예를 들면, 12039 'A String', emp.empno, emp.sal*15) 이것은 A<->B 의 비교에서 A 값을 말하며, 우리가 검사해야 할 표현식이다. 그 다음 인자들은 하나 이상 쌍으로 반복된다. (예를 들면 search1/result1, search2/result2 와 같은 식으로)
search[1-n] 는 유효한 모든 표현식으로서, 비교시에 사용할 B 값을 말한다. 만약 search1 과 expr 이 서로 다른 자료형을 갖는다면 오라클은 expr 과 모든 search 표현식들을 search1 과 같은 자료형으로 변환한다. 그러나 이러한 자동적인 형변환에 의존하지 않는 것이 가장 좋은 방법이다. 또 다른 중요한 점은 DECODE expression 에서 null 은 null 과 동일하다는 것이다.
result[1-n] 는 이전의 search 값이 expr 값과 일치할때 함수에 의해서 돌려질 결과값이다. (또는 다르게 말하면, 이 표현식의 값이 되돌려 진다) 오라클은 적당한 결과값을 돌려 주기 위해서 search 표현식을 최소로 evaluation 한다. 예를 들면 만약 server 가 search1 을 evaluation 해서 expr=search1 을 만족하는 값을 발견하게 되면, 함수는 result1 을 돌려 주고 search2, search3, ... 는 절대로 evalueation 하지 않는다. 이 때문에 여러분들은 expr 과 일치하는 search expression 의 순서를 좌측에서 우측으로 순서를 정해서 적어 줄 필요가 있다. 이것은 표현식의 evaluation 을 최소화 시킬 수 있는 방법이다.
default 는 expr 과 일치하는 search 표현식이 없을 경우 되돌려 줄 값이다. 만약 default 값이 명시되지 않고, expr 과 일치되는 search 표현식을 찾지 못했다면 오라클은 NULL 을 돌려 주게 된다.
이제까지 설명은 장황했지만 실제 원칙은 대단히 단순하다. 여러분들이 행해오던 다른 프로그래밍 언어에서의 표현식을 빌리자면 DECODE 는 if ... then ... elseif ... 형식의 문장의 변형에 지나지 않는다.
if (expr == search1)
return(result1);
elseif (expr == search2)
return(result2);
…
elseif (expr == searchn)
return(resultn);
else
return(default);
C 언어에서의 표현식으로 하자면 다음과 같다.
switch ( expr ) {
case search1 :
return(result1);
…
case searchn :
return(resultn);
default :
return(default);
}
이제 우리들의 첫번째 예제로 돌아가 보자. 아래 예제들은 모두 표준적인 SCOTT.EMP 와 SCOTT.DEPT 테이블 내에 포함된 내용이며, Personal Oracle 7.2, Personal Oracle 8.0.3 그리고 Oracle Lite 3.5 에서 테스트 된 것이다. 모든 실행 plan 은 SQL*Plus Autotrace facility 를 이용하여 생성된 것이며, rull-based optimizer 를 이용한 퍼스널 오라클 8.0.3 에서 테스트 되었다. 이것은 실제 데이터베이스와 예제 테이블의 적은 데이터 량에서 모두 비슷한 실행 plan 을 제공해 주기 때문에 데모 목적으로는 적절하다. 이 말은 예제 테이블의 통계를 볼 필요 없이 어떤 데이터베이스에서라도 유사한 결과를 보여 줄 것이라는 것을 의미한다. (즉, 이것을 집에서 실행해 보라. 만약 SCOTT 예제 테이블이 있다면 결과에 별다른 차이가 없을 것이다. Rule-based optimizer 를 사용하던지 cost 를 사용하던지간에 원칙은 동일하다.
다음은 report 형식을 좋도록 하기 위해서 DECODE 를 사용하는 가장 전형직인 예이다.
[i]example1[/i]
SELECT ename Name,
DECODE(deptno, 10, 'Accounting',
20, 'Research',
30, 'Sales',
40, 'Operations',
'Unknown') Department
FROM emp;
NAME DEPARTMENT
---------- ----------
KING Accounting
BLAKE Sales
CLARK Accounting
JONES Research
MARTIN Sales
ALLEN Sales
TURNER Sales
JAMES Sales
WARD Sales
FORD Research
SMITH Research
SCOTT Research
ADAMS Research
MILLER Accounting
14 rows selected.
이 문장은 emp 테이블의 deptno 컬럼을 검사해서 만약 값이 = 10 이라면 'Accounting' 을, = 20 이라면 'Research'를 ... 출력하는 것이다. default 값으로 'Unknown' 이 명시되어 있다는 것을 주의해서 보라. 이것은 만약 deptno 값이 10, 20, 30 또는 40 중에 포함되어 있지 않다면 출력 될 것이다.
이것은 DECODE 함수를 실제로 사용하는 간단한 예제일 뿐만 아니라 실제 프로그래밍에서도 deptno 값을 직접적으로 변경할 필요가 없기 때문에 사용하기 좋은 예제이다. 만약 이와같은 상황이라면, 우리는 실제 department 이름을 가진 reference table 을 join 해서 사용해야 할 것이다. 그러나 실제로 이러한 값들을 hard-code 시키는 것 보다는 (예를 들어 Male/Female) 이와 같은 방식이 훨씬 더 효율적인 것이다.
비록 report 응용 프로그램에서 읽기 쉬운 형식으로 코드를 변환시키는데 많이 사용되지만, 다음에서 좀더 다양한 일반적인 사용법을 보도록 하겠다.
Logic-driven Column Calculations
만약 HR Manager 가 Sales 부서에 봉급을 모두 20% 올려 주는 효과에 대해서 조사해서 report 해 주기를 원한다고 가정해 보자. 보고서에는 회사내의 월급의 총액이 포함되어야 할 것이고, 이것은 GROUP BY 절을 이용해서 구할 수 있을 것이며, SUM 은 Example 2a 에서 볼수 있다.
Example 2a
SELECT d.dname department,
SUM(e.sal) salary_total
FROM emp e, dept d
WHERE d.deptno = e.deptno
GROUP BY d.dname;
Sales 부서의 고용인들에게 추가적으로 다른 값을 돌려 주도록 하는 것은 복잡하다. 몇가지 상황을 추측할 수 있다. FROM 절에서 다른 emp 테이블을 하나 더 사용할 수도 있으며, Example 2a 에서 처럼 Sales 와 Non-Sales 두개의 데이터 집합에 대해서 UNION 연산자를 이용해 볼수도 있다.
Example 2b
SELECT d.dname department,
SUM(e.sal) * 1.2 salary_total
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND d.dname = 'SALES'
GROUP BY d.dname
UNION ALL
SELECT d.dname department,
SUM(e.sal) salary_total
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND d.dname != 'SALES'
GROUP BY d.dname;
DEPARTMENT SALARY_TOTAL
-------------- ------------
ACCOUNTING 8750 (Second half of UNION)
RESEARCH 10875 (Second half of UNION)
SALES 11280 (First half of UNION)
이 구문은 비록 원하는 결과를 출력해 주지만, UNION 구문에서 이용할 두개의 자료 집합을 얻기 위해서 emp table 에 대해 두번의 full table scan 을 사용한다. (우리는 두개의 자료 집합이 이미 서로 다르다는 것을 알기 때문에 차집합을 없애기 위해서 SORT UNIQUE 를 사용할 필요 없이 UNION ALL 을 사용했다.) Personal Oracle 8.0.3 에서 이 query에 대해서 생성한 plan 은 다음과 같다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 UNION-ALL
2 1 SORT (GROUP BY)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'EMP'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
6 5 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
7 1 SORT (GROUP BY)
8 7 NESTED LOOPS
9 8 TABLE ACCESS (FULL) OF 'EMP'
10 8 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
11 10 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
우리가 emp table 을 두번 scan 하는 유일한 이유는, 부서간의 월급의 합을 구하기 위해, non-Sales 고용인과 그 월급의 자료 집합을 돌려 주어서 SUM(emp.sal) 을 구하고, 그리고 Sales 부서 고용인들의 월급 정보를 찾아서 돌려 주고, SUM(emp.sal*1.2)을 이용해서 합을 구한다. 그리고 이들 두 자료 집합들에 대해 UNION 을 실행한다. 우리는 모든 고용인들의 월급 정보를 가져오도록 emp table 을 한번만 scan 한 후에, SELECT 구문 내에서 선택적으로 Sales 고용인들에게만 20%를 추가해 주도록 DECODE 를 이용하여 질의를 최적화 시킬 수 있다. (Example 2c)
Example 2c
SELECT d.dname department,
SUM(DECODE(d.dname,
'SALES', e.sal * 1.2,
e.sal)) salary_total
FROM emp e, dept d
WHERE d.deptno = e.deptno
GROUP BY d.dname
DEPARTMENT SALARY_TOTAL
-------------- ------------
ACCOUNTING 8750
RESEARCH 10875
SALES 11280
비록 결과가 동일하고, 두 문장이 기능적으로 동일하지만, 실행 plan 을 보면 명확히 이 구문은 emp table 을 한번만 full scan 한다는 것을 알수 있으며, 만약 emp table 이 아주 큰 테이블이라면 유의한 performance 향상을 가져 올 것이라는 것을 알수 있다.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
그럼 이제 뭐가 바뀌었는지를 보자. 우리는 마지막까지 SELECT 구문을 남겨두어야 하고 (가장 유의한 변경이 그때 일어 나므로), 구문의 일부를 제거할 수 있다.
우리는 여전히 FROM 절에서 같은 테이블 두개를 적어야 하고 이것을 부서 이름별로 GROUP BY 를 해 주어야 하므로 이 두 부분은 구문에서 바뀌지 않았다. 우리는 회사의 모든 고용인들에 대해 조사해야 하므로, 첫번째 구문에서 두개의 다른 WHERE 절을 검사해서 부서명을 제거하도록 하자. 이것은 우리가 emp 와 dept 테이블 간에 join 을 할때와 같은 WHERE 절을 보여 준다. 이제 두개의 WHERE 절은 그들이 같은 row 를 돌려 줄때와 동일하며, 이제 우리는 이것을 UNION 을 사용할 필요 없이 하나의 자료 집합으로 줄일 수 있다. 사실 질의는 example 2a 의 시작과 같아 보일 것이다.
FROM emp e, dept d
WHERE d.deptno = e.deptno
GROUP BY d.dname;
이것은 우리에게 새로운 SELECT 구문 처럼 보인다. dept table 로 부터 부서이름을 가져 오는 첫번째 컬럼은 동일하게 남아 있게 된다. 두번째 컬럼은 UNION 의 WHERE 절로 부터 가져온 logic 을 포함하게 된다. 이것은 부서의 모든 고용인들의 총 월급을 계산하기 위해서 SUM() 함수를 사용했지만, 부서가 SALES 냐 아니냐에 따라 다른 값을 사용했다. 그래서 example 2c 가 어떻게 동작하는 high-level procedural view 를 보여 주고 있다.
FOR EACH department (GROUP BY d.dname)
Generate the total salary for that department (SUM)
FOR EACH employee (DECODE …)
IF the related department (d.dname) is 'SALES', then
Use salary (e.sal) * 1.2
ELSE (by default)
Use the employee's current salary (e.sal)
Example 2c 는 이전 예제에 사용하지 않았던 두번의 DECODE 함수를 이용했으며, 둘다 DECODE 의 아주 일반적인 사용형태이다. 먼저 돌아온 값 (emp.sal 또는 emp.sal*1.2) 는 우리가 검사한 (dept.dname) 의 값을 변경한 것이 아니다. 두번째로 DECODE 는 field (emp.sal) 를 돌려 줄수도 있고, 계산 값을 (emp.sal*1.2) 돌려 줄수도 있다. DECODE 의 모든 인자들은 임의 자료형을 가지는 아주 복잡한 표현식일 수 있다는 점은 중요하게 기억해 두어야 한다.
Mind the Trap!
다음 예제는 우리의 emp 와 dept 테이블에 원래 오라클에서 제공하는 자료보다 훨씬 더 많은 자료가 포함되어 있다고 가정해 보자. 예를 들어 500개의 부서와 수천개의 고용인이 있다고 가정해 보자. 우리의 이전 보고서는 500개 모든 부서에 대해서 자룔ㄹ 생성해 낼것이고, 이제 HR Manager 가 "좋아. 난ㄴ 이제 Slae 와 Research 부서의 총 월급만을 보길 원하네" 라고 말했다고 가정하자. 전혀 실망할 필요없다. 우리는 example 3a 에서 보는 바와 같이 DECODE 를 약간만 변형하면 된다.
Example 3a
SELECT SUM(DECODE(d.dname,
'SALES', e.sal * 1.2,
'RESEARCH', e.sal)
total_salary_bill
FROM emp e, dept d
WHERE d.deptno = e.deptno;
TOTAL_SALARY_BILL
-----------------
22155
이것은 SALES 고용인들의 (월급*1.2)의 총합을 구해 줄것이고, 그기에 RESEARCH 고용인들의 월급의 총합을 더해서 구해 줄것이다. 여기서 둘중 어디에도 속하지 않는 고용인들은 NULL 값을 돌려 줄것이므로 총합에는 전혀 영향을 미치지 않는다. HR 은 원하는 바를 얻게 될 것이고, 우리는 일상으로 가고 모두는 행복해 질것이다. 정말 그럴까? DECODE 를 사용하는데 있어서 강점은 논리적으로 명확성을 보장해 준다는 것이지만, 주의하지 않으면, 비효율적인 코드가 될 수 있다는 것이다. 이 예제는 모든 고용인들에게 DECODE 함수를 적용시키도록 되어 있으나 대부분의 고용인들의 자료는 이 함수를 이용할 필요가 없다. 그러므로 우리는 먼저 불필요한 고용인의 자료를 제외시킬 필요가 있다. 같은 결과를 얻는 좀더 나은 질의가 Example 3b 에 주어져 있다.
Example 3b
SELECT SUM(DECODE(d.dname,
'SALES', e.sal * 1.2,
'RESEARCH', e.sal)
total_salary_bill
FROM emp e, dept d
WHERE d.deptno = e.deptno
and d.dname IN ('SALES','RESEARCH'); -- New Line
TOTAL_SALARY_BILL
-----------------
22155
이것은 약간 다르다. 새로 추가 된 WHERE 행에서 SALES 와 RESEARCH 부서의 고용인들만 SELECT 하도록 질의를 제한 하고 있다. 실제로 오라클이 WHERE 절을 최적화 하기 우해서 인덱스를 사용하느냐 등에 따라 상황은 복잡하다. 그러나 나는 모든 경우에 있어서 기본적인 접근법을 이용하도록 제안하고자 한다. SQL 명령문에서 DECODE 함수를 사용할때 새롭게 excution plan 을 향상시키는 방법은 없다. 절대로 기억해야 할 것은 DECODE 는 post-retrieval function 이라는 것이다.
|
