Science of Database

지난시간의 DEUI라는 기능에 이어서 이번시간에는 그 사촌격인 DE에 대해서 논의해보자.

DE (Distinct Elimination)란 무엇인가
DE는 Unique한 집합일 경우 불필요한 Distinct를 제거하는 기능이다. 이렇게 함으로써 Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다. 이 기능은 Oracle 11g에서 추가되었다. 이제 DE가 어떻게 수행되는지 알아보자.

----------------------------------------+-----------------------------------+

| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |

----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |           |       |       |     3 |           |

| 1   |  NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 2   |   TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 3   |   INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

----------------------------------------+-----------------------------------+

Predicate Information:

----------------------

3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Unique를 보장하는 컬럼이 있으면 Distinct가 필요없다
실행계획에서 Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다. 이유는 Transformer가 위의 SQL을 분석해서 Distinct가 없어도 Unique 함을 알았기 때문이다. Select 절에 있는 d.department_id와 l.location_id는 From 절에 있는 두 테이블의 PK 이다.  따라서 Distinct는 당연히 필요 없음으로 Logical Optimizer가 삭제한 것이다.

아래는 DE와 관련된 10053 Event의 Trace 내용이다.
 

OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************

Order-by elimination (OBYE)

***************************

OBYE:     OBYE bypassed: no order by to eliminate.

Eliminated SELECT DISTINCT from query block SEL$1 (#0)

…이후생략

INDEX UNIQUE SCAN의 비밀
당신은 INDEX UNIQUE SCAN을 쉬운 Operation쯤으로 여길 것이다. 하지만 여기에는 숨겨진 기능이 있다. 대표적인 경우가 Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation이 나오면 Distinct를 제거하는 기능이다. 이 기능의 이름이 없으므로 DEUI*(Distinct Elimination using Unique Index)라 부르기로 하자. 이제 아래의 SQL을 보자.

--------------------------------------------------+-----------------------------------+

| Id  | Operation                     | Name      | Rows  | Bytes | Cost  | Time      |

--------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT              |           |       |       |     3 |           |

| 1   |  NESTED LOOPS                 |           |     1 |    22 |     2 |  00:00:01 |

| 2   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENT|     1 |     7 |     1 |  00:00:01 |

| 3   |    INDEX UNIQUE SCAN          | DEPT_ID_PK|     1 |       |     0 |           |

| 4   |   TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

| 5   |    INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

--------------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

3 - access("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

SQL이 변경되었다
Distinct에 의한 Sort Unique 혹은 Hash Unique가 사라졌다. 그 이유는 테이블 department와 location에서 모두 INDEX UNIQUE SCAN을 사용하였기 때문이다. 하지만 반대로 Unique 인덱스를 사용하지 않는다면 DEUI는 절대 수행되지 않는다. 아래의 SQL은 위의 SQL에서 힌트만 추가한 것이다.
 

---------------------------------------------------+-----------------------------------+

| Id  | Operation                      | Name      | Rows  | Bytes | Cost  | Time      |

---------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT               |           |       |       |     5 |           |

| 1   |  HASH UNIQUE                   |           |     1 |    22 |     5 |  00:00:01 |

| 2   |   NESTED LOOPS                 |           |       |       |       |           |

| 3   |    NESTED LOOPS                |           |     1 |    22 |     4 |  00:00:01 |

| 4   |     TABLE ACCESS FULL          | DEPARTMENT|     1 |     7 |     3 |  00:00:01 |

| 5   |     INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

| 6   |    TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

---------------------------------------------------+-----------------------------------+

Predicate Information:

----------------------

4 - filter("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

이번 내용은 난위도가 있으므로  'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼
사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다.

Interleaving은 선수조건이 필요하다
하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. 즉 CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다.

숫자표기법의 이해
Interleaving 에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 과 1 로 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting 됨
CSU(0) : 서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2개(SUBQ2, SUBQ1)라면 아래처럼 표현된다.
CSU(1,1) : 모든 서브쿼리가 Unnesting 됨
CSU(1,0) : SUBQ2만 Unnesting 됨
CSU(0,1) : SUBQ1만 Unnesting 됨
CSU(0,0) : 모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다.

Interleaving의 용도
Interleaving은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving은 CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */ 

       e1.employee_id, e1.manager_id, e1.salary

  FROM employee e1

 WHERE e1.department_id = 10

     and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

                                             e2.manager_id,  max(e2.salary) 

                                        from employee e2

                                       group by e2.manager_id )

     and rownum = 1

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM및 JPPD의 Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다.

CVM과 JPPD 는 동시에 고려된다
CVM 과정이 끝나면 JPPD의 Costing 과정이 진행된다. Interleaving이 CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) 의 Cost가 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration의 Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010은 JPPD의 Cost가 되는 것이 아니라 CSU의 Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. 즉 CSU의 Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.

첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL 튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

Index Scan       : 인덱스 사용

Full Table Scan  : 인덱스 미사용

Rowid            : Rowid로 테이블 엑세스

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.

Rowid

Index Unique Scan    

Index Range Scan

Index Inlist Iterator

Index Skip Scan 

Index Full Scan

Index Fast Full Scan 

Bit Map Index Combination

Full Table Scan        

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

Rowid

Index Unique Scan    

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)    

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan 

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending 

Index Full Scan (min/max)   

Index Fast Full Scan 

BIT MAP OR           

BIT MAP AND          

BIT MAP MINUS        

BIT MAP MERGE

Full Table Scan        

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우  출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

invalid-file

invalid-file

invalid-file

PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

결론:
실제로 SQL 튜닝 등으로 성능향상이 불가능한 경우는 당신이 생각하는 것 보다 훨씬 많다.
이럴 경우 화려한 튜닝 테크닉으로 문제를 해결을 시도하는 사람, 대화와 설득으로 해결하는 사람 누가 더 뛰어난 튜너인가?

예전에 Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 서브쿼리가 Semi Join 으로 적용될때 조인의 순서를 바꿀수 있는 방법에 대하여 설명한바 있다. 이어서 오늘도 예전글과 유사한 Semi Join 의 튜닝에 대해서 이야기 하려 한다.

