by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

4. 조인 순서의 중요성




I. 조인순서의 중요성

1. 조인순서에 따라 쿼리 수행 성능이 차이가 날 수 있음

  • 기본적으로 작은 건수의 테이블을 드라이빙하여 조인을 하는 것이 유리하며 이는 모든 튜닝방법의 정석임
  • 즉, A와 B 테이블이 조건없이 조인만 되어 있을 경우 작은테이블을 드라이빙 하는것이 유리하고
    필터 조건이 큰 테이블에 있는 경우 NDV에 따라 큰 테이블을 드라이빙 하는 것이 유리하다.
  • 그러므로 여러 테이블이 조인되어 있고 들어오는 조건도 많을 경우 조건에 따라 전략적인 조인순서를 만들어야 하고
    이를 위해 동적 힌트를 사용해서 튜닝을 진행할 수 있다.

II. 필터 조건이 없을 때

1. 샘플 데이터 생성


-- 1. 오라클 버전
SELECT * FROM v$version;

BANNER                                                               
---------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit     
PL/SQL Release 10.2.0.3.0 - Production                               

-- 2. create table
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;

CREATE TABLE EMP AS
SELECT -- 1. EMPNO(Unique 컬럼)
       LEVEL EMPNO,
       -- 2. EMPNO(VARCHAR로 변형)
       TO_CHAR(LEVEL) EMPNO_VARCHAR,
       -- 3. JOB(10만건 데이터를 20개 그룹으로 분류)
       CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
       -- 4. HIREDATE(10개씩 증가하여 날짜 데이터 만듬)  
       TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
       -- 5. DEPTNO(자리수 길이를 한 그룹으로 하여 데이터 생성, 분포도 차이나도록) 
       LENGTH(LEVEL) * 10 DEPTNO
FROM   DUAL
CONNECT BY LEVEL <= 100000
;

CREATE TABLE DEPT AS
SELECT LEVEL * 10        DEPTNO,
       'SALES_' || LEVEL DNAME,
       'ZONE_'  || LEVEL LOC
FROM   DUAL
CONNECT BY LEVEL <= 9
;

-- 3. create index
CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_U1 NOPARALLEL;

CREATE INDEX EMP_N1 ON EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N1 NOPARALLEL;

CREATE INDEX EMP_N2 ON EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N2 NOPARALLEL;

CREATE INDEX EMP_N3 ON EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N3 NOPARALLEL;

CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO);

-- 4. gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);

-- 5. View EMP Table Statistics
*************[ Table Info ]**********************************************************************************************************************************************************

COLUMN_NAME                    DATA_TYPE  DATA_LEN Null DISTINCT_KEY      DENSITY NUM_NULLS      NUM_BUCKETS SAMPLE_SIZE    LAST Analyzed       HISTOGRAM       Default Value        
------------------------------ ---------- -------- ---- -------------- ---------- -------------- ----------- -------------- ------------------- --------------- ---------------------
DEPTNO                         NUMBER           22 Y                 4        .25              0           1          4,996 2010-03-31 19:36:04 NONE                                 
EMPNO                          NUMBER           22 Y            99,600  .00001004              0           1          4,996 2010-03-31 19:36:04 NONE                                 
EMPNO_VARCHAR                  VARCHAR2         40 Y           100,000     .00001              0           1        100,000 2010-03-31 19:36:04 NONE                                 
HIREDATE                       DATE              7 Y            10,000      .0001              0           1        100,000 2010-03-31 19:36:04 NONE                                 
JOB                            VARCHAR2          3 Y                 1          1              0           1          4,996 2010-03-31 19:36:04 NONE                                 

