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 함수를 사용하여 선택적으로 조인을 해야한다.

가로를 세로로 바꾸기

세로를 가로로 만드는 방법에 대해서는 Recursive SQL 에서 충분히 논하였다.
그렇다면 가로를 세로로 바꾸는 방법중의 최적은 무엇인가?

그문제와 관련하여 필자는 얼마전 어떤 개발자가 유져로 부터 다음과 같은 질문을 받았는것을 보았다.

유져 : "DBMS 와 웹 Application 간에 배열 연동이 안되므로 변수를 배열로 던져주지 못하는 대신에 '7654,7698,7780,7788' 처럼 변수를 만들어서 Oracle 에 던지고 싶습니다.
그런데 이렇게 던지면 Oracle 에서 List 로 만들어서 처리할수 있습니까?"

개발자 : "예" --> 어느정도 내공이 있는 개발자임을 알수 있다.

유져 : "구분자(delimiter)가 항상 ',' 가 아닐수도 있고 1 byte 도 아닐수 있습니다.
예를들면 구분자가 ':;;:' 처럼 될수도 있고 그것은 프로그램 마다 다를수 있습니다.
그래도 가능 하겠습니까?"

개발자: (주저주저 하며 대답을 못함)

이런경우 일단 대답은 "예" 이다.

왜냐하면 프로그램마다 문자열과 구분자를 받을수 있기 때문이다.

SELECT :v_str , :v_deli FROM dual;

--여기서 :v_deli 는 구분자(',') 이며 :v_str 는 문자열 '7654,7698,7780,7788' 이다.

결과 :

Oracle 10g 라면 아래처럼 간단한 SQL 로 IN-LIST 를 만들수 있다.

SELECT
ltrim(regexp_substr(:v_deli:v_str:v_deli, :v_deli'[^':v_deli']+', 1, level ), :v_deli) AS empno
FROM dual
connect by level<= ( length(:v_deli:v_str) - length(replace(:v_deli:v_str, :v_deli)) ) / length(:v_deli) ;

결과 :

처리의 핵심은 첫번째로는 문자열 길이 에서 구분자(comma) 를 제거한 길이를 빼서 구분자의 길이로 나누면 row 갯수가 나온다는 점이다.
두번째로는 10g 부터 사용할수 있는 정규식을 사용하여 시작문자와 끝문자 를 정의해서 그사이에 있는 문자들을 가져올수 있다는 점이다.
예를들면 regexp_substr( 문자열, ',[^,]+') 이렇게 하면 문자열에서 ,(comma) 부터 다음 ,(comma) 까지의 문자들을 가지고 올수 있다.
물론 10g 미만의 버젼이라면 level 과 connect by 를 대신해서 copy_t 류의 테이블을 사용하고 substr 과 instr 함수를 적절히 조합하면 된다.

이해를 돕기위해 아래 SQL 을 실행해 보자.
아래 SQL 은 첫번째,두번째,세번째, 네번째 의 사번(EMPNO)을 위의방법을 이용하여 각각 가져오고 있다.

SELECT
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 1 ) empno1,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 2 ) empno2,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 3 ) empno3,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 4 ) empno4
FROM dual;

