DATOR


Join 조건 테이블 분산에 따른 Access 비효율 제거 - FBI(function based index) 활용. 튜닝[SQL]


많이 사용되어지는 SQL 유형중 하나가 Master , Detail  Table 간의 Join 입니다.

 

어떠한 경우가 되었건 정규화를 거치고 물리모델로 가면 각 Table 에만 필요한 속성을 가지게 됩니다.

 

하지만 조회조건이 Master 에도 있고 Detail 에도 있다면 어느 테이블을 먼저 Driving 해야 할지 고민이 생기게 됩니다.

 

물론 전체적으로 건수가 적은 Master Table 이 먼저 Driving 되어야 성능이 좋을 것이라 생각할 것입니다.

 

하지만 아래의 SQL 문장을 보면

 

           SELECT  
                  D.MAT_NO, D.AMT
             FROM MM_MAT_IO_MST M, MM_MAT_IO_DTL D
            WHERE M.CO_DVCD = :ARG_CO
              AND M.CO_DVCD = D.CO_DVCD
              AND M.MAT_IO_NO = D.MAT_IO_NO
              AND M.MAT_IO_DVCD = '2'
              AND M.IO_YMD BETWEEN :ARG_FYMD AND :ARG_TYMD
              AND D.MAT_NO = :ARG_MATER
              AND D.AMT > 0

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.01       0.01           0           4          0           0
Fetch            3        0.03       0.08           7        1087          0          29
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total            5        0.04       0.08           7        1091          0          29

Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 62  (AA)

Rows     Row Source Operation
-------  ---------------------------------------------------
     29  FILTER  (cr=1087 pr=7 pw=0 time=41350 us)
     29   HASH JOIN  (cr=1087 pr=7 pw=0 time=41347 us)
    770    TABLE ACCESS BY INDEX ROWID MM_MAT_IO_DTL (cr=795 pr=4 pw=0 time=4848 us)
    789     INDEX SKIP SCAN MM_MAT_IO_DTL_IX03 (cr=14 pr=0 pw=0 time=996 us)(object id 440710)
  27241    INDEX RANGE SCAN MM_MAT_IO_MST_IX02 (cr=292 pr=3 pw=0 time=27 us)(object id 440572)

 

실행계획을 보면 Master Table 이 먼저 수행되지 않고 Detail Table 이 먼저 수행되어 Hash Join 으로 수행되고 있습니다.

 

물론 힌트로 실행계획을 제어할수 있겠지만 Master Table 이 먼저 읽는다 해도 Master Table 을 읽는 Block 수는  

 

변하지 않을 것입니다.

 

이런경우는 위의 실행계획처럼 Detail 과 Master 를 각각 읽는 범위를 좁힌뒤 Hash Join 으로 수행하는것이 유리할 수도 있겠지만 

 

Master Table 에서의 비효율은 사라지지 않습니다.

 

Function Based Index (이하 FBI) 는 이런 경우에 사용할 수 있습니다.

 

Detail Table 에서 Master Table 쪽의 조건을 미리 조인하여 인덱스를 생성함으로써 SQL 수행시 

 

한쪽 Table 에 Driving 조건을 모두 부여함으로 비효율을 제거 할 수 있습니다.

 

--인덱스 생성 script

 

  CREATE INDEX MM_MAT_IO_DTL_IX01
 ON MM_MAT_IO_DTL(MAT_NO, SUBSTR(FC_MR_GET_MAT_IO_YMD(CO_DVCD , MAT_IO_NO ),1,8))

  ..

  ..

 

Detail Table 에 있는 조건으로 Master Table 에 있는 IO_YMD 조건을 인덱스로 생성하여 조건으로 비교하는 방법입니다.

 

실제 수행된 결과를 보면..

 

          SELECT  
                  D.MAT_NO, D.AMT
             FROM   MM_MAT_IO_MST M, MM_MAT_IO_DTL D
            WHERE M.CO_DVCD = :ARG_CO
              AND M.CO_DVCD = D.CO_DVCD
              AND M.MAT_IO_NO = D.MAT_IO_NO
              AND M.MAT_IO_DVCD = '2'
              AND SUBSTR(FC_MM_GET_MAT_IO_YMD(D.CO_DVCD , D.MAT_IO_NO ),1,8) BETWEEN :ARG_FYMD AND :ARG_TYMD  --> Function-Based Index를 타기 위한 부분.
              AND D.MAT_NO = :ARG_MATER
              AND D.AMT > 0

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.01       0.01           0          10          0           0
Fetch            3        0.00       0.01           1         124          0          29
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total            5        0.01       0.02           1         134          0          29

Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 62  (UHATIS)

Rows     Row Source Operation
-------  ---------------------------------------------------
     29  FILTER  (cr=124 pr=1 pw=0 time=94 us)
     29   NESTED LOOPS  (cr=124 pr=1 pw=0 time=91 us)
     29    TABLE ACCESS BY INDEX ROWID MM_MAT_IO_DTL (cr=34 pr=1 pw=0 time=495 us)
     29     INDEX RANGE SCAN MM_MAT_IO_DTL_IX01 (cr=5 pr=0 pw=0 time=317 us)(object id 443004)
     29    TABLE ACCESS BY INDEX ROWID MM_MAT_IO_MST (cr=90 pr=0 pw=0 time=501 us)
     29     INDEX UNIQUE SCAN MM_MAT_IO_MST_PK (cr=61 pr=0 pw=0 time=317 us)(object id 441388)

 

실행계획에서 Detail Table 이 먼저 수행되어 FBI 를 통해 조건에 해당하는 건들만 읽어오고 다시 Master Table 을 읽어서

 

Master Table 에 있는 조건을 Filter 처리 함으로 전혀 비효율이 발생하지 않고 있습니다.

 

물론 함수 내부에 Master Table 의 다른 조건도 포함시킨다면 Master Table 을 Access 하지 않아도 될 것입니다.

 

한가지 주의할 사항은 FBI 생성시에는 생성되는 인덱스 칼럼중 변경되는 칼럼이 없어야 합니다.

 

Table 에서 변경되는 값은 FBI에는 적용되지 않기 때문입니다.

 

   

 

 

 

 

Tag :

Leave Comments