10 Dynanic SQL 사용 기준

(1) Dynamic SQL 사용에 관한 기본 원칙

1. Static SQL을 지원하는 개발 환경에서는 Static SQL을 사용하는 것을 원칙으로 한다.

• Static SQL 사용시 PreCompile을 하여 런타임시에 안정성이 높아진다.
• Dynamic SQL사용시 어플리케이션 커서 캐싱 기능이 동작하지 않는 때가 있으나 Static SQL은 그렇지 않다.

2. 아래와 같은 경우에는 Dynamic SQL을 사용할 수도 있다.

• PreCompile과정에 에러가 나는 구문이 사용될 때 :스칼라서브쿼리， 분석함수， ANSI
• 상황과 조건이 복잡하여 Static SQL로 작성하기에 개발 및 관리가 어렵겠다고 판단될 때.

3. 2번 경우에 해당해서 Dynamic SQL를 사용하더라도 조건절에는 바인드 변수를 사용하는 것을 원칙으로 한다

4. 3번 바인드 변수 사용원칙을 준수하되 아래 경우는 예외적으로 인정한다.

• 배치 프로그램이나 DW. OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리.파싱 수행 시간이 총 쿼리 소요시간에 비해 얼마 되지 않을 때(라이브러리 캐시 부하를 유발할 가능성이 적음)).
• 사용 빈도가 매우 낮아 하드 파싱에 의한 부하가 시스템에 별로 영향을 주지 않는 쿼리
• 조건절 컬럼의 갑종류가 소수일때, 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도할때

(2) 기본 원칙이 잘 지켜지지 않는 첫 번째 이유， 선택적 검색 조건

• 검색 조건이 다양해 사용자 선택에 따라 조건절이 동적으로 바뀌는경우
• 대용량 테이블에 대 한 조회 요건 이 이렇게 복잡하다면 적어도 현업과의 협의를 통해 필수입력 항목을 수렴해서 프로그램에 반영해야 한다.
  또한 기간 조건(Between， <, >, <=, >= 등) 에 대해서는 입력 값 범위를 가능한 짧게 제한하려는 노력을 반드시 해야 한다.

• 거래일자, 종목코드, 투자자유형, 주문매체를 조회 조건으로 화면 구성, 단 거래일자는 필수 입력사항

select 거래일자, 종목코드, 투자자유형코드, 주문매체코드
     , 체결건수，체결수량, 거래대금
from 일별종목거 래
where 거래일자 between 시작일자 and 종료일자
%option

%option = " and 종목코드 = 'KR123456' and 투자자유형코드 = '1000' "

• NVL 사용

select 거래일자, 종목코드, 투자자유형코드, 주문매체코드
     , 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between 시작일자 and 종료일자
and 종목코드 = nvl (:종목코드, 종목묘드)
and 투자자유형묘드 = nvl(:투자자유형코드, 투자자유형코드)
and 주문매체구분코드 = nvl(:주문매체구분코드, 주문매체구분묘드)

• UNION ALL 사용
  라이브러리 캐시 효율과 1/0 효율을 모두 고려하면서 SQL을 개발하려면， 남아 있는 방법은 한 가지뿐이다.
  종목코드 투자자유형코드 주문매체구분묘드 입력여부에 따라 SQL을 모두 분리해서 개발하는 것이다.
  그럴 경우 이 3가지 선택적 입력 조건을 처리하는 데에만 모두 8개의 SQL을 따로 작성해야 한다.

1. 거 래 일자 between
2. 거래일자 between, 종목코드 =
3. 거래일자 between, 종목코드 투자자유형코드 =
4. 거래일자 between, 종목코드 투자자유형코드 주문매체구분코드 =
5. 거래일자 between, 종목코드 주문매체구분코드 =
6. 거래일자 between, 투자자유형코드 =
7. 거래일자 between, 투자자유형코드 주문매체구분코드 =
8, 거래일자 between, 주문매체구분묘드 =

<인덱스 구성>
index 1 : 종목코드+거래일자
index 2 : 투자자유형코드+거래일자+주문매체구분코드
index 3 : 거래일자+주문매체구분코드

select 거래일자, 투자자유형코드, 회원번호
     , 체결건수, 체결수량, 거래대금
from 일별종목거래
where :종목코드 is not null
and 거래일자 between :시작 and :종료
and 종목코드=:종목코드
and 투자자유형코드=nvl(:투자자유형, 투자자유형코드)
and 주문매체구분코드=nvl(:주문매체, 주문매체구분코드)
union all
select 거래일자, 투자자유형코드, 회원번호
     , 체결건수, 체결수량, 거래대금
