h3.(1) Dynamic SQL 사용에 관한 기본 원칙
1. Static SQL은 PreCompile 과정을 거치므로 런타임 시 안정적으로 프로그램 빌드가 가능하므로,
Static SQL을 지원하는 개발환경이라면 Static SQL로 작성하는 것을 원칙으로 한다.
2. Dynamic SQL 어플리케이션 커서 캐싱 기능을 정상적으로 사용 하지 못하므로 성능이 나빠짐
1. Pre Compile 과정에서 에러가 발생하는 구문을 사용 하는 경우(ex> 스칼라, 분석함수, 서브쿼리, ANSI조인등..)
2. Static SQL로 작성시 SQL 개수가 많아져 개발에 관한 생산성의 저하로 유지 보수 비용이 많아지는 경우
3. 위의 2에 한해서 Dynamic SQL을 사용 하더라도 조건 절에는 바인드 변수를 사용 (사용 빈도가 높고 값을 종류가 많은 경우)
4. 바인드 변수 사용 원칙을 준수 하되 예외적인 경우
1) Long Running 쿼리 및 쿼리의 파싱 소요 시간, 쿼리 총 소요 시간에서 차지 하는 비중이 매우 낮고 수행 빈도가 낮아 하드 파싱에 의한 라이브러리 캐시 부하가 적은 경우
ex> 배치 프로그램 , 마감 프로그램, DW, OLAP
2) OLTP성의 프로그램이라 하더라도 사용 빈도가 낮아 하드 파싱에 의한 라이브러리 캐시 부하를 주지 않는 경우
3) 조건절에 대한 컬럼 값 종류가 적은 경우(소수 일경우): 데이터의 분포가 균일 하지 않아 옵티마이저의 히스토그램 정보를 활용하도록 유도할 경우
Static SQL이 지원 하지 않는 환경 이라면 모든 SQL은 Dynamic SQL이지만 런타임시 동적으로 SQL이 바뀌는 것을 삼가 해야 한다.
그런 환경에서 Static과 Dynamic SQL을 편의상 아래와 같이 재 정의하고, 위에서 제시한 기본 원칙을 동일하게 적용할 것 을 권고한다.
⊙ Static SQL : Repository에서 완성된 형태로 관리
⊙ Dynamic SQL : Repository에서 불완전한 상태로 관리 되며, 런타임시 필요에 따라 조건에 따라 동적으로 쿼리를 생성하는 SQL
현업과의 충분한 협의를 통하지 하지 않고 다양한 검색 조건으로 화면을 설계 하여 사용자에 따라 검색조건이 동적으로 바뀌는 경우
(대량의 데이터 일 경우 문제가 발생될 우려가 있음: 동적으로 쿼리가 바뀌며 라이브러 캐쉬를 제대로 활용 하지 못하게 되며, 심지어는 필수 입력과 검색기간이 무제한으로 인해 검색 성능에 문제를 가져옴)
=> 해결 방안 : 현업과 협의를 통해 필수입력항목을 수렴해야함. 기간조건에 대해 입력 값 범위를 짧게 할 수 있도록 노력.
검색 조건을 단순화 하여 라이브러리 캐쉬를 최대한 활용 할 수 있도록 유도(반복 사용을 유도함)
사례 1) 필수입력 : 거래일자, 나머지는 선택적 입력조건
(조건을 추가 함에 따라 실행계획이 바뀌며, 라이브러리의 캐쉬에는 별도를 쿼리로 인식 하여 하드 파싱이 일어 나는 경우)
그림4-14
SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
,체결건수, 체결수량, 거래대금
FROM 일별종목거래
WHERE 거래일자 BETWEEN :시작일자 AND :종료일자
%OPTION
필요 선택 조건에 따라 쿼리 조건절이 동적으로 추가됨
%OPTION = " AND 종목코드 = 'KR123456' AND 투자자유형코드 = '1000' "
=>
SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
,체결건수, 체결수량, 거래대금
FROM 일별종목거래
WHERE 거래일자 BETWEEN :시작일자 AND :종료일자
*AND 종목코드 = NVL(:종목코드, 종목코드)*
*AND 투자자유형코드 = NVL(:투자자유형코드, 투자자유형코드)
*AND 주문매체구분코드 = NVL(:주문매체구분코드, 주문매체구분코드)
◆ 장점 : 라이브러리 캐시 효율 측면이 좋다.(사용자의 입력값이 변하더라고 한 개의 실행계획을 공유하면서 반복 재사용하게 되므로)
단점 : 인덱스 활용측면(거래일자가 선두컬럼이 아닌 인덱스의 경우)
=> 라이브러리 캐시 효율과 I/O효율을 모두 고려하려면, 조건의 입력여부에 따라 SQL을 모두 분리해서 개발하는 것이다.
그러면, 3가지 선택적 입력조건을 처리하는 8가지 경우의 SQL을 따로 작성해야한다.
그래서.. 인덱스구성을 고려해 변별력이 좋은 컬럼을 중심으로 IF문이나 union all을 사용해 분기하는 방법을 보자.
<인덱스 구성>
INDEX01 : 종목코드 + 거래일자
INDEX02 : 투자자유형코드 + 거래일자 + 주문매체구분코드
INDEX03 : 거래일자 + 주문매체구분코드
//거래일자 o, 종목코드 o 인경우
SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
,체결건수, 체결수량, 거래대금
FROM 일별종목거래
*WHERE :종목코드 IS NOT NULL*
AND 거래일자 BETWEEN :시작일자 AND :종료일자
AND 종목코드 = :종목코드
AND 투자자유형코드 = NVL(:투자자유형코드, 투자자유형코드)
AND 주문매체구분코드 = NVL(:주문매체구분코드, 주문매체구분코드)
UNION ALL
//거래일자 o, 종목코드 = NULL, 투자자유형 o 인경우
SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
,체결건수, 체결수량, 거래대금
FROM 일별종목거래
*WHERE :종목코드 IS NULL AND :투자자유형코드 IS NOT NULL*
AND 거래일자 BETWEEN :시작일자 AND :종료일자
AND 투자자유형코드 = :투자자유형코드
AND 주문매체구분코드 = NVL(:주문매체구분코드, 주문매체구분코드)
UNION ALL
//거래일자 o, 종목코드 = NULL, 투자자유형 = NULL 인경우
SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
,체결건수, 체결수량, 거래대금
FROM 일별종목거래
*WHERE :종목코드 IS NULL AND :투자자유형코드 IS NULL*
AND 거래일자 BETWEEN :시작일자 AND :종료일자
AND 주문매체구분코드 = NVL(:주문매체구분코드, 주문매체구분코드)
1. Static SQL사용을 윈칙으로함
2. 조건에 따른 SQL 생성 개수가 많은 경우 Dynamic SQL을 허용
3. Dynamic SQL은 바인드 변수 사용을 원칙적으로 준수 해야함.(단 인덱스를 설계시 불편한 단점이 있다)
사례1) if 조건을 이용한 Dynamic 쿼리
SQLStmt := 'SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드'
|| ',체결건수, 체결수량, 거래대금'
|| 'FROM 일별종목거래 '
|| 'WHERE 거래일자 BETWEEN :1 AND :2 ';
IF :종목코드 IS NULL Then
SQLStmt := SQLStmt || 'AND :3 IS NULL ';
Else
SQLStmt := SQLStmt || 'AND 종목코드 = :3 ';
End If;
If :투자자유형 IS NULL Then
SQLStmt := SQLStmt || 'AND :4 IS NULL ';
Else
SQLStmt := SQLStmt || 'AND 투자자유형 = :4 ';
End If;
EXECUTE IMMEDIATE SQLStmt
INTO :A, :B, :C, :D, :E, :F, :G
USING :시작일자, :종료일자, :종목코드, :투자자유형코드;
★ 정리: Static SQL을 작성을 기본으로 하고 방법이 없거나 SQL이 복잡한 경우에는 Dynamic SQL을 사용 해야 함.
Dynamic하게 구성하면, 인덱스 설계시 다소 불편하지만,
결과적으로 수집되는 최종 SQL은 Static Sql과 차이가 없기 때문에 이들 엑세스 경로 기준으로 인덱스 설계가 가능하다.
힌트를 사용해 튜닝이 곤란.
h3.(4) NVL대신에 선택적 검색 조건에 사용 할 수 있는 기법 성능 비교
h5.A. OR 조건을 사용 하는 경우
select * from 일별종목거래
where (:isu_cd is null or isu_cd = :isu_cd)
Execution Plan
--------------------------------------------------------------------------
0 SELECT STATEMENT optimizer=ALL_ROWS
1 0 TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
--------------------------------------------------------------------------
항상 TABLE FULL SCAN으로 처리가되므로 인덱스를 활용 할 경우는 이 방식을 사용해서는 안된다.
h5.B. LIKE 연산자를 사용한 경우
select * from 일별종목거래
where isu_cd like :isu_cd || '%'
Execution Plan
--------------------------------------------------------------------------
0 SELECT STATEMENT optimizer=ALL_ROWS
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
2 1 INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
--------------------------------------------------------------------------
인덱스 사용이 가능하지만 사용자가 :ISU_CD 값을 입력하지 않았을 때
TABLE FULL SCAN이 유리함에도 인덱스를 사용하게 되므로 성능이 나빠질 수 있다.
h3.C NVL함수를 사용한 경우
select * from 일별종목거래
where isu_cd = nvl(:isu_cd, isu_cd)
Execution Plan
--------------------------------------------------------------------------
0 SELECT STATEMENT optimizer=ALL_ROWS
1 0 CONCATENATION
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4 1 FILTER
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6 5 INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
--------------------------------------------------------------------------
h3.D. DECODE를 사용한경우
select * from 일별종목거래
where isu_cd = decode(:isu_cd, null, isu_cd, :isu_cd)
Execution Plan
--------------------------------------------------------------------------
0 SELECT STATEMENT optimizer=ALL_ROWS
1 0 CONCATENATION
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4 1 FILTER
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6 5 INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
--------------------------------------------------------------------------
C와 D 방식은 사용자의 입력여부에 따라 실행계획이 자동 분기된다.
단, nvl또는 decode 사용 시 해당 컬럼은 not null이어야 한다.
(조건절이 isu_cd = isu_cd가 될때, isu_cd가 null이면 false를 리턴하기 때문, DBMS에 따라 NULL 비교가 될수도 있다)
h3.E. UNION ALL을 사용한 경우
select * from 일별종목거래
where :isu_cd is null
union all
select * from 일별종목거래
where :isu_cd is not null
and isu_cd = :isu_cd
Execution Plan
--------------------------------------------------------------------------
0 SELECT STATEMENT optimizer=ALL_ROWS
1 0 UNION-ALL
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4 1 FILTER
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6 5 INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))
--------------------------------------------------------------------------
1. NOT NULL 컬럼일경우는 NVL, DECODE를 사용 하는 것이 좋다.
2. NULL 값을 허용 하고 있는 검색 조건이라면 UNION ALL를 사용 하여 명시적으로 분기해야 한다.
3. 인덱스 엑세스 조건으로 참여 하지 않는 경우, 즉 인덱스 필터 또는 테이블 필터 조건으로만 사용 되는 컬럼이라면 (:c is null or col = :c) 또는 (c like :c || '%') 어떤 방식을 사용 해도 무방하다.