환경 : Oracle 11.1.0.6
먼저 테이블을 단순한 구조로 만들기 위해서 테이블을 2개 생성한다.
ORACLE 의 Sample 스키마인 SH 를 이용한다.

이제 SQL 을 실행할 준비가 다 되었다.
아래는 상품의 Category 가 Software/Other 인 제품의 상품코드별, 채널별 판매량을 나타낸 SQL 이다.
이러한 SQL 은 주로 DW 에서 나타난다.

--------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts |   A-Time   | Buffers |
--------------------------------------------------------------------------
|   1 |  HASH GROUP BY      |            |      1 |00:00:08.12 |    4445 |
|*  2 |   HASH JOIN         |            |      1 |00:00:06.94 |    4445 |
|*  3 |    TABLE ACCESS FULL| PRODUCTS_T |      1 |00:00:00.01 |       4 |
|   4 |    TABLE ACCESS FULL| SALES_T    |      1 |00:00:01.84 |    4441 |
--------------------------------------------------------------------------

헉 8초 이상 걸렸다!
Subquery Unnesting 이 발생하여 작은쪽 테이블인 Products_t 가 Driving 테이블로 선정되었고 Salse_t 테이블과 Hash Join 을 하게된다. Scan 한 블럭수는 4445블럭 이며 8초 이상 수행되었다. 조인건수(Starts 항목 참조)는 1회에 불과하다. 이것은 최적으로 튜닝된 SQL 인가?  이것을 알아보기 위해 Hash Join 대신 Nested Loop 조인으로 바꿔보자.

원본 SQL 의 경우 왠만해서는 Nested Loop Join 으로 바뀌지 않으므로 강제로 서브쿼리를 From 절로 끌어 올려서 Nested Loop Join 으로 유도 하였다.

------------------------------------------------------------------------------------
| Id |Operation                       |Name            |Starts|   A-Time   |Buffers|
------------------------------------------------------------------------------------
|  1 | HASH GROUP BY                  |                |    1 |00:00:10.07 |   3099|
|  2 |  VIEW                          |                |    1 |00:00:08.92 |   3099|
|  3 |   NESTED LOOPS                 |                |    1 |00:00:07.70 |   3099|
|  4 |    NESTED LOOPS                |                |    1 |00:00:02.03 |     41|
|* 5 |     TABLE ACCESS FULL          |PRODUCTS_T      |    1 |00:00:00.01 |      4|
|  6 |     BITMAP CONVERSION TO ROWIDS|                |   26 |00:00:00.41 |     37|
|* 7 |      BITMAP INDEX SINGLE VALUE |SALES_T_PROD_BIX|   26 |00:00:00.01 |     37|
|  8 |    TABLE ACCESS BY INDEX ROWID |SALES_T         |  405K|00:00:02.45 |   3058|
------------------------------------------------------------------------------------ 

Hash Join 보다 더 느려졌다
Nested Loop Join 으로 유도 하자 인덱스는 제대로 사용하였지만 비효율이 극심하게 드러난다. 먼저 Nested Loop Join 이 40만번 이상 수행된다.(Starts항목 참조) 그결과로 View 를 만드는데 8초 이상 수행되었다. Buffer 가 30% 이상 줄어들었지만 조인건수 때문에 시간이 많이 걸리므로 이것은 견딜수 없는 결과이다.

그럼 Hash Join 에 만족해야 하나?
지금까지의 상식으로는 그렇다. 하지만 Driving Semi Join 기법을 사용한다면 생각이 달라질것이다.

Driving Semi Join 이란?
Driving Semi Join 이란 Semi Join 의 변형된 형태로서 Hash Join 의 장점(조인건수의 최소화)과 Nested Loop Join 의 장점(선행테이블이 상수화 되어 후행 테이블에서 인덱스를 효율적으로 사용) 을 합친 개념이다. 물론 Driving Semi Join 이라는 Title 자체의 의미대로 Semi Join 이 Driving 집합이 되는것은 당연하다. 다음의 SQL 을 보자  

Query Transformer 의 냉대
  튜닝을 하는 많은 사람들이 PM 의 개념을 모른다는 결과가 나왔다. 정기모임 술자리에서 즉석으로 설문을 하였는데 결과는 충격적이었다. 참석자 10명은 DBA, 튜너, DB 컨설턴트 등등 DB 전문가들의 모임이라고 할수 있는데 단 한명도 아는사람이 없었다.

 필자가 충격적이라고 한 이유는 그모임의 많은 사람들이 왠만한 SQL 의 COST 를 계산할수 있는 내공을 가진 사람들이 었기 때문이다. 다행히 JPPD 나 VIEW MERGING, Unnesting 과 같이 튜닝 책에 소개 되는 간단한 변환들은 알고 있었다. 하지만 Query Transformer 가 푸배접을 받고 있다는 생각은 지울수가 없었다.
 
  Query Transformer 는 그 중요성이 옵티마이져의 50% 를 차지한다. 왜그럴까? 옵티마이져의 3대 Components 는 Query Transformer, Cost Estimator,  Plan Generator  이지만  이중에서 우리가 연구할수 있는 것은  Query Transformer 와  Cost Estimator 이며  Plan Generator 의 비밀은 오라클사의 DBMS 설계자/개발자만이 알수 있는 영역이기 때문이다. 또한 SQL 이 Transformer 에 의하여 변형되고 재작성 되기 때문에 성능에 직접적인 영향을 끼친다. 대부분의 경우 Query Transformation 이 발생하면 성능에 긍정적인 영향을 끼치지만 예외적으로 부정정인 영향을 줄수가 있으므로 가능한 Query Transformer에 대하여 상세히 알아야 한다.

어찌되었건 모임에서 PM 관련 내용을 블로그에 올리겠다는 약속을 하였다.

PM ( Predicate Move Around ) 이란?
 