from 일별종목거래
where :종목코드 is null and :투자자유형 is not null
and 거래일자 between :시작 and :종료
and 투자자유형코드=nvl(:투자자유형, 투자자유형코드)
and 주문매체구분코드=nvl(:주문매체, 주문매체구분코드)
union all
select 거래일자, 투자자유형코드, 회원번호
     , 체결건수, 체결수량, 거래대금
from 일별종목거래
where :종목코드 is null and :투자자유형 is null
and 거래일자 between :시작 and :종료
and 주문매체구분코드=nvl(:주문매체, 주문매체구분코드)

(3) 선택적 검색 조건에 대한 현실적인 대안

• Static SQL 사용을 원칙으로 함
• 사용자 입력 조건에 따라 생성될 수 있는 SQL 최대 개수가 너무 많을 때는 Dynamic SQL 사용을 허용(실질적 하드 파싱 부하는 없다)
• 바인드 변수 사용 원칙만큼은 준수하도록

• PL/SQL에 서 조건절을 동적으로 구성하되 바인드 변수를 사용하도록 코딩한 예제

SQLStmt := 'SELECT 거래일자, 종목코드 투자자유형코드, '
        || '주문매체코드, 체결건수, 체결수량, 거래대금 '
        || FROM 일별종목거래 '
        ||WHERE 거래 일자 BETWEEN :1 AND :2'
If종목코드 IS NULL Then
SQLStrnt : = SQLStrnt || 'AND : 3 IS NULL';
Else
SQLStrnt : = SQLStrnt || 'AND 종목코드 :3';
End If;
If 투자자유형 IS NULL Then
SQLStrnt : = SQLStrnt || 'AND : 4 IS NULL';
Else
SQLStrnt : = SQLStrnt || 'AND 투자자유형코드 :4' ;
End If;
EXECUTE IMMEDIATE SQLStrnt
INIO :A, :B, :C, :D, :E, : F, :G
USING :시작일자, :종료일자, :종목코드, :투자자유형코드;

(3) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교

• A OR 조건을 사용하는 경우

select * from 일별종목거래
where (:isu_cd is null or isu_cd :isu_cd)

Execution Plan
-------------------------------------------------------------
o SELECT STATEMENT Optirnizer=ALL rows
1 0 TABLE ACCES (FULL) OF '일별종목거래' (TABLE)

• B LIKE 연산지를사용하는 경우

select * frαn 일별종목거 래
where isu_cd like :isu_cd || '%'
Execution Plan
-------------------------------------------------------------
o SELECT STATEMENT Optirnizer=ALL rows
1 0 TABLE ACCESS (BY LOCAL INDEX ROWWID) OF ' 일별종목거래' (TABLE)
2 1 INDEX (RANGE SCAN) OF '일별종목거래 PK' (INDEX (UNIQUE))

• C NVL 함수를 시용하는 경우

select * from 일별종목거래
where isu_cd = nvl (:isu_cd,isu_cd)
Execution Plan
-------------------------------------------------------------
o SELECT STATEMENT Optirnizer=ALL rows
1 0 CONCATENATION
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4 1 FILTER
5 4 TABLE ACCESS (BY IDCAL 1NDEX ROWID) OF ' 일별종목거 래 ' (TABLE)
6 5 INDEX (RANGE SCAN) OF '일별종목거래 PK' (INDEX (UNIQUE))

• D DECODE 함수를 사용하는 경우

select * from 일별종목거래
where isu_cd =decode (:isu_cd, null, isu_cd, :isu_cd)
Execut ion Plan
-------------------------------------------------------------
o SELECT STATEMENT Optirnizer=ALL ROWS
1 0 CONCATENATION
2 1 FILTER
3 2 TABLE ACCESS (EULL) OF '일별종목거래 ' (TABLE)
4 1 FILTER
5 4 TABLE ACCESS (BY IDCAL INDEX ROWID) OF ' 일 별종목거 래 ' (TABLE)
6 5 INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))

• E UNION ALL을 사용하는 경우

select * frαn 일별종목거래
where : isu_cd is null
union all
select * from 일별종목거래
where :isu_cd is not null
and isu_cd = :isu_cd
Execution Plan
-------------------------------------------------------------
o SELECT STATEMENT Optimizer=ALL ROWS
1 0 UNICN-ALL
2 1  FILTER
3 2   TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4 1  FILTER
5 4   TABLE ACCESS (BY lDCAL INDEX ROWID) OF ' 일별종목거 래 ' (TABLE)
6 5    INDEX (RANGE SCAN) OF '일별종목거래 PK' (INDEX (UNIQUE))