▣ Dynamic SQL 사용에 관한 기본 원칙

(1) Static SQL을 지원하는 개발 환경이면 Static SQL로 작성하는 것을 원칙으로 한다.

    => precompile하므로 런타임시 안정적인 프로그램 build가 가능하다.

(2) Dynamic SQL 사용해도 무방한 경우

① Precompile 과정에서 컴파일 오류가나는 문장 - 스칼라서브쿼리, 분석함수, ANSI 조인

② SQL 개수가 많아 Static SQL로 일일이 작성하면 개발생산성이 떨어질 경우

(3) (2)번의 Dynamic SQL 문의 경우 조건절에서는 바인드 변수 사용을 원칙으로 한다.

     특히 사용 빈도가 높고 조건절 컬럼의 값 종류가 많을 때 반드시 준수한다.

(4) (3)번의 원칙에서 아래 경우는 예외적으로 인정한다.

① DW, OLTP와 같이 Long Running 질의는 하드파싱 시간은 큰 문제가 안된다.

② OLTP의 경우 사용빈도가 낮은 질의는 하드파싱이 문제가 안됨

③ 조건절의 값 종류가 소수일때, 특히 값 분포가 일정하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도할 때, 예) 증권시장코드={유가, 코스닥,주식파생,상품파생}

- Static vs Dynamic

Static SQL -> SQL Repository에 완성된 형태로 저장된 SQL

Dynamic SQL -> SQL Repository에 불완전한 형태로 저장한 후 런타임때 동적으로 생성

▣ 기본원칙이 지켜지지 않는 경우, 선택적 검색 조건

- Dynamic SQL로 조건절을 동적으로 구성하게되는 이유는?

(1) 조건절이 다양해서 사용자의 선택에 따라 조건절이 동적으로 바뀌는 경우

<p317 그림> 웹입력 화면

조건절을 줄이고 필수입력 항목을 만들고, 기간 조건의 범위를 줄인다.

- 업무상 조회 조건이 다양한 경우(거래일자, 종목코드, 투자자유형, 주문매체)

<p318 그림> : 거래일자 필수 나머지는 선택이다.

(SQL 문 작성 예1) 라이브러리 캐시를 비효율적으로 사용한다.(하드파싱 많이...)

select 거래일자, 종목코드, 투자자유형코드, 주문매체코드, ...

from 일별종목거래

where 거래일자 between :시작 and :종료

%option

(%option = " and 종목코드= ... and 투자유형코드= ...“)

(SQL 문 작성 예2) 라이브러리 캐시는 효율적이지만 인덱스를 비효율적으로 사용하게 된다.(하드파싱 1번..

select 거래일자, 종목코드, 투자자유형코드, 주문매체코드, ...

from 일별종목거래

where 거래일자 between :시작 and :종료

and 종목코드= nvl(:종목코드, 종목코드)

and 투자유형코드= nvl(:투자자유형코드, 투자자유형코드)

...

(SQL 문 작성 예3) union all 사용하여 최적화

- 종목코드, 투자자유형코드, 주문매체코드에 따라 SQL 문 8가지 따로 작성

- 인덱스 구성은 변별력이 있는 컬럼으로 생성

index 1 : 종목코드+거래일자

index 2 : 투자자유형코드+거래일자+주문매체구분코드

index 3 : 거래일자+주문매체구분코드

- SQL 문 작성

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(:주문매체, 주문매체구분코드)

▣ 선택적 검색 조건에 대한 현실적인 대안

- Static SQL 사용을 원칙으로 하되 입력조건에 따라 SQL 문이 너무 많을 때는 Dynamic SQL을 허용한다.

  조건절 SQL이 많아도 그 중 일부만 사용되므로 실질적인 하드 파싱 부하는 없다.

  다만, 라이브러리 캐시 효율화의 중심인 바인드 변수 사용 원칙은 준수한다.

- 예제 : PL/SQL에서 조건절을 동적으로 구성하고 바인드 변수 사용한 예제

- Dynamic SQL 문은 인덱스를 구성할 때 불편하다. 힌트를 사용하기도 어렵다.

  => 완성된 형태의 SQL들은 SQL Repository에 저장된 것을 참조하고, 그렇지 않은 것들은 수행된 최종 SQL들을 수집하여

      자주 나타나는 액세스 유형을 기준으로 인덱스를 설계한다.

  => 위 예제에서 Dynamic SQL로 작성했을 때 오라클 서버를 통해서 수집되는 최종 SQL 문들

(1) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금

from 일별종목거래

where 거래일자 between ? and ?

(2) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금

from 일별종목거래

where 거래일자 between ? and ?

and 종목코드=?

(3) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금

from 일별종목거래

where 거래일자 between ? and ?

and 투자유형코드=?

(4) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금

from 일별종목거래

where 거래일자 between ? and ?

and 종목코드=? and 투자유형코드=?

- 힌트를 사용하려면 Static SQL을 사용해야한다.

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

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

- Table Full Scan으로 처리하므로 인덱스 활용이 필요할 때는 이 방식은 안된다.(null 비교하면 full scan?)

B. LIKE 연산자를 사용하는 경우

- 인덱스를 사용하지만 사용자가 :isu_cd 값을 입력하지 않았을 때 Table Full Scan이 유리한데도

   인덱스를 사용하게되므로 성능이 나빠질 수 있다.

C. NVL 함수를 사용하는 경우

D. DECODE 함수를 사용하는 경우(DECODE(비교값, case1, 결과1, case2, 결과2, ..., 기본값)

- C,D는 사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 자동 분기된다.

- nvl 또는 decode 함수를 사용할 때, 해당컬럼이 not null이어야하며 null을 허용할 때는 결과가 달라진다.

  (null=null 비교는 DBMS에 따라 다르다)

E. union all을 사용하는 경우

- 둘중 하나를 선택 실행한다.

- (정리)

① not null 컬럼일 경우 nvl, decode를 사용하는 것이 편하다(C,D)

② null을 허용하고 인덱스액세스로 의미있는 컬럼이면 union all을 사용해 명시적으로 분기한다.(E)

③ 인덱스 액세스 조건으로 참여하지 않는 경우(인덱스 필터 또는 테이블 필터 조건으로 사용), (:c is null or col=:c), (c like :c || '%'), 어떤 방식을 사용해도 무관하다(A, B)