OWNER.TABLE NAME                                            AVG USED_BLOCKS                 PCTFREE/  INITRAN Init/Next/    FLst/ IOT_Name/                           Duration       
[TABLESPACE NAME]                        NUMBER_OF_ROWS  ROWLEN EMPTY_BLOCKS  DEG  CHAIN    USED/INCR MAXTRAN MIN/MAX       FGrp  IOT_Type        LAST Analyzed       Dropped        
---------------------------------------- -------------- ------- ------------- ---- -------- --------- ------- ------------- ----- --------------- ------------------- ---------------
USER.EMP                                        100,000      23          413    1         0 10//0     1/255   128k/128k     /                     2010-03-31 19:36:04                
[USER_TS_TX_DATA]                                                          0                                  1/Unlimit                                               NO             

                                                                NUMBER_ROWS     CLUSTERING     BLEVEL       AvgLfBlperKEY             INITRAN Init/Next/    FLst/                    
OWNER.INDEX_NAME                         TABLESPACE             DISTINCT_KEY    FACTOR         LEAF_BLOKCS  AvgDtBlperKEY DEG  LOGGIN MAXTRAN MIN/MAX       FGrp  LAST Analyzed      
---------------------------------------- ---------------------- --------------- -------------- ------------ ------------- ---- ------ ------- ------------- ----- -------------------
USER.EMP_N1                              USER_TS_TX_DATA               100,000             401         [1]            32    1  YES    2/255   128k/128k     /     2010-03-31 19:36:06
                                                                             6                         196            66                      1/unlimit                              
USER.EMP_N2                              USER_TS_TX_DATA               100,000             401         [1]             1    1  YES    2/255   128k/128k     /     2010-03-31 19:36:07
                                                                        10,000                         266             1                      1/unlimit                              
USER.EMP_N3                              USER_TS_TX_DATA               100,000          19,298         [1]             1    1  YES    2/255   128k/128k     /     2010-03-31 19:36:08
                                                                       100,000                         236             1                      1/unlimit                              
USER.EMP_U1                              USER_TS_TX_DATA               100,000             399         [1]             1    1  YES    2/255   128k/128k     /     2010-03-31 19:36:05
                                                                       100,000                         208             1                      1/unlimit                              

OWNER.INDEX_NAME                         Uniq Type   Status Drop COLUMN LIST                                                                                                         
---------------------------------------- ---- ------ ------ ---- --------------------------------------------------------------------------------------------------------------------
USER.EMP_N1                              NONU NORMAL VALID  NO   DEPTNO                                                                                                              
USER.EMP_N2                              NONU NORMAL VALID  NO   HIREDATE                                                                                                            
USER.EMP_N3                              NONU NORMAL VALID  NO   EMPNO_VARCHAR                                                                                                       
USER.EMP_U1                              UNIQ NORMAL VALID  NO   EMPNO                                                                                                               

-- 6. View DEPT Table Statistics
*************[ Table Info ]**********************************************************************************************************************************************************

COLUMN_NAME                    DATA_TYPE  DATA_LEN Null DISTINCT_KEY      DENSITY NUM_NULLS      NUM_BUCKETS SAMPLE_SIZE    LAST Analyzed       HISTOGRAM       Default Value        
------------------------------ ---------- -------- ---- -------------- ---------- -------------- ----------- -------------- ------------------- --------------- ---------------------
DEPTNO                         NUMBER           22 Y                 9 .111111111              0           1              9 2010-03-31 19:36:09 NONE                                 
DNAME                          VARCHAR2         46 Y                 9 .111111111              0           1              9 2010-03-31 19:36:09 NONE                                 
LOC                            VARCHAR2         45 Y                 9 .111111111              0           1              9 2010-03-31 19:36:09 NONE                                 

OWNER.TABLE NAME                                            AVG USED_BLOCKS                 PCTFREE/  INITRAN Init/Next/    FLst/ IOT_Name/                           Duration       
[TABLESPACE NAME]                        NUMBER_OF_ROWS  ROWLEN EMPTY_BLOCKS  DEG  CHAIN    USED/INCR MAXTRAN MIN/MAX       FGrp  IOT_Type        LAST Analyzed       Dropped        
---------------------------------------- -------------- ------- ------------- ---- -------- --------- ------- ------------- ----- --------------- ------------------- ---------------
USER.DEPT                                             9      18            4    1         0 10//0     1/255   128k/128k     /                     2010-03-31 19:36:09                
[USER_TS_TX_DATA]                                                          0                                  1/Unlimit                                               NO             

                                                                NUMBER_ROWS     CLUSTERING     BLEVEL       AvgLfBlperKEY             INITRAN Init/Next/    FLst/                    
