Chapter06.Function 수행과 SQL 성능 문제 이해하기

 

01234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556

Chapter06.Function 수행과 SQL 성능 문제 이해하기 Part.01.pdf

Chapter06.Function 수행과 SQL 성능 문제 이해하기 Part.02.pdf

=====================================================================================
------------------FUNCTION 수행과 SQL 성능문제 이해하기--------------------------------
=====================================================================================
DROP TABLE FUNCTION_TABLE PURGE;
DROP TABLE C1_CODE_NM PURGE;
DROP TABLE C2_CODE_NM PURGE;

< FUNCTION_TABLE >

■ 생성 요건
- 테이블 데이터 건수는 100000 ROWS
- 칼럼 C1은 값의 종류가 100000가지 즉 UNIQUE 성
- 칼럼 C2는 값의 종류가 2가지

■ 테이블 생성
 CREATE TABLE FUNCTION_TABLE AS
  SELECT LEVEL C1,
         MOD(LEVEL, 2)  C2,
         CHR(65+MOD(LEVEL,26)) C3,
         MOD(LEVEL, 3) +1 C4
    FROM DUAL
CONNECT BY LEVEL <= 100000;

■ 각 칼럼에 인덱스 생성 및 통계정보 수집

CREATE UNIQUE INDEX IDX_FUNCTION_TABLE_C1 ON FUNCTION_TABLE(C1);
CREATE INDEX IDX_FUNCTION_TABLE ON FUNCTION_TABLE(C2,C3);
CREATE INDEX IDX_FUNCTION_TABLE_C4 ON FUNCTION_TABLE(C4);

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'FUNCTION_TABLE',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

[칼럼 C1에 대한 코드성 테이블 생성 및 인덱스 생성]
< C1_CODE_NM >

■ UNIQUE한 C1값에 대한 코드성 테이블 생성
CREATE TABLE C1_CODE_NM AS
SELECT LEVEL C1,
       LEVEL||'C2 CODE VALUE' C2,
       CHR(65+MOD(LEVEL,20)) C3,
       MOD(LEVEL,5) C4
FROM DUAL
CONNECT BY LEVEL <= 100000;

■ 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_C1_CODE_NM ON C1_CODE_NM(C1);

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'C1_CODE_NM',
CASCADE=>TRUE,ESTIMATE_PERCENT=>100)  ;

[칼럼 C2에 대한 코드성 테이블 생성 및 인덱스 생성]
< C2_CODE_NM >

■ 3가지 값에 대한 코드성 테이블 생성
CREATE TABLE C2_CODE_NM AS
SELECT MOD(LEVEL, 3) C1,
        CHR(65+MOD(LEVEL,3)) C2
FROM DUAL
CONNECT BY LEVEL <= 3;

■ 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_C2_CODE_NM ON C2_CODE_NM(C1);

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'C2_CODE_NM',
CASCADE=>TRUE,ESTIMATE_PERCENT=>100)  ;

[ FUNCTION 생성 ]
■ 칼럼 C1 값에 대한 코드명을 가져오는 FUNCTION 생성
CREATE OR REPLACE FUNCTION FN_C1_CODENM(P_CODE NUMBER)
 RETURN VARCHAR2 IS
                    V_P_CODE_NM VARCHAR2(100);
BEGIN
      SELECT C2 INTO V_P_CODE_NM
        FROM C1_CODE_NM
       WHERE C1 = P_CODE;
      RETURN V_P_CODE_NM;
END;

■ 칼럼 C2 값에 대한 코드명을 가져오는 FUNCTION 생성
CREATE OR REPLACE FUNCTION FN_C2_CODENM(P_CODE NUMBER)
  RETURN VARCHAR2 IS
                     V_P_CODE_NM VARCHAR2(100);
BEGIN
      SELECT C2 INTO V_P_CODE_NM
        FROM C2_CODE_NM
       WHERE C1 = P_CODE;
      RETURN V_P_CODE_NM;
END;
/

FUNCTION 기본내용들 이해하기
USER DEFINED FUNCTION(사용자 정의 함수)의 의미
Oracle은 자주 사용되는 TO_CHAR, NVL 함수와 같은 내장 합수를 제공하여 SQL 작성 시
편리함을 지원하고, 사용자가 필요에 의해 User Defìned Function (이하 Function)
을 생성하여 사용.
Function은 사용 목적에 맞게 직접 작성하는게 가능
대부분의 Function은 스칼라 서브쿼리로 구현이 가능하지만, Function을 사용하는 이유는
스칼라 서브쿼리에 비해 사용할 때 얻을 수 있는 장점 때문이다.