인라인뷰가 여러 개 있고 각각의 where 절에 공통적인 조건들이 있다고 가정하자.
이럴경우에 모든 인라인뷰의 where 절에 똑 같은 조건들을 반복해서 사용해야 할까?
물론 그렇게 해야 하는 경우가 있지만 아래의 경우에는 그렇지 않음을 알수 있다..

결론 :
  제목에서 보듯이 PM 의 개념은 매우 간단하다. Where 조건을 다른뷰에 이동시키는 기능이며 Heuristic Transformatin 의 대표적인 예제이다.
  오늘 올린 글은 현재 집필중인 책의 내용인데 일부를 먼저 공개하기로 결정 하였다.

편집후기 :  JPPD 와 PM 이 헷갈린다는 보고가 들어왔다. 둘다 WHERE 조건이 PUSH 되는것이지만 가장 결정적이 차이점은 JPPD 는 Predicate pull up 기능이 없다는 것이다. 아주 명확하게 구분할수 있다.

 

  대부분의 TOP SQL 은 ROWNUM 을 이용하여 부분범위처리 하는 개념임을 알것이다.
하지만 RANK 등의 분석함수를 이용하여 TOP 처리를 하는경우가 많이 있다.
이런경우에 대부분의 개발자및 DBA 들이 범하는 오류가 있으므로 오늘은 그부분을 바로잡고자 한다.
환경은 Oracle 10.2.0.4 이며 모든 예제는 오라클의 SH 스키마의 SALES 테이블을 이용한다.

들어가며
  ROWNUM 을 이용한 TOP SQL과 분석함수를 이용한 것의 다른점이 무엇인가?.
분석함수를 이용하면 1등이 여러명일때도 처리가 가능 하지만 ROWNUM 을 이용할경우 단한건만 가져오므로 처리가 불가능하다는 것이다.  아래는 그러한 경우에 분석함수를 하여 간단하게 해결한 경우이다.

먼저 매출액기준으로 순위를 만든후에 인라인뷰의 밖에서 FILTER 하는 형태의 SQL 이다.   
금액기준으로 가장 많이 팔린 상품과 해당고객 그리고 해당매출일자 등을 SELECT 하고 있다.

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |      1 |    108 |00:00:01.09 |    1718 |          |
|*  1 |  VIEW                    |       |      1 |    108 |00:00:01.09 |    1718 |          |
|*  2 |   WINDOW SORT PUSHED RANK|       |      1 |    109 |00:00:01.09 |    1718 |   45M (0)|
|   3 |    PARTITION RANGE ALL   |       |      1 |    918K|00:00:00.01 |    1718 |          |
|   4 |     TABLE ACCESS FULL    | SALES |     28 |    918K|00:00:00.01 |    1718 |          |
----------------------------------------------------------------------------------------------
 
A-Rows 항목을 보면 1등이 108 건임을 알수 있다.
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)
 
울며 겨자먹기
  약 91만건을 full scan 하였으며 일량(Buffers)과 메모리 사용량(Used-Mem)이 엄청나다.
분명히 문제가 있다.
분석함수로 TOP SQL 을 구현할경우 이와 관련된 일반적인 3가지 미신(오류)이 있다.

첫번째 오류로는 일반적으로 금액컬럼에 인덱스를 만들지 않는다는 것이다.

두번째 오류로는 금액컬럼에  인덱스를 만들어도 분석함수를 사용하였으므로 인덱스를 타지 않는다는 것이다.
(참고로 분석함수로는 Function Based Index를 만들수 없다.)

세번째 오류가 가장 심각하다.
인덱스를 사용하지 못할 바에야 전체건을 sort 해야하므로 울며 겨자먹기로 순위컬럼을 모델에 추가하고 그컬럼에 인덱스를 생성하며 데이터가 바뀔때마다 실시간으로 순위컬럼에 UPDATE 하는방식을 많이 사용하고 있다.
이런것을 추출속성이라고 하는데 정합성을 해칠수 있으므로 물리모델링시에 최소화 시켜야 한다.  
그렇다면 이러한 문제를 어떻게 풀수 있을까?

먼저 AMOUNT_SOLD에 인덱스를 하나 만든다.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      11 |
|*  1 |  VIEW                                |             |      1 |    108 |00:00:00.01 |      11 |
|*  2 |   WINDOW NOSORT STOPKEY              |             |      1 |    108 |00:00:00.01 |      11 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    109 |00:00:00.01 |      11 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    109 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)
 
 
해결책은 인덱스 생성
  금액 컬럼으로 인덱스를 하나 만들었을 뿐인데 성능과 정합성이라는 두가지 이슈를 모조리 해결 하였다.
일량이(Buffers ) 170 배정도 차이가 나며 메모리 사용은 하지도 않는다.
분석함수 사용시 인라인뷰의 바깥에서 filtering 시의 비밀은 3가지로 요약할수 있다.

1.해당컬럼 컬럼에 인덱스가 있을경우 rownum 과 같이 Stop Key Operation 이 발생 한다는 것이다.

2.Predicate Information 을 보면 뷰를 만들기 전에 filter 하는 것을 볼수 있다.(id 기준으로 2번)
  이때 인덱스를 사용하는 것이다.

3.id 기준으로 2번 Operation 을 보면 WINDOW NOSORT ~ 가 있다.
이것은 인덱스가 이미 정렬이 되어있기 때문에 Sort 가 필요없다는 뜻이다.

마치 Sort Group By 시에 해당컬럼이 인덱스로 생성되어 있을경우 NOSORT 가 나오는 원리와 같다.  
하지만 인덱스가 없으면 Full Table Scan + filter 만 일어난다. 이때 WINDOW SORT PUSHED RANK Operation 이 발생하게 된다.(가장 위의 인덱스가 없을때의 plan 을 참조 할것)

ROWNUM VS RANK
  WINDOW NOSORT STOPKEY 사용시 PLAN 을 보면 1건 더 SCAN했음을 알수 있다.