주의사항은 오라클 정규식의 예약어인 파이프라인 이나 괄호 )( ][ {} 등을 구분자로 사용하지 말라는것이다.
파이프라인 이나 괄호 대신에 ':' 처럼 적당한 다른문자를 사용하면 됨.

NO Costing in CBO

CBO(Cost Based Optimizer) 라고 하면 DBMS 종류를 막론하고 비용이 가장 낮은 실행계획을 선택하는것이라 할수있다.
보통 CBO 에서 문제가 되는것은 (스키마통계정보 or 시스템통계)의 부재나 부족으로 인한 잘못된 Cost를 계산해서 잘못된 실행계획을 선택하는것 이라고 볼수있다.
하지만 항상 그런것은 아니다.
CBO 가 높은 Cost 를 선택해서 끔찍한 일을 저지를 수도 있다는 것이다.
어떤경우에 No Costing 이 발생하는지 아래를 참조하자.

1.환경 :
버젼 : 10gR2(10.2.0.3)
Optimizer mode :all_rows
계정 : scott/tiger

2.임시 부서 테이블 생성
CREATE TABLE TEMP_DEPT AS SELECT * FROM DEPT; --> DEPT 임시테이블 생성

3.통계정보 생성
EXEC dbms_stats.gather_table_stats(user,'TEMP_DEPT',cascade=>true);
EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> EMP 는 그대로 사용.

4. Hash join Cost

SQL> explain plan for
2 select /*+ USE_HASH(d e) */ e.ename, d.dname
3 from emp e , temp_dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';

Explained.

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> cost 가 5이다.
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------

4. NL join Cost

SQL> explain plan for
2 select /*+ USE_NL(d e) */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';

Explained.

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 4 --> cost 가 4이다.
1 NESTED LOOPS 5 90 4
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
* 3 TABLE ACCESS FULL EMP 5 35 2
--------------------------------------------------------------------

-- NL 조인이 COST 가 더작은걸 알수 있다.

5.CBO 는 과연 어떤 조인을 선택 할것인가?

SQL> explain plan for
2 select /*+ ALL_ROWS */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> COST 가 높은 HASH 조인선택
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------

개인적인 생각으로 all_rows 에서는 비용이 높더라도 NL 조인시 FULL SCAN 을 반복하는것을 피하는 어떤 rule 이 있는것 같다.

6.결론:
FIRST_ROWS 나 FIRST_ROWS(1) 로 힌트를 사용하면 NL 조인으로 풀리는 것이 관찰 되었다.
그러나 FIRST_ROWS 시리즈는 완전한 Cost Base 라고 볼수 없고 NL 조인을 선호하는 Rule 이
포함될수 밖에 없다.
10g Default 로 all_rows 이며 부분범위 Rule 이 적용이 배제된 완전한 CBO 모드인 ALL_ROWS 에서 어처구니 없이 CBO 가 COST 가 높은 JOIN 을 선택하는것을 볼수 있다.
물론 TEMP_DEPT 테이블에 PK 혹은 UK를 만들면 이런현상은 사라진다.
하지만 현실(실제 프로젝트)에서는 이러한 PK 없는 임시작업 테이블들을 많이 사용하고 있다.
이런경우 CBO 가 항상 낮은 COST 를 선택하는 것은 아니므로 임시 테이블이나 global temp table 등을 사용시에는 주의할 필요가 있다.

오라클 White Paper 에 대하여

튜닝 관련 오라클 문서(White Paper)를 소개한다.
아래에 링크된 모든문서는 Oracle 10g 관련 문서이다.

이중에서 가장 추천할만한 문서는 Query Optimization in Oracle Database10g Release 2 이다.
특히 이문서에는 Query Transformation 에 대하여 비교적 자세히 나와있다.
Query Transformation 은 크기 2가지로 나눌수 있다

1) Cost Base transformations(비용기반) :
-Materialized view rewrite
-OR-expansion --> inlist 로 인해서 활용범위가 줄어들긴 하였다.
-Star transformation --> DW나 DSS 에서만 사용하는것이 아니다.
테스트 결과 대용량 배치에서 CODE 테이블 부터 엑세스 하여 GROUP BY , COUNT, SUM 하는경우 Star transformation을 사용하면 HASH 조인에 비해서 2배이상의 성능 차이가 난다.
Star 조인에 배해서도 1.5 배나 빨랐다.
-Predicate pushdown for outer-joined views.

2) Heuristic query transformations(전통적인 방식) :
-View Merge
-SubQuery Unnesting
-Transitive predicate generation
-Common subexpression elimination
-Predicate pushdown and pullup
-Group pruning for “CUBE” queries
-Outer-join to inner join conversion
위의 두가지 주제에 대하여 관심이 있으면 해당 백서를 꼭읽어보기 바란다.