USER DEFINED FUNGION의 특징과 장점
특징
1.리턴값이있다.
2.데이터베이스 객체로 저장되어, 컴파일 된 상태에서 수행된다.
3.단독적인 사용보다, SQL에서 많이 수행된다.
4.예외 처리가 가능하다.
장점
1.모률화된 프로그램이 가능하다.
2.변수 및 다양한 제어 문사용이 가능해서 복잡한 비즈니스 로직도 쉽게 구현이 가능하다.
3.WAS 서버와 네트워크 부하를 줄일 수 있다.
4.유지보수 측면에서 매우 효율적이다.

"WAS 서버와 네트워크 부하를 줄일 수 있다."

DB 서버에서 추출된 데이터를 WAS 서버로 가져와 가공(연산)한 후 클라이언트 PC에 결과를
출력하는 프로그램이 있다고 하자. 이런 프로그램 중 DB 서버에서 많은 데이터가
추출되지만 클라이언트 PC에 줄력되는 데이터가 적은 경우의 프로그램은 크게 두 가지 성능 문제를
발생시킬 수 있다.
첫째: DB 서버에서 추출된 많은 데이터를 WAS 서버의 물리 메모리에 적재한 후 가공해야
하므로 이때 WAS 서버의 물리 메모리와 CPU 사용률이 증가.
둘째: DB 서버에서 추출된 많은 데이터를 WAS 서버로 전송해야 하므로 데이터 전송량과
전송 횟수가 늘어 프로그램의 성능 저하와 네트워크 트래픽 증가의 원인이 될 수 있다.

위 프로그램에 Function을 사용하면 두 가지 문제는 최소화 할 수 있을 것이다.
DB 서버에서 추출된 데이터를 직접 가공한 후 가공된 데이터만 WAS 서버로 전송하도록
Function을 생성하고 사용하면 된다.

위 프로그램이 빈번하게 수행된다면 Function의 수행 횟수가 과도하게 증가하여
DB 서버의 CPU 사용률을 높이는 비효율을 발생시킬 수 있으므로 상황에 따라 적절한 방법을 선택

"유지보수 측면에서 매우 효율적이다."
공통 업무에서 사용하는 SQL을 변경해야 한다면 공통 SQL을 사용하는 모든 프로그램 소스를 찾아
수정해야 하지만 공통으로 사용하는 SQL인 만큼 너무 광범위하게 사용되다 보니 변경하지 못한
프로그램이 있어 문제가 발생할 수 있다.
가장 큰 문제는 잘못 추출된 데이터로 트랜잭션까지 처리하여 데이터 무결성을 훼손시킬 수 있다는 것
예: 일반 사용자가 제품을 구매할 때 부가세가 별도로 부과된다면 구매 시 제품 가격에 부가세를
더한 금액을 보여줘야 한다. 이 계산식을 SQL에 직접 기술하였다면 부가세가 10%에서 11%로 인상될 경우
계산식을 사용한 모든 SQL을 찾아 수정해야 하지만 SQL이 아닌 Function을 사용하였다면
Function 소스만 수정하여 재컴파일 하면 수정 내역이 모든 SQL에 반영되기 때문에 유지보수
측면에서 매우 효율적이다.

USER DEFINED FUNCTION의 종류와 사용법
• NOT DETERMINISTIC FUNCTION
특별한 옵션을 주지 않을 경우 Default로 생성되는 Function으로 입력 값을 받아 결과 값을
리턴하는 기본적인 Function. SQL의 Select절에 사용 할 경우 최대 SQL의 추출 데이터 만큼
Function을 반복 수행하므로 추줄 데이터가 많은 배치 프로그램에 사용될 경우 성능에
악영향을 줄 수 있다.