OWNER.INDEX_NAME                         TABLESPACE             DISTINCT_KEY    FACTOR         LEAF_BLOKCS  AvgDtBlperKEY DEG  LOGGIN MAXTRAN MIN/MAX       FGrp  LAST Analyzed      
---------------------------------------- ---------------------- --------------- -------------- ------------ ------------- ---- ------ ------- ------------- ----- -------------------
USER.DEPT_U1                             USER_TS_TX_DATA                     9               1         [0]             1    1  YES    2/255   128k/128k     /     2010-03-31 19:36:09
                                                                             9                           1             1                      1/unlimit                              

OWNER.INDEX_NAME                         Uniq Type   Status Drop COLUMN LIST                                                                                                         
---------------------------------------- ---- ------ ------ ---- --------------------------------------------------------------------------------------------------------------------
USER.DEPT_U1                             UNIQ NORMAL VALID  NO   DEPTNO                                                                                                                        

2. EMP => DEPT

SELECT /*+ ORDERED USE_NL(E D) */
       *
FROM   EMP  E,
       DEPT D
WHERE  E.DEPTNO = D.DEPTNO
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch    10001    0.050        1.984        399     120361          0     100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total    10003    0.050        1.987        399     120361          0     100000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
 100000   NESTED LOOPS  (cr=120361 pr=399 pw=0 time=1210637 us)
 100000    TABLE ACCESS FULL EMP (cr=10360 pr=399 pw=0 time=110599 us)
 100000    TABLE ACCESS BY INDEX ROWID DEPT (cr=110001 pr=0 pw=0 time=997420 us)
 100000     INDEX UNIQUE SCAN DEPT_U1 (cr=10001 pr=0 pw=0 time=403812 us)(Object ID 19697318)

3. DEPT => EMP

SELECT /*+ ORDERED USE_NL(E D) */
       *
FROM   DEPT D,
       EMP  E
WHERE  E.DEPTNO = D.DEPTNO
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch    10001    0.050        1.168        196      20576          0     100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total    10003    0.050        1.171        196      20576          0     100000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
 100000   TABLE ACCESS BY INDEX ROWID EMP (cr=20576 pr=196 pw=0 time=67708660 us)
 100010    NESTED LOOPS  (cr=10218 pr=196 pw=0 time=1400730 us)
      9     TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=235 us)
 100000     INDEX RANGE SCAN EMP_N1 (cr=10210 pr=196 pw=0 time=67305741 us)(Object ID 19697315)

4. 정리

  • 위에서 보는 것처럼 'EMP -> DEPT' 순서일 경우 조인횟수가 총 20만번이 이루어 졌지만
    'DEPT -> EMP'일 경우 10만번에 9번만 추가되어 I/O가 줄어들음을 알 수 있다(I/O : 120,361 -> 20,576)

III. 필터 조건이 있을 때

1. DEPT => EMP

SELECT /*+ ORDERED USE_NL(E D) */
       *
FROM   DEPT D,
       EMP  E
WHERE  E.DEPTNO = D.DEPTNO
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch    10001    0.050        1.168        196      20576          0     100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total    10003    0.050        1.171        196      20576          0     100000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
 100000   TABLE ACCESS BY INDEX ROWID EMP (cr=20576 pr=196 pw=0 time=67708660 us)
 100010    NESTED LOOPS  (cr=10218 pr=196 pw=0 time=1400730 us)
      9     TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=235 us)
 100000     INDEX RANGE SCAN EMP_N1 (cr=10210 pr=196 pw=0 time=67305741 us)(Object ID 19697315)
;

2. DEPT => EMP(필터조건 존재)

SELECT /*+ ORDERED USE_NL(E D) */
       *
FROM   DEPT D,
       EMP  E
