Lateral View 를 활용한 튜닝

Lateral View와 아우터조인의 개념에 대해서는 이미 언급이 되었고 이번에는 Lateral View 를 이용한 튜닝에 대하여 알아보기로 한다.
먼저 개발자들에게 받는 질문 상위 10개 중에 항상 들어있는 질문이 있다.
"선택적으로 조인하는 기능이 오라클에 있습니까?"
필자는 항상 다음과 같이 답변한다.
"있습니다."
아래 모델을 보자.

이모델을 보면 고객유형(subtype) 에 따라서 개인기본으로 조인할지 사업자기본으로 조인할지 결정이 되는것이다.
물론 연락처기본은 고객기본과 항상 1:1 이다.
이런 모델에서 대부분의 개발자는 아래와 같은 SQL 을 작성한다.

SELECT
A.고객번호, A.고객유형,
B.취미코드, B.종교코드,
C.사업규모코드, C.종업원수,
D.대표핸드폰번호
FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D
WHERE A.고객번호 = B.고객번호 (+)
AND A.고객번호 = C.고객번호(+)
AND A.고객번호 = D.고객번호
AND A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함.

언뜻 보기에 위의 SQL 은 아무 문제가 없어보인다.
하지만 과연 그런가?
아래 Trace 결과를 보자

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)
1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)
1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)
1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)
1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)
1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)
1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)
0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=3 pr=0 pw=0 time=33 us)
0 INDEX UNIQUE SCAN PK_사업자기본 (cr=3 pr=0 pw=0 time=29 us)
1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)
1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)

개인고객임에도 불구하고 사업자기본 테이블 및 인덱스에 3블럭(cr =3)씩 read 한것을 볼수 있다.
위의 SQL 은 항상 고객번호 인덱스로 개인기본과 사업자 기본을 뒤진후에 연락처기본과 조인하는 구조이다.
다시말하면 개인고객인경우도 사업자기본 테이블을 access 하고 사업자고객인 경우도 개인기본 테이블을 access 한다는 뜻이다.

아래처럼 ANSI SQL 을 사용하여 SQL 을 수정하면 오라클은 Lateral View 로 변환하여 비효율적인 access 를 방지한다.
고객유형에 따라서 개인일경우 개인기본 테이블만 access하고 고객유형이 사업자일 경우는 사업자기본 테이블만 access 한다.

SELECT
A.고객번호, A.고객유형,
B.취미코드, B.종교코드,
C.사업규모코드, C.종업원수,
D.대표핸드폰번호
FROM 고객기본 A left outer join 개인기본 B
on (A.고객번호 = B.고객번호 and A.고객유형 = '1') --> 고객유형이 개인 일경우만 조인됨
left outer join 사업자기본 C
on (A.고객번호 = C.고객번호 and A.고객유형 = '2') --> 고객유형이 사업자 일경우만 조인됨
join 연락처기본 D
on (A.고객번호 = D.고객번호) --> 무조건 조인한다.
WHERE A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함

이것을 Trace 에서 차이를 비교해보면 read 한 블럭수가 차이난다.
즉 개인고객이면 사업자기본 테이블을 읽은 블럭수가 0 이고 사업자고객이면 개인기본 테이블을 읽은 블럭수가 0 이라는 뜻이다.
아래의 Trace 결과를 보자.

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)
1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)
1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)
1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)
1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)
1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)
1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)
0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=0 pr=0 pw=0 time=33 us)
0 INDEX UNIQUE SCAN PK_사업자기본 (cr=0 pr=0 pw=0 time=29 us)
1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)
1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)

자주 엑세스 하는 뷰를 만들때도 위와 같은 SQL 로 만들어야 할것이다.
ANSI SQL 을 사용할수 없는 구조라면 아래처럼 DECODE 함수를 활용하면 위와 같은 효과를 얻을수 있다.

SELECT
A.고객번호, A.고객유형,
B.취미코드, B.종교코드,
C.사업규모코드, C.종업원수,
D.대표핸드폰번호
FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D
WHERE DECODE(A.고객유형, '1',A.고객번호) = B.고객번호 (+) --> 고객유형이 개인 일경우만 조인됨
AND DECODE(A.고객유형, '2',A.고객번호) = C.고객번호(+) --> 고객유형이 사업자 일경우만 조인됨
AND A.고객번호 = D.고객번호
AND A.고객번호 = :V_고객번호;

결론:

성능을 위해서는 Subtype 으로 인하여 선택적으로 조인해야 하는경우 ANSI Outer 조인으로 조인을 제한 하거나 decode 함수를 사용하여 선택적으로 조인을 해야한다.

Outer Join 의 재조명

Outer Join 의 Lateral Views 로의 변환

목적 : 많은 사람들이 ANSI OUTER JOIN 에 대해 이해하지 못하고 심지어 튜너들이라고 하는사람들까지도 개념정립을 못하고 있다.

이 문서를 보고 개념정립을 확실히 하기바란다.