Function 작성 방법.
CREATE [OR REPLACE] FUNCT10N [함수 명]
([ paraml [mode] data_typel]
[,param2 [mode] data_type2]
[,param3 [mode] data type3]
return data_type; ---> 크기 지정은 불가
IS
변수 선언         ---> FUNCTION 내에서 사용할 변수를 선언 한다.
BEG1N
 실제 PL/SQL Program
 return (변수);
 exception
  .........
 
END;

• CREATE[OR REPLACE FUNUION: Function을 생성 및 재생성 할 때 사용함
• 함수 명: 데이터베이스 내에 저장될 Function 이름
• param: Function 내로 어떤 값을 전달할 때 매개변수 명
• mode: 매개변수의 역할을 의미하며 IN, OUT, IN OUT 3 가지 중 선택
• data_type: param 변수의 데이터 타입
• exception: 특정조건에 대한 예외처리 부분
• begin - end: Function의 시작 ~ 끝을 의미
예제를 통해 실제로 Function을 생성 및 사용해보자.

예) 부서번호를 입력 받아 부서명을 리턴하는 FUNCTION 생성
CREATE OR REPLACE FUNCTION dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 IS /*--> RETURN 값에 대해 아무런 옵션이 주어지지 않는다. */
pdeptnm VARCHAR2(32);
BEGIN
SELECT dname INTO pdeptnm
FROM dept
WHERE deptno pdeptno;
RETURN pdeptnm;
EXCEPTION WHEN NO_DATA_FOUND THEN pdeptnm := "
 RETURN pdeptnm
END dept_nm;
/

Function을 생성한 후에 아래와 같이 Select 절에 사용할 수 있다.
SELECT empno, ename, deptno, dept_nm(deptno) dname
FROM emp;

DEPT 테이블의 부서명은 DEPT_NM Function을 수행하여 추출
Function은 추출 데이터 건수만큼 13번 수행.

• DETERMINISTIC FUNCTION
입력 값이 같다면 리턴 값도 항상 같음을 보장하는 Function
Not Deterministic Function은 추출 데이터 만큼 반복 수행.
추출 데이터가 100만건인 배치 프로그램에 Not Deterministic Functio을 사용 할 경우
100만번 수행되어야 한다.

Function은 1회 수행 당 Recursive call로 1번의 파싱이 발생되므로 파싱도 총 100만 번
수행해야 하므로 소프트 파싱의 부하가 크고 반복적인 블록 액세스로 Hot Block 에 의한 비효율이 발생
Deterministic Function을 사용하면 입력 값에 해당하는 컬럼 값의 종류(NUM_DIST1NCT 값)가 적다면
자연스럽게 입력 값이 동일한 경우가 많아 Function을 매번 수행하지 않고 메모리 내에 Cache된
결과 값을 바로 가져와 성능이 크게 개선.

Deterministic Function을 사용할 때 입력 값에 대한 Cache는 스칼라 서브쿼리와는
다르게 SQL Level이 아닌 Fetch Level에서 이루어 진다.
Cache 효과를 극대화하려면 FETCH ARRAY SIZE를 적절하게 설정.

Deterministic Function을 생성
예) 부서번호를 부서명으로 리턴하는 DETERMINISTIC FUNCTION 생성
CREATE OR REPLACE FUNCTI0N d_dept_nm(pdeptno IN NUMBER)
RETURN VARCHAR2 DETERMINISTIC
IS
pdeptnm VARCHAR2(32);
BEGIN
SELECT dname INTO pdeptnm
FROM dept
WHERE deptno pdeptno;
RETURN pdeptnm;
EXCEPT10N WHEN no data found THEN
pdeptnm :='';
RETURN pdeptnm;
END d_dept_nm;
/

Deterministic Function은 Not Deterministic Function을 작성할 때와 모두 동일
하고 RETURN절 선언부에 DETERMINIST1C 옵션만 추가
Deterministic Function을 생성한 후 아래와 같이 Select절에서 사용.
SELECT empno, ename, deptno, d_dept_nm(deptno) dname
FROM emp;

위 SQL에 사용된 Function은 입력 값에 대해서 Cache 된 값을 이용하므로 Function의
수행 횟수가 13번이 아닌 입력 값의 종류(10,20,30) 만큼 총 3번 수행.

•PIPELlNE TABLE FUNCTlON
Pipeline Table Function은 Multi Column + Multi Row 형태를 입력값으로 받아 들여,
값을 리턴할 수 있는 Function으로 9i 이전에 사용했던 Table Function과는 다르게
전체범위 처리하지 않고 부분범위 처리가 가능.
[PIPEUNE TABLE FUNCTION 생성 방법]
Pipeline Table Function은 다음과 같은 절차로 생성.
1.데이터를 담을 User Object Type을 생성.
2.데이터를 담기위해 1에서 만든 Type을 이용해 테이블 형태(2차원 배열 형태)의 Type을 생성.
3.Pipeline Table Function을 생성.

먼저 데이터를 담을 Type을 생성한다.
CREATE TYPE table_type_row AS OBJECT(
SEQ NUMBER,
DTL VARCHAR2(50));
/