즉 1위그룹은 108 건  이지만 109 건을 scan 하였고 WINDOW FILTER 과정에서 1건이 제거되었다.
하지만 ROWNUM 을 이용한다면 어떻게 될까?
아래는 ROWNUM 으로 108 건(1위그룹의 전체건수)을 가져오는 예제이다.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      10 |
|*  1 |  COUNT STOPKEY                       |             |      1 |    108 |00:00:00.01 |      10 |
|   2 |   VIEW                               |             |      1 |    108 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    108 |00:00:00.01 |      10 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    108 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<:V_RANK)
  
정확히 108 건만 SCAN 했음을 알수 있다.
이것이 ROWNUM 과의 차이이다. ROWNUM 은 정확히 건수를 잘라내지만 WINDOW STOP KEY 는 한건을 더읽어야만 한다.
하지만 이정도(1건)의 비효율은 눈감아 줄수 있다. 하지만 아래의 경우는 비효율이 심하므로 반드시 피해야 한다.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      13 |
|*  1 |  VIEW                                |             |      1 |    108 |00:00:00.01 |      13 |
|*  2 |   WINDOW NOSORT STOPKEY              |             |      1 |    115 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    116 |00:00:00.01 |      13 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    116 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<:V_RANK)

RANK <= 1과 RANK < 2 는 결과는 같다. 하지만 성능은?
  WHERE 절에서 = 을 제거하고 < 로 바꾸었으므로 :V_RANK 변수에 2 를 대입 해야 같은결과를 넏을수 있다.
결과는 <= 를 사용한 것과 같지만 일량(Buffers)이 11 에서 13 으로 늘었다.
이것또한 분석함수를 이용한 TOP SQL 처리시 기억해야할 특징중의 하나인데 :V_RANK 변수에 2를 대입 하면 2위그룹까지 SCAN 한다.(정확히 말하면 3위그룹의 첫번째 ROW 까지 SCAN 한다.)
따라서 WINDOW STOPKEY 사용시에 = 를 생략하면 안된다.
 
결론:
여러 사이트에서 분석함수에 관한 몇가지 오해 때문에 성능이 나빠짐은 물론이고 정합성이 위배될수도 있는 일들이 벌어지고 있다.
두가지만 기억하자.
1.분석함수로 TOP SQL 처리시 반드시 인덱스를 만들것. (빠른성능을 원한다면)
2.RANKING 비교시 <= 를 사용할것.

편집후기 : 주의사항이 있으므로 반드시 댓글을 참고할것.

  한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다.
물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166)
   이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은
buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.

  이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다.
먼저 인덱스를 하나 만든다.

 이어서 buffer cache 를 비우고 SQL 을 실행한다.
고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.

Oracle 8i Plan --> just normal
------------------------------------------------
| Id  | Operation                              |
---------------------------------------------- |
|   1 |  SORT AGGREGATE                        |
|   2 |   VIEW                                 |
|   3 |     NESTED LOOPS                       |
|   4 |      TABLE ACCESS FULL                 |
|   5        TABLE ACCESS BY GLOBAL INDEX ROWID|
|   6 |        INDEX RANGE SCAN                |
------------------------------------------------

특별 할것 없는 전통적인 Nested Loop Join 이다.
이제 9i 및 10g 의 plan 을 보자.

9i & 10g Plan --> table prefetch
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |               |      1 |00:00:05.67 |     245K|   1454 |
|   2 |   VIEW                               |               |   8269 |00:00:05.66 |     245K|   1454 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:05.62 |     245K|   1454 |
|   4 |     NESTED LOOPS                     |               |    327K|00:00:02.83 |   41304 |   1454 |
|   5 |      TABLE ACCESS FULL               | CUSTOMERS     |  20010 |00:00:00.12 |    1457 |   1454 |
|   6 |      INDEX RANGE SCAN                | SALES_CUST_IX |    307K|00:00:00.88 |   39847 |      0 |
-------------------------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_PREFETCH(@"SEL$2" "S"@"SEL$2")
      END_OUTLINE_DATA
  */ 

Oracle 9i 에서 table prefetch 기능이 나오다.
  Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로
NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까?
이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다.
여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다.
어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함)  바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)

이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.

NAME                                           DIFF
---------------------------------------- ----------
undo change vector size                        2840
physical read IO requests                      3812
... 중간생략                                      
physical reads cache prefetch                  1344

위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다.  9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.

11g Plan --> Index Vector I/O
------------------------------------------------------------------------------------------------------
|Id  | Operation                             | Name          | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------
|  1 |  SORT AGGREGATE                       |               |      1 |00:00:04.82 |     245K|   1454|
|  2 |   VIEW                                |               |   8269 |00:00:04.81 |     245K|   1454|
|  3 |    NESTED LOOPS                       |               |   8269 |00:00:04.79 |     245K|   1454|
|  4 |     NESTED LOOPS                      |               |    307K|00:00:01.56 |   41304 |   1454|
|  5 |      TABLE ACCESS FULL                | CUSTOMERS     |  20010 |00:00:00.08 |    1457 |   1454|
|  6 |      INDEX RANGE SCAN                 | SALES_CUST_IX |    307K|00:00:00.47 |   39847 |      0|
|  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:01.93 |     203K|      0|
------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_BATCHING(@"SEL$2" "S"@"SEL$2")
      END_OUTLINE_DATA
  */

궁하면 통한다.
이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다.
이러한 현상이 의미하는 바는 무엇일까?
9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다.
참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.

그러면 11g 의 버젼에서 v$sesstat 통계를 보자.

NAME                                           DIFF
---------------------------------------- ----------
Batched IO vector block count                  3758
Batched IO vector read count                     50
... 이후 생략

위에서 보듯이 Batched IO 란것이 생겼다.
Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.

주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.

결론:
  오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다.
첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation의 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.

  앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다.
조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?

<편집후기 : 위 테스트는 11g 에서 테스트 되었음을 다시한번 밝혀둡니다.>

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|*  1 |  FILTER                       |           |      1 |      5 |00:00:00.01 |      16 |
|   2 |   TABLE ACCESS FULL           | EMP       |      1 |     14 |00:00:00.01 |       8 |
|   3 |   SORT AGGREGATE              |           |      5 |      5 |00:00:00.01 |       8 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP       |      5 |     13 |00:00:00.01 |       8 |
|*  5 |     INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">)
   5 - access("INNER"."DEPTNO"=:B1)

