Join 조건 테이블 분산에 따른 Access 비효율 제거 - FBI(function based index) 활용. 튜닝[SQL]
2011.02.06 19:40
많이 사용되어지는 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에는 적용되지 않기 때문입니다.
추천
스크랩
URL복사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 :
- Tuning
Comments 4
-
김용환
2011.06.13 14:31
*.158.247.232
-
kypark76
2011.06.01 10:30
*.189.124.101
-
김용환
2011.02.09 10:49
*.158.247.238
아래는 함수 생성 문장입니다. 물론 아무 컬럼이나 포함시킬수는 없을듯 합니다. MASTER TABLE 과 DETAIL TABLE 간의 조인시 MASTER TABLE 의 날짜 칼럼을 DETAIL TABLE 에 인덱스로 생성해 놓는 것이고 PK 가 아니더라도 화면에서 조건으로 사용되는 칼럼들은 변하지 않는 칼럼들일 가능성이 높은거 같습니다. 업무담당자와 협의가 필요하고 결정해야 할거 같습니다. CREATE OR REPLACE FUNCTION FC_MM_GET_MAT_IO_YMD
(
arg_co varchar2,
arg_mat_io_no varchar2
) RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS v_ymd varchar2(8) ;
/*------------------------------------------------------
-- MM_MAT_IO_DTL에 자재별 일출고일자를 가져오는 FBI 생성을 위한 함수
-- Argument : arg_co IN varchar2, 회사코드
arg_mat_io_no IN varchar2, 해당자재입출고번호
v_ymd OUT varchar2 해당 입출고일자
------------------------------------------------------*/
BEGIN v_ymd := null;
BEGIN SELECT IO_YMD
INTO v_ymd
FROM MM_MAT_IO_MST
WHERE CO_DVCD = arg_co
AND MAT_IO_NO = arg_mat_io_no; EXCEPTION
WHEN NO_DATA_FOUND THEN
v_ymd := null;
END;
RETURN v_ymd;
END;