NUMBER 타입의 SEQ와 VARCHAR2(50) 타입의 DTL 컬럼을 가진 TABLE_TYPE_ROW 이름으로 Type을 생성.
CREATE TYPE table_type_tab IS TABLE OF table_type_row;

TABLE_TYPE_ROW를 이용해 2차원 배열 형태의 Type을 생성
데이터를 담을 Type의 선언은 완료
Pipeline Table Function을 생성할 때 일반적인 Function을 생성하는 방법과 동일
RETURN절에 PIPELINED 옵션을 선언하고, 데이터를 추출할 때 PIPE ROW함수를 사용하여
ROW 단위로 데이터를 추출.
예)값을 입력 받을 수만큼 반복하여 추출하는 PIPELINE TABLE FUNCTION
CREATE OR REPLACE FUNCTION pipe_table(end_num IN NUMBER)
RETURN table_type_tab pipelined AS
BEGIN
 FOR i IN 1..end_num LOOP
  dbms_output.put line('count==> ' ||i);         -- dbms_output으로 출력
  pipe row(table_type_row(i,'count for '|| i));  -- 매건 마다 줄력
 END LOOP;
RETURN;
END;
/

아래는 Pipeline Table Function의 특징을 알아 보기 위해 입력 값에 10을 기입하고
SEQ 컬럼 값에 대해 내림차순으로 정렬 처리한 SQL이다.

SELECT *
FROM TABLE( pipe_table(10))
ORDER BY seq DESC;

Function 생성 구문을 보면 PIPE ROW 전에 DBMS_OUTPUT을 먼저 수행하도록 작성
그런데 PIPE ROW로 추출된 데이터가 먼저 출력
왜냐하면 Pipeline은 로우 단위로 매번 출력하지만 DBMS_OUTPUT 패키지를 사용한
출력은 Function이 모두 처리된 후에 할 수 있기 때문이다.
Pipeline table Function이 매건 마다 추출되어 부분범위 처리가 가능

SELECT *
FROM TABLE(pipe_table(10))
WHERE ROWNUM <= 5;

전체범위 처리가 되었다고 가정하면 PIPE ROW는 10까지 값을 가져온 후 ROWNUM 조건에 의해
5까지의 데이터만 출력했을 것이다. 그러나 Function이 모두 수행된 후 일괄적으로
처리하는 DBMS_OUTPUT을 보면 5까지의 데이터만 추출
Function 은 1부터 10까지 Loop를 수행하게 되어 있지만 Where절에 ROWNUM 조건이 있는 경우
조건에 만족하는 데이터만 추출하고 수행이 멈춘다.
실행계획을 보면 COUNT STOPKEY 오퍼레이션이 있으므로 부분범위 처리로 수행

FUNCTION 동작방식 이해하기
SELECT절에 시용하는 FUNCTION의 동작방식
Select절에 사용된 Not Deterrninistic Function의 경우 Main SQL의 추줄 건수만큼
Function이 반복적으로 수행
여러 유형의 트레이스 결과를 통해 Function이 어떻게 수행되는지 확인하자.

Script. Function 테스트용을 이용하여 데이타 생성
=====================================================================================
------------------FUNCTION 수행과 SQL 성능문제 이해하기------------------------------
=====================================================================================
DROP TABLE FUNCTION_TABLE PURGE;
DROP TABLE C1_CODE_NM PURGE;
DROP TABLE C2_CODE_NM PURGE;

< FUNCTION_TABLE >

■ 생성 요건
- 테이블 데이터 건수는 100000 ROWS
- 칼럼 C1은 값의 종류가 100000가지 즉 UNIQUE 성
- 칼럼 C2는 값의 종류가 2가지

■ 테이블 생성
 CREATE TABLE FUNCTION_TABLE AS
  SELECT LEVEL C1,
         MOD(LEVEL, 2)  C2,
         CHR(65+MOD(LEVEL,26)) C3,
         MOD(LEVEL, 3) +1 C4
    FROM DUAL
CONNECT BY LEVEL <= 100000;

■ 각 칼럼에 인덱스 생성 및 통계정보 수집

CREATE UNIQUE INDEX IDX_FUNCTION_TABLE_C1 ON FUNCTION_TABLE(C1);
CREATE INDEX IDX_FUNCTION_TABLE ON FUNCTION_TABLE(C2,C3);
CREATE INDEX IDX_FUNCTION_TABLE_C4 ON FUNCTION_TABLE(C4);

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'FUNCTION_TABLE',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

[칼럼 C1에 대한 코드성 테이블 생성 및 인덱스 생성]
< C1_CODE_NM >