전통적인 Filter Subquery(확인자 SubQuery) 이다.


2.단계 : 서브쿼리를 인라인뷰로 바꿔라.
 이 단계에서 unnest 힌트를 사용함으로서 Subquery 가 인라인뷰로 바뀌며 서브쿼리가 없어진다. 이때 메인쿼리의 건수를 유지하기 위해 인라인뷰에 group by 가 추가된다.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP       |      1 |      5 |00:00:00.01 |      16 |          |
|   2 |   NESTED LOOPS              |           |      1 |     19 |00:00:00.09 |      10 |          |
|   3 |    VIEW                     | VW_SQ_1   |      1 |      5 |00:00:00.01 |       7 |          |
|   4 |     HASH GROUP BY           |           |      1 |      5 |00:00:00.01 |       7 | 1622K (0)|
|   5 |      TABLE ACCESS FULL      | EMP       |      1 |     14 |00:00:00.01 |       7 |          |
|*  6 |    INDEX RANGE SCAN         | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       3 |          |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">"VW_COL_1")
   6 - access("DEPTNO"="OUTER"."DEPTNO")
       filter("OUTER"."DEPTNO" IS NOT NULL)  

이것은 Optimizer가 쿼리를 아래처럼 변형시킨것이다.

메인쿼리의 결과집합을 보존하기위하여 rowid 로 Group by 를 한것에 유의하자.
두개의 Query  Plan 은 동일하며 아래와 같다.
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      5 |00:00:00.01 |      12 |          |
|   2 |   HASH GROUP BY                |           |     13 |00:00:00.01 |      12 | 1103K (0)|
|   3 |    MERGE JOIN                  |           |     51 |00:00:00.01 |      12 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP       |     13 |00:00:00.01 |       5 |          |
|*  5 |      INDEX FULL SCAN           | IX_EMP_N3 |     13 |00:00:00.01 |       1 |          |
|*  6 |     SORT JOIN                  |           |     51 |00:00:00.01 |       7 | 2048  (0)|
|*  7 |      TABLE ACCESS FULL         | EMP       |     13 |00:00:00.01 |       7 |          |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   5 - filter("INNER"."DEPTNO" IS NOT NULL)
   6 - access("INNER"."DEPTNO"="OUTER"."DEPTNO")
       filter("INNER"."DEPTNO"="OUTER"."DEPTNO")
   7 - filter("OUTER"."DEPTNO" IS NOT NULL)
 
 드디어  1~3 단계에 걸친 Query Transformation 단계가 완성 되었다. 그결과는 성능면에서 대성공이다. Buffers(읽은 Block수) 가 16(원본쿼리) 에서 12 로 약 25% 감소했다.

오라클 트랜스포머는 악성쿼리와 싸워...
  오라클 Query Transformer 는 SQL 을 멋지게 변화시켰다. 이모든 과정을 개발자가 해야한다고 상상해보자.
개발자들에게 전체과정을 이해시키는 교육과정이 추가되어야 하고 개발속도는 몇배나 느려질것이다. 이는 프로젝트의 Risk 가 될것이다. 하지만 오라클 Query Transformer 가 있으므로 악당 로봇이 아닌 악성쿼리와 멋지게 싸워서 이길수 있는 것이다.

편집후기 :
  Query Transformation 을 하려면 반드시 unnesting 이나 merge 힌트를 써야 하는지 질문이 들어왔다. 대부분의 경우 Query Transformer 가 자동으로 변환과정을 수행해준다. 하지만 이것이 가끔 제대로 수행이 안될수 있으므로 이럴경우에만 명시적으로 힌트를 사용하는것이 바람직하다.  

PAGE 처리 할때나 아니면 부분범위 처리를 할때 우리는 TOP SQL 을 많이 사용한다.
오늘은 rownum 을 이용한 TOP N건 처리시 몇가지 함정이 있으므로  정확한 사용법에 대해서 알아보고자 한다.
먼저  환경을 만들기 위하여 테이블및 인덱스를 생성 한다.
환경 : ORACLE 11.1.0.6

 EMP 와 DEPT 를 이용하여 테이블및 인덱스를 생성 하였고 new_dept 테이블에는 부서별로 PAGE COUNT 라는 컬럼이 추가적으로 생성 되었다.
아래의 SQL 을 보자.

20번 부서에 해당하는 사원중에서 급여가 큰순으로 20건만 보겠다는 의미이다.

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |      1 |     20 |00:00:00.01 |       8 |
|   2 |   VIEW                          |             |      1 |     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |      1 |     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------ 

위 PLAN 상의 A-Rows 에 나타나듯이 부서번호 20번에 해당하는 모든건을 엑세스및 조인한것이 아니라
 정확히 BIG_EMP 쪽을 20 건만 scan 하였다.
이것이 COUNT STOPKEY 의 위력이다.
즉 BIG_EMP 테이블에 deptno = 20 조건을 만족하는 건이 아무리 많더라도 20 건만 읽고 끝낸다는 것이다.

 하지만 rownum < :v_page_count 조건대신에 부서별로 PAGE COUNT 를 테이블에 저장 하고
그 값을 이용하여 TOP SQL 을 작성 해야 한다면 이야기가 달라진다.
아래의 SQL 을 보면 부서별로 PAGE COUNT 를 지정 하고 있다.

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  COUNT                           |             |      1 |     20 |00:00:00.01 |    6269 |
|*  2 |   FILTER                         |             |      1 |     20 |00:00:00.01 |    6269 |
|   3 |    VIEW                          |             |      1 |  10000 |00:00:00.09 |    6269 |
|   4 |     NESTED LOOPS                 |             |      1 |  10000 |00:00:00.07 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |  10000 |00:00:00.04 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |  10000 |00:00:00.01 |      29 |
-------------------------------------------------------------------------------------------------
 
 어이 없게도 20번 부서에 해당하는 전체건(10000 건)을 다 SCAN 하였다.
