Oracle 노하우 및 팁
이번 절에서는 공용 View를 사용할 때 공용 View 안의 컬럼이 가공이 되어 조건을 침투 못시키는 Case를 살펴보겠습니다.
0. View 안으로 조건을 침투 못시키는 Case
1) View와 조인되거나 조건으로 들어간 컬럼이 View안에서 가공(DECODE, NVL, SUBSTR 등)되어 있을 경우
2) View와 조인되거나 조건으로 들어간 컬럼이 View안에서 Scalar Subquery로 가공되어 있을 경우
3) View와 조인되거나 조건으로 들어간 컬럼이 View안에서 User Defined Function으로 가공되어 있을 경우
1. View와 조인되거나 조건으로 들어간 컬럼이 View안에서 가공(DECODE, NVL, SUBSTR 등)이 되어 있을 경우
1) Test Case
① ’ACCOUNTING’ 부서에 대해서만 부서 급여 합계를 보여주는 View
CREATE OR REPLACE VIEW EMP_DEPTNO_GUBN_V AS
SELECT DECODE(DEPTNO, 10, DEPTNO) DEPTNO_GUBN,
SUM(SAL) SUM_SAL
FROM EMP
GROUP BY DECODE(DEPTNO, 10, DEPTNO)
;
② 위에서 만든 View를 사용하는 SQL
SELECT D.DNAME,
EDGV.SUM_SAL
FROM DEPT D,
EMP_DEPTNO_GUBN_V EDGV
WHERE D.DEPTNO = EDGV.DEPTNO_GUBN
AND D.DNAME = ’ACCOUNTING’
;
③ 실행계획
보시는 것처럼, DEPT에서 조인하는 컬럼 DEPTNO가 EMP_DEPTNO_GUBN_V 뷰에서 DECODE()로 가공이
되어 있기 때문에 View 안으로 침투롤 못하고 HASH JOIN으로 풀고 있습니다.
그러므로 EMP 테이블에서 DEPTNO 컬럼으로 인덱스가 있는 것을 활용하기 위해 DECODE()로 가공된
부분을 테이블의 컬럼으로 활용하도록 변경해야만 침투가 가능합니다.
아래에서 이 부분을 살펴 보도록 하겠습니다.
2) 해결 방법
① View 안으로 조건이 침투가 가능하도록 EMP_DEPTNO_GUBN_V 변경
CREATE OR REPLACE VIEW EMP_DEPTNO_GUBN_V AS
SELECT DEPTNO DEPTNO_GUBN,
SUM(SAL) SUM_SAL
FROM EMP
WHERE DEPTNO = 10
GROUP BY DEPTNO;
② 변경한 View를 가지고 1) > ②번의 SQL 실행계획 다시검토
SELECT D.DNAME,
EDGV.SUM_SAL
FROM DEPT D,
EMP_DEPTNO_GUBN_V EDGV
WHERE D.DEPTNO = EDGV.DEPTNO_GUBN
AND D.DNAME = ’ACCOUNTING’
;
이제 원하는데로 EMP_DEPTNO_GUBN_V로 조건이 잘 침투가 됐는데요. 여기서 재미있는 현상이
하나 보입니다. 바로 DEPT 테이블에 조건을 D.DNAME = ’ACCOUNTING’ 으로 주었는데 DEPT
테이블에는 DNAME 컬럼으로 인덱스는 잡혀져 있지 않기 ㅤㄸㅒㅤ문에 FULL TABLE SCAN을 할것으로
예상하셨을 것입니다.
하지만 옵티마이저는 DEPTNO 컬럼으로 생성된 Unique 인덱스를 사용하였는데요. 어떻게 이런
현상이 나타났을까요?
즉 조건은 D.DNAME = ’ACCOUNTING’이 들어갔지만 조인컬럼이 DEPTNO로 연결이 되어 있기 때문에
이 컬럼 또한 드라이빙 조건으로 사용이 되어서 이 컬럼을 인덱스로 사용하였고, 그렇기 때문에
DEPT 테이블을 인덱스로 액세스 할 수 있게 되었던 것입니다.
⑥ 정리
이처럼 공용 View안에서 SELECT-LIST에 있는 컬럼들을 나열할 때 밖에서 조인이 들어올 가능성이
있는 컬럼들에 대해서는 가공을 하지 않아야 View 안으로 조건이 침투가 가능합니다. 그러므로
DECODE, NVL, TO_CHAR, TO_NUMBER 같은 함수로 컬럼을 가공하지 말고 로직으로 처리할 수 있도록
해야 할 것입니다.
2. View와 조인되거나 조건으로 들어간 컬럼이 View안에서 Scalar Subquery로 가공되어 있을 경우
1) Test Case
① EMP 테이블에서 DEPT의 LOC 정보를 스칼라 서브쿼리로 만든 View
CREATE OR REPLACE VIEW EMP_DEPT_LOC_V AS
SELECT E.ENAME,
E.JOB,
(SELECT D.LOC
FROM DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND DNAME IN (’SALES’, ’RESEARCH’)) D_LOC
FROM EMP E
;
② 위에서 만든 View를 사용하는 SQL
SELECT *
FROM EMP_DEPT_LOC_V
WHERE D_LOC = ’CHICAGO’
;
③ 실행계획과 Trace
보시는 것처럼, EMP 테이블에서 DEPT의 LOC 정보를 가져오기 위해 스칼라 서브쿼리로 만든 View를
사용하는데 공교롭게도 그 스칼라 서브뭐리의 컬럼으로 조회를 하고 있습니다.
1번에서도 살펴보았듯이, 스칼라 서브쿼리 또한 가공이 되어있기 때문에 EMP 테이블에서 전체
데이터를 가져온 뒤 스칼라 서브쿼리의 내용을 필터처리해서 가져와야 합니다.
’Predicate Information’절을 보시면 그 내용이 있는데, 3번 라인을 보시면 다음의 내용이
있습니다.
3 - filter( (SELECT /*+ */ "D"."LOC" FROM APPS."DEPT" "D" WHERE
"D"."DEPTNO"=:B1 AND ("DNAME"=’RESEARCH’ OR "DNAME"=’SALES’))=’CHICAGO’)
즉, FILTER 부분에서 EMP에서 가져온 14건을 스칼라 서브쿼리로 가공한 뒤에 이들 중
’CHICAGO’값이 있는지 없는지 하나씩 비교하여 체크를 한 뒤에 만족하는 6건을 가져오고 있습니다.
이처럼 스칼라 서브쿼리를 필터가 아닌 드라이빙으로 사용하기 위해서는 어떻게 해야 할까요?
바로 조인으로 연결을 시키는 것입니다.
여기서 알아두셔야 할 내용 하나를 말씀드리면, 오라클 DBMS는(다른 DBMS는 제가 접해보지 못해서)
Object로 데이터를 저장한 데이터에 대해서만 인덱스를 생성할 수 있습니다(FBI Index 제외)
즉 실제 테이블 상에 물리적으로 저장된 데이터에 대해서만 인덱스를 만들 수 있으며 가공된
컬럼이나 계산된 컬럼들에 대해서는 저장된 데이터가 아니기 때문에 인덱스로 잡을 수 없습니다.
물론 FBI 인덱스로 만들 수 있긴 하지만 FBI 인덱스는 반드시 그 가공된 형태로 조건이 들어올
때문 유효하기 때문에 변화무쌍한 조건들에 비해 그 가용성이 극히 제한적입니다.
이 점을 충분히 이해하신다면 왜 가공된 컬럼으로는 체크로밖에 필터할 수 밖에 없는지
왜 View 안으로 침투를 못시키는지 이해할 수 있으실 것입니다.
잠시 다른 얘기로 넘어갔는데 다시 돌아와서 이 문제를 보면 위에서 얘기한 것 처럼 가공이
되어 있기 때문에 조건을 체크가 아닌 드라이빙으로 사용하려면 가공된 컬럼을 가공되지 않은
컬럼으로 만들어야 합니다. 또한 그렇게 만들려면 조인으로 연결시켜서 가공되지 않은 컬럼으로
조건이 들어오도록 해야겠죠.
아래 해결방법에서는 스칼라 서브쿼리를 조인으로 어떻게 만들어야 하는지 살펴보겠습니다.
2) 해결 방법
혹시 스칼라 서브쿼리와 비슷한 개념을 가진 오라클 구문이 있는데 여러분은 혹시 알고 계시는지요?
SQL 활용도가 좋으신 분들은 이미 눈치 채셨을텐데요. 바로 Outer Join 입니다.
Outer Join은 여러분께서 알고 계시듯이 어느 한쪽을 기준으로 ’Equi Join’이 되지 않는 데이터도
추출하기 위해서 사용하는 구문입니다.
그림에서 보시듯이 ’TAB_1.COL_1 = TAB_2.COL_3(+)’로 조인을 할 경우 TAB_1 테이블을 기준으로
데이터를 가져올 수 있습니다.
이와 비슷한 개념이 바로 스칼라 서브쿼리인데요. 스칼라 서브쿼리는 FROM절에서 나온 데이터를
한 건씩 받아서 그에 해당되는 데이터를 SELECT절에서 가져오는 구문입니다.
그럼 Outer Join과 스칼라 서브쿼리 사이에 비슷한 점과 차이점을 잠시 살펴보겠습니다.
- 비슷한 점
Outer Join은 하나의 테이블을 기준으로 ’Equi Join’이 되지 않은 데이터도 모두 가져오는 구문입니다.
마찬가지로 스칼라 서브쿼리 또한 SELECT절에서 해당 데이터와 일치하는 레코드가 없다고 하더라도
NULL값을 리턴합니다. 이는 스칼라 서브쿼리의 특징이라기 보다 SELECT절의 특징이기 때문입니다.
아래에서 그 내용을 테스트 해보겠습니다.
SQL >
1 SELECT ’TRUE’
2 FROM DUAL
3 WHERE 1=1;
’TRUE’
--------
TRUE
1 row selected.
Elapsed: 00:00:00.00
SQL >
1 SELECT ’TRUE’
2 FROM DUAL
3 WHERE 1=2;
no rows selected
Elapsed: 00:00:00.00
SQL >
1 SELECT CASE WHEN 1 = 1 THEN ’TRUE’ END VALUE
2 FROM DUAL;
VALUE
--------
TRUE
1 row selected.
Elapsed: 00:00:00.01
SQL >
1 SELECT CASE WHEN 1 = 2 THEN ’TRUE’ END VALUE
2 FROM DUAL;
V
-
1 row selected.
이는 Outer Join과 비교해 보았을 때 Outer Join 또한 하나의 테이블을 기준으로 일치되는 레코드가
없더라도 기준이 되는 테이블에 있는 모든 데이터를 가져오고 반대편은 NULL값을 가져오는 것처럼,
스칼라 서브쿼리 또한 일치되는 값이 없더라도 데이터는 가져오되 NULL값을 가져오기 때문에 비슷한
개념이라고 볼 수 있습니다.
그럼 스칼라 서브쿼리에서 Outer Join 처럼 기준이 되는 테이블은 어느쪽일까요? 당연히 FROM절에 있는
테이블이 기준 테이블이 될 수 있습니다.
- 차이점
차이점은 Outer Join일때 기준테이블이 1건 상대테이블이 여러건일경우 조인이기 ㅤㄸㅒㅤ문에 여러건을 가져오게
되지만, 스칼라 서브쿼리는 이런 관계일경우 에러를 내보냅니다. 즉 스칼라 서브쿼리는 단일 레코드에 대해서만
데이터를 가져올 수 있기 때문에 이점에서는 Outer Join과 다르다고 할 수 있습니다.(스칼라 서브쿼리에서
2개 이상 레코드를 가져오는 구문은 8i부터 Cursor구문으로 구현은 가능하게 되었습니다)
- 정리
이 내용을 충분히 이해하셨다면 스칼라 서브쿼리를 조인으로 변경할 경우 반드시 Outer Join으로
내려야 한다는 것을 아실 수 있으실겁니다. 그럼 실제로 스칼라 서브쿼리를 Outer Join으로
내려보겠습니다.
① 위에서 스칼라 서브쿼리로 만든 내용을 Outer Join으로 내린 뷰
CREATE OR REPLACE VIEW EMP_DEPT_LOC_V_2 AS
SELECT E.ENAME,
E.JOB,
D.LOC D_LOC
FROM EMP E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO(+)
AND DECODE(D.DNAME(+), ’SALES’, ’1’, ’RESEARCH’, ’1’) = ’1’
;
② 위에서 만든 View를 사용하는 SQL
SELECT *
FROM EMP_DEPT_LOC_V_2
WHERE D_LOC = ’CHICAGO’
;
③ 실행계획과 Trace
이제 원하는데로 DEPT.LOC에 조건을 넣을 경우 DEPT 테이블을 먼저 드라이빙 하고 거기서 나온 데이터를
EMP와 Nested Loop로 잘 조인이 되었습니다. 여기서 하나 더 중점으로 봐야 할 내용이 있는데요.
스칼라 서브쿼리에서 ’AND DNAME IN (’SALES’, ’RESEARCH’)’ 이 조건을 Outer Join으로 내릴 때
DECODE() 구문을 사용했다는 것입니다.
스칼라 서브쿼리 : AND DNAME IN (’SALES’, ’RESEARCH’)
Outer Join : AND DECODE(D.DNAME(+), ’SALES’, ’1’, ’RESEARCH’, ’1’) = ’1’
IN 조건이 스칼라 서브쿼리에서 Outer Join으로 내려올 때
AND DNAME(+) IN (’SALES’, ’RESEARCH’)
위와 같이 사용하면 될 것 같지만 이렇게 하였을 경우는 Outer Join이 아니라 Equals 조인밖에
되지 못합니다. 그러므로 반드시 IN 조건을 Outer Join으로 변경할 때는 DECODE() 구문을 사용하셔야 합니다.
3. View와 조인되거나 조건으로 들어간 컬럼이 View안에서 User Defined Function으로 가공되어 있을 경우
- 이 부분 또한 스칼라 서브쿼리에서 살펴보았듯이 View 내에서 저장형 함수로 만들어진 컬럼을 View 밖에서
조건으로 사용할 경우 View 안으로 침투가 되지 못합니다.
- 그러므로 저장형 함수로 가공된 컬럼을 조건으로 사용하고자 한다면 반드시 FROM절의 Outer Join으로 내려서
가공되지 않은 컬럼을 SELECT절에 올려야 침투가 가능합니다.
4. 정리
1) View 안으로 조건을 침투시키지 못하는 3가지 Case를 살펴보았습니다. 이 내용을 간단히 정리하면 인덱스와
비교를 할 수 있습니다. 테이블에 N개의 컬럼으로 인덱스를 만들 경우 그 테이블을 조회할 때 인덱스에 있는
컬럼을 조건으로 주면 인덱스를 먼저 읽고 테이블을 액세스하기 ㅤㄸㅒㅤ문에 빠른 응답속도를 볼 수 있습니다.
하지만 조건컬럼이 인덱스에 없을 경우 테이블을 모두 읽은 뒤에 해당 조건과 일치하는 레코드를 일일이
체크하면서 데이터를 찾을 수 밖에 없겠지요.
2) 이 내용처럼 ’테이블 -> View, 인덱스 -> 컬럼’으로 확장을 해보면 해당 View에 조건이 침투될때와
안되었을때의 퍼포먼스 차이가 날 수 밖에 없다는 것을 이해하시게 되실겁니다.
3) 그러므로 공용 View를 생성할 때 View 안에 나열된 SELECT절의 컬럼들 중 이 View를 사용하는
프로그램들에서 조건으로 사용될 수 있는 가능성이 있는 컬럼들은 반드시 가공되지 않도록 주의하여
생성해야 향후에 해당 View를 사용하는 개발자들 나아가서는 운영으로 넘어갔을 때 안정적인 퍼포먼스를
유지하여 큰 비용을 줄일 수 있을 것입니다.
다음 시간에는 공용View를 사용할 때 글로벌 힌트가 잘 들어가도록 하기 위한 전략에 대해 살펴보겠습니다.
읽어보시고 궁금한 점 있으시면 리플 달아주시기 바랍니다.
감사합니다.
수고하셨네용~
서브뭐리의...
좋은글 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.