■ UNIQUE한 C1값에 대한 코드성 테이블 생성
CREATE TABLE C1_CODE_NM AS
SELECT LEVEL C1,
       LEVEL||'C2 CODE VALUE' C2,
       CHR(65+MOD(LEVEL,20)) C3,
       MOD(LEVEL,5) C4
FROM DUAL
CONNECT BY LEVEL <= 100000;

■ 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_C1_CODE_NM ON C1_CODE_NM(C1);

EXEC dbms_stats.gather_table_stats(OWNNAME=>'ORACLE',TABNAME=>'C1_CODE_NM',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

[칼럼 C2에 대한 코드성 테이블 생성 및 인덱스 생성]
< C2_CODE_NM >

■ 3가지 값에 대한 코드성 테이블 생성
CREATE TABLE C2_CODE_NM AS
SELECT MOD(LEVEL, 3) C1,
        CHR(65+MOD(LEVEL,3)) C2
FROM DUAL
CONNECT BY LEVEL <= 3;

■ 인덱스 생성 및 통계정보 수집
CREATE UNIQUE INDEX IDX_C2_CODE_NM ON C2_CODE_NM(C1);

EXEC dbms_stats.gather_table_stats(OWNNAME=>'ORACLE',TABNAME=>'C2_CODE_NM',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

[ FUNCTION 생성 ]
■ 칼럼 C1 값에 대한 코드명을 가져오는 FUNCTION 생성
CREATE OR REPLACE FUNCTION FN_C1_CODENM(P_CODE NUMBER)
 RETURN VARCHAR2 IS
                    V_P_CODE_NM VARCHAR2(100);
BEGIN
      SELECT C2 INTO V_P_CODE_NM
        FROM C1_CODE_NM
       WHERE C1 = P_CODE;
      RETURN V_P_CODE_NM;
END;
/

■ 칼럼 C2 값에 대한 코드명을 가져오는 FUNCTION 생성
CREATE OR REPLACE FUNCTION FN_C2_CODENM(P_CODE NUMBER)
  RETURN VARCHAR2 IS
    V_P_CODE_NM VARCHAR2(100);
BEGIN
      SELECT C2 INTO V_P_CODE_NM
        FROM C2_CODE_NM
       WHERE C1 = P_CODE;
      RETURN V_P_CODE_NM;
END;
/

CASE [1]. 10만 건올 가진 테스트 테이블올 조건 없이 조회한 경우
SELECT c1,
 fn_c1_codenm(c1) c2,
 c3
FROM FUNCTION_TABLE;
--Trace 결과 참고

트레이스 결과: Main SQL의 추출 데이터는 총 10만건. CPU Time / Elapsed Time은 4초 이상 소요
읽은 총 블록이 6,858블록. 블록 I/O 처리량만 보면 효율적으로 수행한 것처럼 보인다.
SQL 트레이스 결과만으로 효율적인지 판단하기는 부족.
- Function의 수행 결과가 합산되지 않았기 때문.
Function을 사용한 SQL의 트레이스는 Main SQL 과 Function의 수행 결과가 따로 있기 때문에 둘다 확인
- Main SQL의 트레이스 결과만 보고 성능을 분석하는 것은 부정확한 성능 분석 결과가 도출

정확한 판단을 위해 Function의 수행 결과 확인.

[FUNCTION의 수행 내역]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
--Trace 결과 참고

Function의 수행 결과: Execute는 10만번으로 Main SQL의 추출 데이터인 총 10만건과 동일한 횟수
Function을 10만번 수행하는데 소요된 시간이 9.3초
Function을 수행하면서 총 30만 블록을 처리
Function이 1회 수행 당 소요되는 시간은 0.000093초, 1회 수행 당 읽어야 할 블록은 3블록
- 1회 수행시의 성능은 매우 양호, 10만번이라는 수행 횟수가 SQL의 성능을 비효율로 만들었다.

CASE [2]. WHERE절올 추가하여 추출 데이터롤 줄인 경우
SELECT c1, FN_C1_CODENM(c1) c2, c3
FROM FUNCTION_TABLE
WHERE c2 = 0
  AND c3 = 'A;

CASE[l]은 Where절 없이 수행하여 FUNCTION_TABLE의 데이터 건수만큼 Function이 10만번 수행
CASE[2]는 Where절에 C2=0 AND C3='A' 조건을 추가
CASE[2]에서 Function의 수행 횟수가 CASE[l]과 같이 10만번 수행될까?

[FUNCTION의 수행 내역]
SELECT c2
  FROM C1_CODE_NM
 WHERE c1 = :b1;
 
CASE[l]에서 추줄 건수가 10 만건 일때 Function 이 10 만번 수행된 것과 비교해 보면
CASE[2]의 경우는 수행횟수가 3,846 번으로 크게 감소했다. Fucntion 의 수행 횟수와 CASE[2]의 총 추출 건수는 일치.
Function이 수행되는 시점은 Where절을 만족하는 데이터를 Fetch할 때 수행하는 것일까?
CASE[2]와 동일한 조건을 가진 SQL에 ROWNUM = 1 조건을 추가하여 1 건만 추출하도록 하였다.
데이터를 Fetch 할 때 Function이 수행된다면 1건만 Fetch 하기 때문에 Function도 한번만 수행될 것이다.

CASE [3]. ROWNUM = 1 조건을 추가하여 1 건이 추출되도록 유도
SELECT c1, FN_C1_CODENM(c1) c2, c3
FROM FUNCTION_TABLE
WHERE c2 = 0
  AND c3 'A' AND ROWNUM = 1
--Trace 결과 참고
트레이스 결과를 보면 SQL의 추줄 데이터는 1건이다. Function의 수행 내역도 확인

[FUNCTION의 수행 내역]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
--Trace 결과 참고

추출 데이터도 1건, Function도 1번 수행. Select절에 사용되는 Function의 경우 데이터 Fetch시에
수행되는 것을 의미.

WHERE절에 사용하는 FUNCTION의 동작방식
Where절에 쓰인 Function은 조인 순서, 조인 방법, Where절에 의해 추출된 데이터 건수 등
다양한 요소에 따라 Function의 동작 방식이 조금 더 복잡한 형태를 가진다.
6가지 CASE로 Where절에 사용된 Function의 동작 방식을 알아보자.

CASE[1]. T1을 먼저 수행하고 HASH JOIN 처리할 경우
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);

트레이스 결과: TOTAL 부분을 보면 총 추줄 건수: 768건, I/O 처리량 730블록
SQL 내에 Function이 존재하는 경우 Recursive Call에 대한 분석이 반드시 필요.
"Select절에 사용된 Function 처럼 추출 건수만큼(768번)수행될까?"

[FUNCTION 의 수행 내역]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1;

Function은 총 10 만번 수행
- Function을 호출하는 T2 테이블의 데이터를 줄여주는 상수 조건이 없고, T1과 T2의 조인 방식이
Hash Join이기 때문

Hash Join 개념
Hash Join은 먼저 수행하는 테이블(선행 테이블)에 대해 상수 조건으로 데이터를 걸러낸 후,
조인 키 컬럼으로 Hash Map을 생성. 그 다음 Probe Table(후행 테이블)을 수행
이때 상수 조건이 있다면, 먼저 상수 조건으로 대상을 줄인다. 그 이후 선행 테이블이 생성
해 놓은 Hash Map에 있는 데이터 중에서 조인에 성공한 데이터를 추출.
CASE[l]의 테이블 T1은 Tl.C2 = 0 AND Tl.C3 = 'A' 조건으로 데이터를 줄일 수 있지만
T2는 상수 조건은 없고, T2.C3 = FN_C2_CODENM(T2.C4) 조건이 있다.
그런데 FN_C2_CODENM(T2.C4)의 결과 값은 T1과 T2가 Hash Join을 하기 전에 추출되어야 한다.
그러므로 T2 테이블의 전체 데이터 건수만큼 Function이 수행된 것.
- Hash Join으로 수행될 때 Where절에 있는 Function은 조인을 처리하기 전에 데이터를 걸러낼 때
수행되었음을 알 수 있다. 10만건에 대해 모두 Function 을 수행하며, 해당 조건을 만족하는
데이터는 15,000건, 이 결과를 가지고 Hash Join을 하면 데이터는 768건으로 줄게 된다.

CASE [2]. WHERE절에 T2 테이블의 조건을 추가
SELECT /*+ LEADING(Tl) USE_HASH(Tl T2) */
tl.*,
t2.*
FROM FUNCTION_TABLE tl,
Cl_CODE_NM t2
WHERE t1.c2 = 0
  AND tl.c3 = 'A'
  AND tl.cl = t2.cl
  AND t2.c4 IN (2, 4)
  AND t2.c3 FN_C2_CODENM(t2.c4);
--Trace 내용 참고

조인 방법과 조인 순서는 CASE[l]과 동일하지만, 테이블 T2에 T2.C4 IN (2, 4)이란 상수 조건을 추가

[FUNCTION의 수행 내역]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1;
--Trace 내용 참고

"T2.C4 IN (2, 4)" 조건을 추가: Function 수행 횟수가 10만번에서 4만번으로 줄었다.
- T2.C3 = FN_C2_CODENM(T2.C4)을 수행하기 전에 T2.C4 IN (2, 4) 조건으로 추출된 4만건에 대해서만
추가적으로 Function을 수행했기 때문.

CASE [3]. 조인 방법이 NESTED LOOPS JOIN 인 경우
SELECT /*+ LEADING(Tl) USE_NL(Tl T2) */
tl.*,
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);
--Trace 내용 참고

