[Oracle] Function 내부작동 및 Nasted Loops Join / Hash Join

Hash Join 방식

먼저 수행되는 테이블 (선행 테이블)에 대해 상수 조건으로 데이터 걸러낸 후 join key column 으로 Hash map을 만든다.

그 다음 Probe Table(후행 테이블)을 수행하는데 이 때 상수 조건이 있다면, 먼저 상수 조건으로 대상을 

줄인다. 그 이후 선행 테이블이 생성해 놓은 Hash Map 에 있는 데이터 중에서 조인에 성공한 데이터를 추출한다.

선행테이블의 상수 조건으로 데이터를 줄일 수 있더라도, 후행테이블의 상수 조건 없음으로 인해, 후행 테이블의 전체 데이터 건수만큼 Function 이 수행된다.

(I/O 처리량이 급격히 높아져 성능이 안좋게 된다.)

ex)

FN_C1_CODENM := c1 코드명을 가져오는 함수

FN_C2_CODENM := c2 코드명을 가져오는 함수

SELECT /*+ LEADING(T1) USE_HASH(T1 T2) */

T1.*,

T2.*

FROM FUNCTION_TABLE T1, C1_CODE_NM T2

WHERE

T1.C2 = 0

AND T1.C3 = 'A'

AND T1.C1 = T2.C1

AND T2.C3 = FN_C2_CODENM(T2.C4)

t1의 상수를 검색한 table 값을 hash로 만들어 후행테이블인 t2와 데이터를 추출한다

(t2의 total rows 가 10000만건이라면 성능이 그다지 좋지가 않다)

SELECT /*+ LEADING(T1) USE_HASH(T1 T2) */

T1.*,

T2.*

FROM FUNCTION_TABLE T1, C1_CODE_NM T2

WHERE

T1.C2 = 0

AND T1.C3 = 'A'

AND T1.C1 = T2.C1

AND T2.C4 IN (2, 4)

AND T2.C3 = FN_C2_CODENM(T2.C4)

하지만 위 쿼리와같이 t2의 where 상수 조건이 있다면 그만큼 줄여서 데이터를 추출한다

(t2의 c4값이 2와 4인 row가 4만건이라면 해당 수만큼 hash 조인한다)

Nested Loops Join 방식

선행 테이블을 액세스한 후 join 조건으로 후행 테이블을 반복 탐색하며 조인을 수행 한다.

이 때 선행 테이블에서 추출된 건수만큼 반복 수행하게 된다. 따라서 선행 테이블을 상수조건으로

추출한 건수만큼 조인을 시도하여 Function 이 수행된다.

(후행 테이블의 수가 많다 하더라도 선행테이블을 상수조건 만큼 줄여 반복 탐색 하기 때문에 I/O처리량이

hash join 방식 보다 낮아져 성능이 좋게 된다.)

ex)

SELECT /*+ LEADING(T1) USE_NL(T1 T2) */

T1.*,

T2.*

FROM FUNCTION_TABLE T1, C1_CODE_NM T2

WHERE

T1.C2 = 0

AND T1.C3 = 'A'

AND T1.C1 = T2.C1

AND T2.C3 = FN_C2_CODENM(T2.C4)

선행 테이블의 t1의 상수값을 검색한 row 에 반복적으로 t2 후행테이블 값을 조인 시킨다.

(t2가 10000만건이라도 t1의 상수검색한 row 수가 3800건이라면 3800번 조인한다)

SELECT /*+ LEADING(T1) USE_NL(T1 T2) */

T1.*,

T2.*

FROM FUNCTION_TABLE T1, C1_CODE_NM T2

WHERE

T1.C2 = 0

AND T1.C3 = 'A'

AND T1.C1 = T2.C1

AND T2.C4 IN (2, 4)

AND T2.C3 = FN_C2_CODENM(T2.C4)

후행테이블인 t2의 상수조건까지 들어간다면 t2의 상수검색된 row와 join 하기때문에 t2의 상수검색되지 않은 테이블보다

더 조인문이 줄어든다 (예 : 1800건) --> 왜 1800건일까

SELECT count(*)

FROM FUNCTION_TABLE T1, C1_CODE_NM T2

WHERE

T1.C2 = 0

AND T1.C3 = 'A'

AND T1.C1 = T2.C1

AND T2.C4 IN (2, 4)

--AND T2.C3 = FN_C2_CODENM(T2.C4)

위와같이 function으로 검색한 부분을 지우면 rows가 1800건이 나온다.

이러한 이유로, Nested Loops Join 이 성공하자 마자 Function이 수행되지 않는다.

조인 조건으로 조인 성공이 이루어지더라도 후행 테이블에 상수 조건이 잇다면, 조건을 수행하고 만족하는 데이터 건에 대해서만

Function 을 수행하여 데이터를 추출하게 된다는 것을 확인할 수 있다.

NL ( NASTED LOOPS JOIN )

요약 하자면

CASE    조인순서    조인 방법    T1 데이터건수    T2 데이터건수    조인후추출건수    Function수행 횟수

1        T1 -> T2    HASH JOIN    3,846            100,000            768            100,000

2        T1 -> T2    HASH JOIN    3,846            40,000             384            40,000

3        T1 -> T2    NL    JOIN    3,846            100,000            768              3,846

4        T1 -> T2    NL    JOIN    3,846            40,000              384            1,538

5        T2 -> T1    NL    JOIN    3,846            100,000            768            100,000

6        T2 -> T1    NL    JOIN    3,846            40,000              384            40,000

위 표를 참고하여 조인 방법 및 조인 순서를 고려해 Select 절 Function 수행횟수를 최적화 하여 SQL 성능 문제 해결에

도움이 되길 바란다.