Lateral View를 활용한 튜닝

고객유형(super type)에 따라서 개인기본 또는 사업자기본으로 조인해야하는 쿼리를 Literal View를 이용해 작성한다.

연락처기본은 고객기본과 1:1 관계이다.

이와 같은 모델에서 개발자들은 아래와 같은 형태의 쿼리를 작성한다.

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 한것을 볼수 있다.

위의 쿼리는 항상 고객번호 인덱스로 개인기본과 사업자기본 테이블을 조건 검색 후에 연락처기본과 조인하는 구조이다.

다시 말하면 개인 고객인경우도 사업자기본 테이블을 액세스하고 사업자고객인 경우도 개인기본 테이블을 액세스 한다는 뜻이다.

아래와 같이 ANSI SQL을 사용하면 오라클은 Lateral View로 변환하여 비효율적인 액세스를 방지한다.

고객유형에 따라서 개인일 경우 개인기본 테이블을 사업자일 경우는 사업자기본 테이블을 액세스하게 된다.

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 이라는 뜻이다.

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)

자주 엑세스하는 뷰를 만들때도 위와 같은 쿼리로 작성해야 한다.

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_고객번호;

성능을 위해서는 Super type으로 선택적으로 조인해야 하는경우 ANSI Outer Join 또는 decode 함수를 사용하여 선택적으로 Join을 처리 해야한다.

출처: Science of Database