CASE[3] 트레이스 결과: CASE[l]과 비교해 보면, 조인 방법이 바뀌었을 뿐 총 추출 건수는 786건으로 동일
I/O 처리량은 CASE[l] 에 비해 10배 이상 증가(약 8,000 블록)
블록 수가 증가했기 때문에 CASE[l]보다 비효율적이라고 판단
- Function이 있는 경우 Function의 수행 내역까지 반드시 분석

[FUNCTION 의 수행 내역]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :B1;

트레이스 결과: CASE[l]에서는 Function을 10만번 수행, 조인 방법이 바뀌자 Function의
수행 횟수가 3,846번으로 크게 감소. 동일한 SQL 임에도 불구하고 CASE[l]과 CASE[3]의
Function 수행 횟수에 큰 차이가 발생한 것
- Nested Loops Join 과 Hash Join의 처리 방식 때문. Nested Loops Join은 선행 테이블을
액세스한 후 조인 조건으로 후행 테이블을 반복 탐색하며 조인을 수행하는데, 선행 테이블에서
추출된 건수만큼 반복 수행. 따라서 T1을 상수 조건으로 추출한 데이터 건수만큼 조인을 시도하여
Function이 3,846번 수행하게 된 것.
조인에 실패했거나, 별도의 상수 조건으로 데이터가 걸러졌다면, 해당 데이터에 대해서는 Function을
수행할 필요가 없다.
- Function은 조인까지 모두 성공한 건들에 대해서 가장 마지막에 수행하며 데이터를 추출해도 되기 때문