COUNT STOPKEY Operation 대신에 FILTER Operation 이 사용되었기 때문이다.
아래처럼 ROWNUM 조건을 인라인뷰 내로 밀어 넣어도 결과는 마찬가지 이다.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  VIEW                            |             |  10071 |  7898   (1)|     20 |00:00:00.01 |    6269 |
|   2 |   COUNT                          |             |        |            |     20 |00:00:00.01 |    6269 |
|*  3 |    FILTER                        |             |        |            |     20 |00:00:00.01 |    6269 |
|   4 |     NESTED LOOPS                 |             |  10071 |  7898   (1)|  10000 |00:00:00.11 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|  10000 |00:00:00.08 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|  10000 |00:00:00.05 |      29 |
--------------------------------------------------------------------------------------------------------------
 

그렇다면  이런 경우에 어떻게 TOP 처리를 할수 있겠는가?
이 문제는 ROWNUM 에 대한 기본을 알면 해결된다.
TOP N건 처리시에 ROWNUM 과의 비교대상은 반드시 상수(혹은 변수) 이어야 한다. 
그렇지 않다면 TOP 처리는 실패한다.
아래의 SQL은 이 문제를 해결하였다.

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |        |            |     20 |00:00:00.01 |      10 |
|   2 |   VIEW                          |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|     20 |00:00:00.01 |       3 |
|   8 |   TABLE ACCESS BY INDEX ROWID   | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  9 |    INDEX UNIQUE SCAN            | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------

 PLAN 에서 보는것과 같이 서브쿼리를 사용하여 PAGE COUNT 를 상수화 하였다.
즉  rownum <= (서브쿼리) 형태가 되면 COUNT STOPKEY Operation 을 정상적으로 사용할수 있다.
"같은 테이블을 반복하여 읽지마라." 라는 튜닝의 기본적인 원칙이 깨지는 순간이다.
하지만 모든 규칙은 예외가 있음을 기억해야 한다.

결론 :
위에서 보는 바와 같이 COUNT STOPKEY 와 FILTER Operation 은 천당과 지옥의 차이이다.
몇천만건 혹은 몇억건 이상인 환경에서  FILTER  Operation 이 나온다면?
더이상 그 프로그램은 사용할수 없을것이다.
TOP SQL 처리시 주의사항은 ROWNUM 과 비교시 항상 상수(혹은변수)와 비교하거나 서브쿼리등을 이용하여 상수화 한후에 비교 하여야 한다는 것이다.

최근의 많은수의 사람들이 DBMS_XPLAN 패키지를 사용하여 튜닝을 하고 있다.
필자는 DBMS_XPLAN 패키지에 대한 너무많은 질문공세 때문에 아예 블로그에 올릴 결심을 하였다.
오늘은 DBMS_XPLAN 패키지에 대한 첫번째 이야기로 가장중요한 실행계획에 대하여 조목조목 따져보려고 한다.

DBMS_XPLAN 패키지는 9i 부터 점점 발전하여 지금은 Trace + tkprof 보고서와 자웅을 겨룰 정도로 발전하고
있다.
DBMS_XPLAN 패키지내의 함수는 10g R2 기준으로 6개 이지만 가장 자주 사용하는 함수는 아래의 3가지 이다.

1.DISPLAY                 --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR   --> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR         --> 실제 실행된 실행계획을 보여준다.

오늘의 주제는 실행계획상의 각항목에 대한 설명이므로 3개의 함수에 대한 자세한 설명은 다음에 계속하여
연재할 계획이다.

실행계획은 패키지 내의 3가지 함수(display, display_cursor, display_awr)를 통해 모두 조회가 가능하다.
아래의 스크립트는 display_awr 의 예제이며 sql_id 만 구하면 언제든지 실행될수 있다.

위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.
아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.
물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.
예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.

주의사항:

파랑색부분( 5), 6), 7)번 에해당됨)은 실행통계가 있을경우만 해당된다.

display_cursor, display_awr 의  경우 statistics_level 파라미터를 all 로 설정하거나 SQL 에 gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.

1)Basics 항목 (Always Available)

Id                :각 Operation 의 ID 임.

                  * 가 달려있는 경우는 predicate 정보에 access 및 filter 에 관한정보가 나옴을 표시한것임.

Operation  : 각각 실행되는 JOB 을 나타냄

                    row source operation.의 줄임말임.

Name          : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄.

2)Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)

Rows (E-Rows)  : 각 operation 이 끝났을 때 return되는 건수를 나타냄.

                              이것은 예측 건수 이므로 실제 건수와는 다름.

Bytes (E-Bytes) : 각 operation 이 return 한 byte 수.
                              예상치 이므로 실제 받은 byte와는 다름

TempSpc             : 각 operation. 이 temporary space 를 사용한 양(예상치임)

Cost (%CPU)      : 각 operation 의 Cost. (예상치 임)

                              괄호안의 내용은 CPU Cost 의 백분율임.

                               이값은 Child Operation 의 Cost 를 합친 누적치임.

Time                     : 예측 수행시간

3)Partitioning (파티션을 엑세스 할경우만 나타남)

Pstart  : 파티션을 엑세스 하는경우 시작파티션을 나타냄

             상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남

Pstop  : 마지막 파티션을 나타냄.

              따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음. 

4)Parallel and Distributed Processing (Parallel Processsing 을 사용하거나 DB-LINK 를 사용하는경우)

Inst         :  DB-LINK 명(사용하는 경우만 나타남).

TQ            :  PARALLEL SQL 사용시 table queue 명을 나타냄
                   TQ 는 PARALLEL SLAVE 간의 통신을 담당함.

IN-OUT      :  Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를
                     나타냄.

PQ Distrib :  Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.

* 이부분의 자세한 내용은 아래를 참조하기 바란다.
1.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution
2.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter 

Starts     : 각 operation 을 try 한 건수(예를 들어 nested loop join 이라면 인덱스를 여러 번 scan 함)

A-Rows  : 각 operation 이 return 한 건수

A-Time   : 실제 실행시간