WHERE  E.DEPTNO = D.DEPTNO
AND    E.EMPNO  = 1
;

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

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   NESTED LOOPS  (cr=24 pr=0 pw=0 time=185 us)
      9    TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=209 us)
      1    TABLE ACCESS BY INDEX ROWID EMP (cr=20 pr=0 pw=0 time=168 us)
      9     INDEX UNIQUE SCAN EMP_U1 (cr=11 pr=0 pw=0 time=101 us)(Object ID 19697314)
;

3. EMP(필터조건 존재) => DEPT

SELECT /*+ ORDERED USE_NL(E D) */
       *
FROM   EMP  E,
       DEPT D
WHERE  E.DEPTNO = D.DEPTNO
AND    E.EMPNO  = 1
;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.006          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0          5          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.010        0.006          0          5          0          1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   NESTED LOOPS  (cr=5 pr=0 pw=0 time=109 us)
      1    TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=82 us)
      1     INDEX UNIQUE SCAN EMP_U1 (cr=2 pr=0 pw=0 time=56 us)(Object ID 19697314)
      1    TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=21 us)
      1     INDEX UNIQUE SCAN DEPT_U1 (cr=1 pr=0 pw=0 time=9 us)(Object ID 19697318)
;

4. 정리

  • 위에서 보는 것처럼 필터조건이 있을 경우 해당 테이블에서 필터조건의 NDV가 테이블의 로우수와 같은
    10만(Unique)이기 때문에 EMP 테이블을 드라이빙 하는 것이 좋다.

IV. 심플 ERD 사용방법 및 실사례 살펴봄(책 이외의 내용)

1. 심플 ERD란?

  • Entity Relation Diagram의 약자로 Entity 관계들을 논리적으로 표현할 수 있는 방법론
  • 심플 ERD는 필자가 만든 방법론으로 기존 ERD 방법론에서 어려운 내용을 제외하고 쉽게 작성하여
    SQL의 액세스 패스를 이해하고자 하는 것이 주요 목적임
  • 심플 ERD의 장점은 테이블의 조인관계를 논리적으로 표현하여 옵티마이저가 만든 조인순서를 평가할 수 있으며,
    비효율적인 부분이 있을 경우 더 좋게 변경이 가능하고, 마지막으로 프로그램 이력관리가 가능하다.

    이 심플 ERD 방법론은 필자가 만든 방법론이므로 문서작성에 활용될 경우 출처를 남겨주시기 바랍니다.

2. 심플 ERD 사용방법

  • FROM절의 테이블들을 사각형으로 모두 표시해주고, 이 사각형 박스에 테이블 이름 또는 Alias를 명시한다.
  • WHERE절의 조인관계를 참고하여 직선을 그려주고, 만약 서브쿼리일 경우는 점선 사각형으로 구분시킨다.
  • Outer Join은 기준 테이블에서 대상 테이블로 직선 화살표를 그린다.(Outer Join은 단방향 액세스 패스이므로)
    이와 마찬가지로 데이터 타입이 틀려 한쪽을 형변환 했을 경우 형변환 된 테이블에서 조인 테이블로 직선 화살표를 그린다.
  • View 또는 Inline View를 그릴 경우 구분할 수 있는 박스를 그린다.

3. 심플 ERD 작성 예

SELECT /*+ LEADING(WND) 
           USE_NL(WND WDA WDD MP OOH OOL OTT XGSE XLV XCRH XSM XGC XFFH XFFL) 
       */
       ...............
FROM   TABLE_01   XXB,
       TABLE_02   WND,
       TABLE_03   WDA,
       TABLE_04   WDD,
       TABLE_05   MP,
       TABLE_06   OOH,
       TABLE_07   OOL,
       TABLE_08   OTT,
       TABLE_09   XGSE,
       TABLE_10   XLV,
       TABLE_11   XLV1,
       TABLE_12   XCRH,
       TABLE_13   XFFL,
       TABLE_14   XFFH,
       TABLE_15   XSM