개념:
Oracle 에서는, ANSI left outer join은 내부적으로 left outer joined lateral views로 표현된다.
'lateral view'는 FROM절에서 앞서 나타난 다른 테이블들을 참조하는 관계를 포함한
inline view이다.
다시말하면 'lateral view'는 Scalar inline view 라고 보면된다.
'lateral view'는 스칼라 서브쿼리처럼 결과집합의 건수에 영향을 미치지 못한다.

Oracle 은 ANSI SQL 2003 의 'lateral view'를 지원하지 못하고 내부적으로만 사용하고 있다.

버그인가 아니면 정확한 결과인가?

아래 예제를 보고 헷갈릴수밖에 없는 이유를 설명한다.

데이터는 다음과 같다.

1) EMP 테이블

SELECT EMPNO, ENAME, DEPTNO
FROM EMP;

2) DEPT 테이블

SELECT DEPTNO, DNAME

FROM DEPT;

3) ANSI 문법으로 outer join 쿼리를 날려본다.

SELECT empno, ename, dname, d.deptno

FROM EMP E LEFT OUTER JOIN DEPT D

ON E.deptno = D.deptno

AND E.empno > 7600;

뭔가 이상하다 사번이 7600 보다 큰건들만 나오지 않고 전체가 나와 버린다.

버그인가?
전혀아니다.

오라클 옵티마이져는 위 SQL 이 나오면 아래와 같이 변환한다.
변환된 모습을 보면 결과를 예측할수 있다.

'lateral view' 개념을 적용

위의 쿼리변형결과를 보고 아래와 같은 결론을 내릴수있다.

1)위의 'lateral view' 는 결과 건수에 영향을 미치지 못하는 스칼라 인라인뷰이다.

2)E.empno > 7600 조건은 결과건수에 영향을 못미치고 DEPT 와의 조인건수에만

영향을 끼친다.
다시말하면 E.empno > 7600 에 만족하는건만 DEPT 와 조인한다.
3)ANSI OUTER JOIN 이 헷갈릴 경우는 Lateral View 개념을 적용하여 쿼리를 작성하면 된다.

그렇다면 아래와 같은 쿼리는 결과가 어떻게 나올것인가

SELECT empno, ename, dname, d.deptno
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.deptno = D.deptno
WHERE E.empno > 7600;

위쿼리를 'lateral view' 개념을 적용하면 아래와 같다

위쿼리를 보면 메인쿼리의 WHERE 절이 추가된것을 알수 있다.

결과도 당연히 사번이 7600 보다 큰건만 나오게 된다.

참고 문헌
ANSI Outer Joins And Lateral Views, http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/(structureddata.org)
Back to basics: outer joins, http://awads.net/wp/2006/03/22/back-to-basics-outer-joins/

DBMS_ADVANCED_REWRITE

개념 :
-Oracle 10g 의 새기능인 DBMS_ADVANCED_REWRITE는 특정 SQL 을 다른 SQL 로 대체하는
기능이다.
-SQL 을 바꾸지 않고 튜닝해야하는 경우에 적합하다.
-Oracle 9i 에 Stored OutLine 이라는 기능이 있었지만 위와 같은경우에는 쓰기가 매우 불편하였다.

사용방법 :
1)user 에게 권한을 준다
CONN sys/password AS SYSDBA
GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test;
GRANT CREATE MATERIALIZED VIEW TO test;

2) rewrite 될 SQL
SELECT * FROM test_tab;
ID DESCRIPTION
---------- --------------------------------------------------
1 GLASGOW
2 BIRMINGHAM
3 LONDON

3 rows selected.
SQL>

3) 뷰를 하나만들고 쿼리가 들어오면 뷰로 대신한다.
CREATE OR REPLACE VIEW rewrite_v AS
SELECT id, INITCAP(description) AS description
FROM test_tab
ORDER BY description;

BEGIN

SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence ( name => 'test1_rewrite',

source_stmt => 'SELECT * FROM test_tab',

destination_stmt => 'SELECT * FROM rewrite_v',

validate => FALSE,

rewrite_mode => 'TEXT_MATCH');

END;/

4) 이제 설정이 끝났으므로 기능이 되는지 TEST 한다.
SELECT * FROM test_tab;
ID DESCRIPTION
---------- --------------------------------------------------
1 GLASGOW
2 BIRMINGHAM
3 LONDON

3 rows selected

기능이 실행되지 않았는데 이유는 아래 파라미터 때문이다.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Session altered.

SELECT * FROM test_tab;

ID DESCRIPTION
---------- --------------------------------------------------
2 Birmingham
1 Glasgow
3 London

3 rows selected.

성공적으로 수행되었다.

5)DBMS_ADVANCED_REWRITE 기능의 모든 설정을 보려면 아래뷰들을 참조하면 된다.
-DBA_REWRITE_EQUIVALENCES
-ALL_REWRITE_EQUIVALENCES
-USER_REWRITE_EQUIVALENCES

-SELECT * FROM user_rewrite_equivalences;

6) 이기능을 비활성화 하려면 아래 프로시져를 사용한다.
EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test1_rewrite');