CASE[4]. T2에 T2.C4 IN (2,4) 상수 조건을 부여한 경우
SELECT /*+ LEADING(Tl) USE_NL(Tl T2) */
tl.*,
t2.*
FROM FUNCTION_TABLE Tl,
  Cl_CODE_NM T2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND tl.cl = t2.Cl
AND t2.c4 IN (2, 4)
AND t2.c3 = FN_C2_CODENM(t2.c4);

트레이스 결과: 선행 테이블의 건수는 CASE[3]과 동일, CASE[3]에 비해 후행 테이블에 조건이 추가
추출 데이터가 절반으로 감소한 것 이외에는 별다른 차이가 없는것 처럼보인다.

[FUNCTION 의 수행 내역]
SELECT c2
FROM C2_CODE_NM
WHERE cl = :b1;

Function의 수행 내역: Function 수행 횟수가 1,538번으로 CASE[3]에 비해 절반 가까이 감소
왜 수행 횟수가 감소했을까?
SELECT COUNT (* )
FROM FUNCTION_TABLE T1
WHERE tl.c2 = 0
AND tl.c3 = 'A';

SQL의 추출 건수는 3,846건이다. T1의 추출 데이터는 3,846건으로 동일
CASE[3]에 비해 선행 테이블에 대해 별도의 조건이 추가 되거나 변경된 것이 없으니 당연한 결과
Nested Loops Join의 특성상 조인 키를 이용하여 3,846번 반복하여 조인을 시도해야 하는 점에서는
CASE[3]과 전혀 차이가 없다.

SELECT /*+ LEADING(T1) USE_NL(T1 T2) */
COUNT(*)
FROM FUNCTION_TABLE T1,
C1_CODE_NM T2
WHERE T1.C2 = 0
  AND Tl.C3 = 'A'
  AND T1.C1 = T2.C1
  AND T2.C4 IN (2, 4)
--AND t2.c3 = FN_C2_CODENM(t2.c4) 주석 처리.