WHERE  XXB.SR_NO                 = WND.NAME
AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
AND    WND.NAME                  = XGSE.SR_NO(+)
AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
AND    XLV1.ORG_ID               = OOH.ORG_ID
AND    EXISTS (SELECT WDA1.DELIVERY_ID
               FROM   TABLE16 WDA1,
                      TABLE17 WDD1
               WHERE  WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID
               AND    WDA1.DELIVERY_ID        = WND.DELIVERY_ID)
AND    WND.ORGANIZATION_ID  = :B1 
AND    MP.ORGANIZATION_CODE = :B2 
AND    OOH.ATTRIBUTE1       = :B3 
;

4. 심플 ERD 단계적 작성

01.
 WHERE  XXB.SR_NO                 = WND.NAME 
02.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID 
03.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID 
04.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID 
05.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID 
06.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID 
07.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID 
08.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID 
09.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID 
10.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID 
11.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID 
12.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+) 
13.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO 
14.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE 
15.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE 
16.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
 AND    XLV1.ORG_ID               = OOH.ORG_ID 
17.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
 AND    XLV1.ORG_ID               = OOH.ORG_ID
 AND    EXISTS (SELECT WDA1.DELIVERY_ID
                FROM   TABLE16 WDA1,
                       TABLE17 WDD1
                WHERE  WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID
                AND    WDA1.DELIVERY_ID        = WND.DELIVERY_ID) 
18.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
 AND    XLV1.ORG_ID               = OOH.ORG_ID
 AND    EXISTS (SELECT WDA1.DELIVERY_ID
                FROM   TABLE16 WDA1,
                       TABLE17 WDD1
                WHERE  WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID
                AND    WDA1.DELIVERY_ID        = WND.DELIVERY_ID)
 AND    WND.ORGANIZATION_ID  = :B1 
 AND    MP.ORGANIZATION_CODE = :B2 
 AND    OOH.ATTRIBUTE1       = :B3  

5. 실사례

  • 위의 ERD를 보면 3개의 테이블에 조건이 들어가므로 드라이빙 테이블은 일단 3개의 후보군을 선정할 수 있다.
  • 이 때 가장 좋은 NDV를 가지는 WDD.ORGANIZATION_ID의 NDV가 가장 좋으므로 힌트도 이미 WND를 LEADING 한 상태임
  • 문제는 WND에서 시작해 WDD 오른쪽 영역으로 넘어갈 수 있는 액세스 패스가 2개인데, 옵티마이저 선택에 따라
    'WND -> WDA -> WDD -> ...'로 갈 수도 있고(파란 화살표), 'WND -> XXB -> XLV1 -> OOH -> ...'로 갈 수도 있다(빨간 화살표)
  • 좋은 액세스 패스는 파란 화살표의 경우이며 빨간 화살표로 갈 경우 데이터를 필터하고 가지 못하기 때문에 성능에 치명적인 패스
  • 실제로 평소에 이 SQL을 사용하는 프로그램의 수행속도는 10초 이내였으나, 통계정보 갱신 이후 갑자기 수행속도가 30분으로 늘어나
    업무에 지장을 주게 되었는데 그 때 실행계획을 살펴본 결과 파란 화살표가 아닌 빨간 화살표로 액세스 패스가 변경되었음
  • 이를 해결하기 위해서는 LEADING 힌트로 항상 파란 화살표 액세스 패턴이 고정되도록 할 수 있었으나, 근본적으로
    액세스 패턴이 하나가 아닌 두개 이상이어서 생긴 문제이므로 XLV1과 OOH의 조인을 제거할 수 있는지를 살펴봄
  • 이를 확인하기 위해 업무담당자들과 협의한 결과 XLV1과 OOH의 조인은 단순히 Validation 처리를 위한 조인관계라고 하며
    이는 변수 선언부에서 값을 할당받아 해당 SQL에서 상수로 값을 넣고 조인은 제거가 가능하다는 회신을 받음
  • 결국 힌트는 변경하지 않고 연결고리만 끊어 단방향 액세스 패스를 유도하게 되었고 성능은 이전처럼 10초 이내로 조회가 됨