0.1초까지 나타남 (HH:MM:SS.FF).

                 이값은 Child Operation 의 Cost 를 합친 누적치임.             
 

Buffers  : 각 Operation 이 메모리에서 읽은 block 수.

Reads   :각 Operation 이 disk 에서 읽은 block 수.

Writes   : 각 Operation 이 disk 에 write한 block 수.

OMem         : optimal execution 에 필요한 메모리(예측치임).

1Mem          : one-pass execution. 에 필요한 메모리(예측치임)

O/1/M        : 각 operation 이 실행한 optimal/one-pass/multipass 횟수가 순서대로 표시됨.

Used-Mem : 마지막 실행시의 사용한 메모리

Used-Tmp  : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.

                      보이는값에 1024 를 곱해야함.
                      예를들어 32K 로 나타나면 32MB 를 의미함.

Max-Tmp    : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사용량임.

                     USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에 
                     항상 최대값만 보인다는 것이다.
                     보이는값에 1024 를 곱해야함.
                     예를들어 32K 로 나타나면 32MB 를 의미함.

결론:
이상으로 PLAN 상에 나오는 각 항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계,  I/O 통계및 Memory 통계중의 일부항목은 Tkprof 보고서에도 나오지않는 정보들로
튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.  
다음시간에는 3가지 함수의 여러가지 옵션에 대하여 알아볼것이다.

Reference :
1.Ttroubleshooting Oracle Performance (Christian Antognini)
2.Oracle 10g Manual : PLSQL Packages and Types Reference

HASH 조인은 DW 뿐아니라 OLTP 의 배치업무및 심지어 OLTP 의 조회성 업무에까지 적용범위를
넓혀가고 있기 때문에 그중요성은 매우크다고 할수 있다.
한가지 아쉬운점은 개발자및 DBA, 튜너 들이 PLAN 을 보고 Driving 테이블만 제대로 나오면 검증하지 않고
그냥 넘어간다는 것이다.
좀더 꼼꼼한 튜너는 SWAP_JOIN_INPUTS 이나 LEADING, ORDERED 등의 힌트로 SIZE가 적은 집합순으로
HASH 조인을 하고 실행후 결과에서 모든 HASH 조인이 Optimal Pass(주1) 가 나오면 그것으로 튜닝을 끝낸다.
하지만 HASH 조인은 다른 조인들과 달라서 몇가지 더 검증해야될 부분이 있다.
오늘은 그중에 한가지에 대하여 소개한다.

실행환경 : Oracle 10g R2

아래는 테스트시 필요한 테이블과 인덱스 생성및 통계정보를 gathering 하는 스크립트 이다.

1.테스트 테이블및 인덱스 생성

3.Right Deep Tree, Left Deep Tree Plan 의 개념

먼저 테스트를 수행하기전에 간단하게 Right Deep Tree, Left Deep Tree Plan 에 대하여 알아보겠다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   TABLE ACCESS FULL  | T1   |
|*  3 |   HASH JOIN          |      |
|   4 |    TABLE ACCESS FULL | T2   |
|*  5 |    HASH JOIN         |      |
|   6 |     TABLE ACCESS FULL| T3   |
|   7 |     TABLE ACCESS FULL| T4   |
-------------------------------------

위와 같은 PLAN 을 오라클을 많이 사용하는 사람이라면 자주 보았을 것으로 예상한다.
아래로 내려갈수록 Operation 이 오른쪽으로 밀려난다.
이것이 Right Deep Tree Plan 이다.
필자는 Hash 조인을 사용하면서  T1 을 엑세스 할때 많은 양의 데이터가 filter 되는 경우 
Right Deep Tree Plan 이 나오면 일단 부정적으로 보고 튜닝을 시작한다.
왜냐하면 대부분의 경우 악성 plan 이기 때문이다.
위 plan 의 조인순서는 아래와 같다.
1. T3 와 T4를 조인한다.
2. T2 와 1번의 결과집합을 조인한다.
3. T1 과 2번의 결과집합을 조인한다.
따라서 실제 조인순서는 T3 --> T4 --> T2 --> T1 이지만 많은수의 개발자나 DBA 들은 이점을 놓치고 있다.
즉 T1 이 Driving 이면서 많은 양의 데이터가 filter 되는 경우 위와 같은 plan 이나오면 T1 과의 조인은
맨마지막에 실행되기 떄문에 T3 --> T4 --> T2 조인이 처리될때 까지 데이터의 범위를 줄일수가 없다.
다시말하면 전체건에 대하여 2번을 조인한 후에 T1 과 조인 하기 때문에 최악의 Hash 조인이라는 것이다.

아래의 PLAN 은 위의 plan 과 반대이며 Left Deep Tree PLAN 이라고 한다.
아래 plan 의 조인순서는 당연히 T1 --> T2 --> T3 --> T4 이며 T1 테이블의 엑세스 및 Filter 과정에서
많은수의 데이터가 FILTER 된다면 최적의 PLAN 이다.

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|*  1 |  HASH JOIN           |      |
|*  2 |   HASH JOIN          |      |
|*  3 |    HASH JOIN         |      |
|*  4 |     TABLE ACCESS FULL| T1   |
|   5 |     TABLE ACCESS FULL| T2   |
|   6 |    TABLE ACCESS FULL | T3   |
|   7 |   TABLE ACCESS FULL  | T4   |
-------------------------------------

이제 2개의 plan 을 테스트 해보자.
아래의 Right Deep Tree Plan 테스트는 실제환경에서 나오지 말아야 할 PLAN 을 테스트 한것이다.
테스트를 위하여 힌트를 주어 일부러 악성 plan 을 만들었다.
특히 힌트부분을 주목하라.

4.Right Deep Tree Plan 테스트

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.13 | 1212K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |     90 |00:00:00.04 |          |
|*  3 |   HASH JOIN          |      |      1 |  10000 |00:00:00.18 |   11M (0)|
|   4 |    TABLE ACCESS FULL | T2   |      1 |  10000 |00:00:00.03 |          |
|*  5 |    HASH JOIN         |      |      1 |  10000 |00:00:00.10 |   11M (0)|
|   6 |     TABLE ACCESS FULL| T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |     TABLE ACCESS FULL| T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