위 SQL은 Funetion이 사용된 조건만 주석 처리: 추출 데이터가 1,538 건으로 Funetion 수행 횟수와 일치.
CASE[3]에서 언급: Nested Loops Join이 성공하자 마자 Funetion이 수행되지 않는다는 것을 의미
조인 조건으로 조인 성공이 이루어지더라도 후행 테이블에 상수 조건이 있다면, 조건을 수행하고
만족하는 데이터 건에 대해서만 Funetion을 수행하여 데이터를 추출하게 된다는 것을 확인.
정리: 두 테이블이 조인 조건으로 조인이 성공된 3,846건 중에서 T2.C4 IN (2, 4) 조건을
만족한 1,538건에 대해서만 Function이 수행된 것. Function을 수행하면서 Function이 사용된 조건을
처리하고 나면, 최종적으로 769건을 추출

CASE[5]는 CASE[3]과 동일한 SQL이며, 조인 방법은 CASE[3]과 동일하게 Nested Loops Join을 하도록 유도
단지 먼저 수행할 테이블을 T1에서 T2로 변경하여 수행되도록 힌트를 부여한 SQL.
CASE[3]과 동일하게 Function이 3,846번 수행했을까? 트레이스 결과와 Function 수행 내역을 확인

CASE[5]. T2부터 수행하고 NESTED LOOPS JOIN율 선택할 경우
select /*+ LEADING(T2) USE_NL(T2 T1) */
t1.*,
t2.*
FROM FUNCTION_TABLE T1,
     C1_CODE_NM T2
WHERE T1.C2 = 0
  AND Tl.C3 = 'A'
  AND T1.C1 = T2.C1
  AND T2.C4 IN (2, 4)
  AND t2.c3 = FN_C2_CODENM(t2.c4);

[FUNCTION의 수행 내역]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1;

Function의 트레이스 결과: Function 수행 횟수가 10만번
테이블 T2를 먼저 수행한 경우, 데이터를 줄일 수 있는 상수 조건이 존재하지 않는다.
T2 테이블 전체 데이터에 대해 "AND t2.c3 = FN_C2_CODENM(t2.c4)" 조건을 처리해야 하므로
Function이 10만번 수행
"ÄND t2.c3 = FN_C2_CODENM(t2.c4)"에 의해 추출된 15,000건은 T1 테이블과 Nested Loops Join을 수행

CASE[6]. T2부터 읽고 조인 방법은 NESTED LOOPS JOIN를 선택하며 상수 조건이 추가 될 경우
SELECT /*+ LEADING(T2) USE_NL(T2 Tl) */
t1.*,
t2.*
FROM FUNCTION_TABLE tl,
     C1_CODE_NM t2
WHERE t1.c2 = 0
  AND t1.c3 = 'A'
  AND t1.c1 = t2.Cl
  AND t2.c4 IN (2, 4)
  AND t2.c3 FN C2 CODENM(t2.c4);

CASE [5]에 비해 T2에 상수 조건이 추가. T2의 추출 데이터가 15,000건에서 5,000건으로 감소.
Nested Loops Join에서 반복 탐색 횟수가 줄어 들어 Function 수행을 제외한 I/O 처리량도 3만 블록에서
1만 블록으로 개선. Function 의 수행 횟수도 감소했는지 수행 내역 확인.

[FUNCTION 의 수행 내역]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1;

CASE[5]에서 10만번 수행했던 것과는 달리 CASE[6]에서는 Function이 4만번만 수행
CASE[5]에서 언급했듯이 상수 조건이 있다면 Function이 존재하는 조건을 처리하기 전에 데이터를
줄여 놓는다.
"SELECT count(*) from C1_CODE_NM t2 where T2.C4 IN (2, 4)"의 결과 값이 4만으로 Function의 수행 횟수와 일치
T2를 먼저 수행 할 때 T2.C4 IN (2, 4) 조건을 처리하여 데이터를 걸러 낸 후, 4만건에 대해서만
T2.C3 = FN_C2_CODENM(T2.C4) 조건을 처리하여 5,000건이 최종 추줄된 것
SQL을 수행하면 정확히 5,000건임을 알 수 있다.

SELECT COUNT(*)
FROM Cl_CODE_NM t2
WHERE T2.C4 IN (2, 4)
AND T2.C3 FN_C2_CODENM(T2.C4)

조인 방법, 조인 순서, 상수 조건에 따라 Function의 수행 횟수도 차이가 발생하는 것을 확인 했다.
테스트 SQL들은 조인 순서가 T1 -> T2 순서로 조인 시 Nested Loops Join 방식을 선택할 때 가장
성능이 좋다는 것을 알 수 있다. SQL의 Where 절에 Function이 있다면, 조인 방법 및 조인 순서를
고려해 Function 수행 횟수를 죄적화하여 SQL의 성능 문제 해결하면 된다.