하위버젼에서 Oracle 10g 로 업그레이드 하는경우 성능 이슈가 존재할수 있는데
이를위한 문서가 2가지 이다.
1.Migrating to the Cost-Based Optimizer
2.Upgrading from Oracle Database 9i to 10g-What to Expect from the Optimizer

DW 혹은 대용량 배치 SQL 관련 튜닝은 아래의 문서를 참조하면된다.
1.DSS Performance in Oracle Database 10g Release 1
2.DSS Performance in Oracle Database 10g Release 2

Sort 나 Group by 관련 문서는 아래문서를 참조하기 바란다.
1.Sort Performance Improvements in Oracle Database 10 Release 2
2.SQL Aggregation for Business Intelligence in Oracle Database 10g

파티션 관련 문서도 2가지이다.
1.Oracle Partitioning 10gR2
2.Partitioning in Oracle Database 10g

위문서 이외에도 SQLAccess Advisor나 CDC, MVIEW, HP Machine 관련한 흥미 진진한 문서들이 아래에 링크되어 있다.

백서의 매력은 네가지로 요약할수 있다.
1.주제가 한가지로 한정되어 있으므로 한가지 주제에 대하여 깊게 파고들수 있다.
2.매뉴얼에 없는 비급(?) 이 가끔 소개된다.
3.장수가 적다(보통 20~40 페이지로 끝난다)
4.백서는 오류가 거의 없다는점(가끔 오라클 매뉴얼도 오류가 있음)
시간이 나는대로 백서를 참조하도록 하자.

DSS Performance in Oracle Database 10g Release 1 :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/379013.PDF

DSS Performance in Oracle Database 10g Release 2 : http://www.oracle.com/wocportal/pls/wocprod/docs/1/1306255.PDF

Key Data WarehousingFeatures in Oracle10g: A Comparative Performance Analysis : http://www.oracle.com/wocportal/pls/wocprod/docs/1/1106757.PDF

Migrating to the Cost-Based Optimizer : http://www.oracle.com/wocportal/pls/wocprod/docs/1/1101645.PDF

Oracle Partitioning 10gR2: http://www.oracle.com/wocportal/pls/wocprod/docs/1/1185109.PDF

Partitioning in Oracle Database 10g :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1065154.PDF

Performance Tuning Using SQLAccess Advisor: http://www.oracle.com/wocportal/pls/wocprod/docs/1/450780.PDF

Query Optimization in Oracle Database10g Release 2:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1194820.PDF

Sort Performance Improvements in Oracle Database 10 Release 2 : http://www.oracle.com/wocportal/pls/wocprod/docs/1/1214088.PDF

SQL Aggregation for Business Intelligence in Oracle Database 10g : http://www.oracle.com/wocportal/pls/wocprod/docs/1/1230066.PDF

Table Compression in Oracle Database 10g Release 2:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1310336.PDF

Upgrading from Oracle Database 9i to 10g-What to Expect from the Optimizer : http://www.oracle.com/wocportal/pls/wocprod/docs/1/14421415.PDF

Change Data Capture Cookbook : http://www.oracle.com/wocportal/pls/wocprod/docs/1/4983723.PDF

Asynchronous Autolog CDC Cookbook : http://www.oracle.com/wocportal/pls/wocprod/docs/1/4995974.PDF

On-Time Data Warehousing with Oracle Database 10g:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/469428.PDF

Oracle Database 10g Materialized Views : http://www.oracle.com/wocportal/pls/wocprod/docs/1/1101619.PDF

Oracle Materialized Views & Query Rewrite:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1174608.PDF

HP Integrity Superdome Cluster with Oracle Database 10: http://www.oracle.com/wocportal/pls/wocprod/docs/1/1015875.PDF

HP ProLiant Cluster with Oracle Database 10g and Oracle:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1015835.PDF