19.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
 AND    XLV1.ORG_ID               = OOH.ORG_ID
 AND    EXISTS (SELECT WDA1.DELIVERY_ID
                FROM   TABLE16 WDA1,
                       TABLE17 WDD1
                WHERE  WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID
                AND    WDA1.DELIVERY_ID        = WND.DELIVERY_ID)
 AND    WND.ORGANIZATION_ID  = :B1 
 AND    MP.ORGANIZATION_CODE = :B2 
 AND    OOH.ATTRIBUTE1       = :B3  
20.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
 --AND    XLV1.ORG_ID               = OOH.ORG_ID
 AND    XLV1.ORG_ID               = :B11
 AND    EXISTS (SELECT WDA1.DELIVERY_ID
                FROM   TABLE16 WDA1,
                       TABLE17 WDD1
                WHERE  WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID
                AND    WDA1.DELIVERY_ID        = WND.DELIVERY_ID)
 AND    WND.ORGANIZATION_ID  = :B1 
 AND    MP.ORGANIZATION_CODE = :B2 
 AND    OOH.ATTRIBUTE1       = :B3  
21.
 WHERE  XXB.SR_NO                 = WND.NAME
 AND    WND.DELIVERY_ID           = WDA.DELIVERY_ID
 AND    WDA.DELIVERY_DETAIL_ID    = WDD.DELIVERY_DETAIL_ID
 AND    WND.ORGANIZATION_ID       = MP.ORGANIZATION_ID
 AND    WDD.SOURCE_HEADER_ID      = OOH.HEADER_ID
 AND    WDD.SOURCE_LINE_ID        = OOL.LINE_ID
 AND    OTT.TRANSACTION_TYPE_ID   = OOH.ORDER_TYPE_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID
 AND    TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID
 AND    XFFL.WH_SHP_HEADER_ID     = XFFH.WH_SHP_HEADER_ID
 AND    XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID
 AND    WND.NAME                  = XGSE.SR_NO(+)
 AND    TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO
 AND    XLV1.LOOKUP_CODE          = XXB.SHIPPING_LINE_CODE
 AND    XSM.SHIP_METHOD_CODE      = XFFH.SHIPPING_METHOD_CODE
 --AND    XLV1.ORG_ID               = OOH.ORG_ID
 AND    XLV1.ORG_ID               = :B11
 AND    EXISTS (SELECT WDA1.DELIVERY_ID
                FROM   TABLE16 WDA1,
                       TABLE17 WDD1
                WHERE  WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID
                AND    WDA1.DELIVERY_ID        = WND.DELIVERY_ID)
 AND    WND.ORGANIZATION_ID  = :B1 
 AND    MP.ORGANIZATION_CODE = :B2 
 AND    OOH.ATTRIBUTE1       = :B3  

6. 정리

  • 이처럼 조인순서는 성능에 영향을 많이 줄 수 있으며 힌트가 없을 경우 통계정보 갱신이나
    OBJECT 생성 및 변경에 의해서도 조인순서가 변경이 될 수 있기 때문에 들어오는 조건에
    최적화가 될 수 있도록 전략적인 방법론이 필요하며, 가급적 액세스 패스는 하나의 통로로만
    다닐 수 있도록 설계하는 것이 중요하다.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 4월 02, 2010

    장태길 says:

    멋지군 강박사 예전에 한번 설명을 들은거라.. 나도 이해가 되네.. 문서화 굿.. 사족.. COMPUTE STATISTICS In earlie...

    멋지군 강박사
    예전에 한번 설명을 들은거라.. 나도 이해가 되네..
    문서화 굿..

    사족..
    COMPUTE STATISTICS
    In earlier releases, you could use this clause to start or stop the collection
    of statistics on an index. This clause has been deprecated.
    Oracle Database now automatically collects statistics during index creation and rebuild.
    This clause is supported for backward compatibility and will not cause errors.