위 SQL 은 FILTER 조건 (T1.N1 < 50) 이 있지만 마지막 조인시 까지 조인건수를 줄일수 없으므로
hash area size 를 많이 쓰게 된다. 
그결과 수행속도가 느리며 특히 메모리 사용량(Used-Mem 참조)이 23.2(11MB + 11MB + 1212K) MB 에 달한다.
Hash 조인 3번의 수행시간(A-Time 참조) 또한 0.13 + 0.18 + 0.10 으로 0.41 초나 걸렸다.
3번의 조인 모두 Optimal Pass(빨강색 부분이 0 이면 Optimal 임) 라고 방심하면 안된다.
여기에는 엄청난 비효율이 숨어 있다.

아래는 Left Deep Tree PLAN 으로 튜닝하여 테스트를 수행한 결과이다.

4.Left Deep Tree PLAN 테스트

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

-------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Used-Mem |
-------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |      |      1 |     90 |00:00:00.07 | 1220K (0)|
|*  2 |   HASH JOIN          |      |      1 |     90 |00:00:00.10 | 1229K (0)|
|*  3 |    HASH JOIN         |      |      1 |     90 |00:00:00.07 | 1229K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |     90 |00:00:00.04 |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  10000 |00:00:00.03 |          |
|   6 |    TABLE ACCESS FULL | T3   |      1 |  10000 |00:00:00.03 |          |
|   7 |   TABLE ACCESS FULL  | T4   |      1 |  10000 |00:00:00.03 |          |
-------------------------------------------------------------------------------

Used-Mem 컬럼의 사용량이 Right Deep Tree Plan 에 비해 대폭 줄어든것에 주목해야 한다.
Hash 조인시 사용한 총 hash area size 사용량 = 1220K + 1229K + 1229K (약 3.6 MB) 이다.
Left Deep Tree PLAN 을 사용하는것으로 바꾸니 놀랍게도 메모리 사용량이 6.5 배나 줄었다.
또한 A-Time 의 수행시간도 약 2배나 차이가 난다.
테스트를 짧게 끝내기위해 각 테이블을 만건으로 한정 했지만 건수가 많아 질수록 차이는
더 벌어질 것이다.

결론 :
T1.N1 < 50 과 같은 훌륭한 filter 조건이 있는 경우에 통계정보의 부재, 부적절한 조인조건등 여러가지
이유로 인하여 Right Deep Tree Plan 이 나온다면 튜닝을 하여 Left Deep Tree  Plan 으로 만들어야 한다.
물론 예외적인 경우 Left Deep Tree PLAN 으로 유도 하는것이 불가능 할수도 있다.
하지만 그때에도 Bushy Tree Plan(주2) 등으로 유도하여 Hash 조인을 튜닝 하여야 한다.
그렇지 않을 경우 과도한 메모리 사용과 수행속도 저하를 막을수 없다.


주1 : Optimal Pass 란 Hash 조인시 build 테이블(Driving)을 Scan 하면서 메모리에 적재하는 과정을
       거치는데 이때 메모리 공간(Hash Area Size) 가 부족하면 메모리에 적재하지 못하고
       Temp 영역(DISK)에 적재를 하는 비효율이 있지만  Optimal Pass 는 메모리 공간이 충분하여
       Disk 작업이 없는 상태를 말한다.
주2: 간단히 설명하면 T1 과 T2 를 조인하고 T3 와 T4 를 조인하여 2개의 결과 집합을 조인하는 방식임.
     자세한 내용은 Hash 조인시 Bushy tree Plan 유도하기 를 참조하기 바란다.

거의모든 튜닝책(원서포함)에 LIKE '%XXX  검색은 테이블 혹은 인덱스를 full scan 한다고 되어있다.
하지만 과연 이것이 사실일까?
필자는 이런류의 오류를 "공공의 오류"라고 표현한다.
대부분 혹은 대다수의 개발자나 심지어 튜너라고 하는사람들 까지 이 공공의 오류를 믿고있다.
물론 필자도 예외는 아니며 공공의 오류를 범하지 않기위해 꾸준히 노력 하고 있다.

아례 예제를  보면서 이런 오해에서 벗어나도록 하자.

1.테스트 환경
버젼 : 10gR2(10.2.0.3) ,
Optimizer mode :all_rows/first_row_n 상관없이 실행가능
계정 : scott/tiger


2. ENAME 에 인덱스 생성

CREATE INDEX EMP5 ON EMP (ENAME);

EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> Analyze


3. LIKE '%XXX' 실행 

select *
from emp
where ename like '%'||:V_ENAME||'%';     --> 변수에 'A' 대입

-- 결과는 당연히 A 를 포함한 결과만 나왔으며 ENAME 으로 Sort 되어있다.

4. Trace 내용확인

Compile Time    : 2008/06/03 19:37:48
Trace File Name : /ora_dump/NBCORET/udump/nbcoret2_ora_6064.trc
Trace Version   : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
Environment     : Array Size = 10
                  Long  Size = 80

********************************************************************************

select * from emp where ename like '%'||:V_ENAME||'%'

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.001          0          0          0          0
Fetch        2    0.000        0.000          0          4          0          7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.001          0          4          0          7

Elapsed Time for Client(sec.): 0.007
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      7   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=592 us)
      7    INDEX RANGE SCAN EMP5 (cr=2 pr=0 pw=0 time=872 us)OF EMP5 (NONUNIQUE)

-- 정확히 'A' 가 포함된 블럭만 READ 했음을 알수 있다.

5.결론

과거에는 LIKE '%XXX%' 류를 빠르게 수행하기 위하여 Domain Index 사용하거나 비싼 검색엔진을 도입하였다.
하지만 이제부터는 Like 를 사용해도 똑같은 효과를 낼수있음을 명심해야 할것이다.

편집후기:
어떻게 LIKE '%XXX%' 구분이 index range scan 을 할수 있는지는 필자도 현재 분석중이다.

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||']+', 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;

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