꿈꾸는 개발자, DBA 커뮤니티 구루비

h1.10. DYNAMIC SQL 사용 기준
h2.(1) Dynamic SQL 사용에 관한 기본 원칙

1. STATICS SQL 을 지원하는 개발환경이라면 Static SQL로 작성하는 것을 원칙으로 한다.
Static SQL 은 Precompile 을 과정을 거치므로 런타임 시 안정적인 프로그램 Build 가
가능하다는 장점이 있다. 그리고 Dynamic SQL 을 사용하면 애플리케이션 커서 캐싱 기능이
작동하지 않는 경우가 있는데, 이 기능이 필요한 상황(예를 들어, 루프 내에서 반복 수행되는
쿼리)에서 Dynamic SQL 을 사용하면 성능이 나빠지기 때문이다.

2. 아래의 경우에는 Dynamic SQL 을 사용해도 무방하다.
- 2.1. PreComplie 과정에서 컴파일 에러가 나는 구문을 사용할 때.
  예를 들어, Pro*C 에서 스칼라 서브쿼리, 분석함수, ANSI 조인 등
- 2.2. 상황과 조건에 따라 생성될 수 있는 SQL 최대 개수가 많아 Statics SQL로 일일이 나눠서
  작성하려면 개발 생산성이 저하되고 유지보수 비용이 커질 때

3. 2번 경우에 해당하는 Dynamic SQL 을 사용하더라도 조건절에는 바인드 변수를 사용하는 것을 원칙으로 한다.
특히 사용빈도가 높고 조건절 컬럼의 값 종류가 매우 많을 때(에를 들어, 계좌번호, 상품번호, 회원번호 등)는 반드시 준수한다.
3번 바인드 변수 사용원칙을 준수하되 아래 경우는 예외적으로 인정한다.

4.
- 4.1 배치 프로그램이나 DW, OLAP 등 정보게 시스템에서 사용되는 Long Running 쿼리.
  이들 쿼리는 파싱 소요시간이 쿼리 총 소효시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아
  하드 파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적음
- 4.2 OLTP 성 애플리케이션이라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를
  유발할 가능성이 없을 때. 예외적으로 인정하는 것이므로 단순히 바인드 변수 정의하는게 귀찮다고 그렇게 해서는 안됨
- 4.3 조건절 컬럼의 값 종류(Distcint value) 가 소수일 때. 특히 값 분포가 균일하지 않아 옵티마이져가 컬럼 히스토그램
  정보를 활용하도록 유도하고자 할 때.
  예) 증권시장구분코드 = { '유가','코스닥','주식파생','상품파생'}
  Static(=Embedded) SQL 을 지원하지 않는 개발 환경이라면 모든 SQL이 Dynamic SQL 이지만
  런타임 시 SQL 이 동적으로 바뀌도록 개발하는 것만큼은 삼가야 한다. 그런 환경에서 Static 과 Dynamic SQL을
  편의상 아래와 같이 재정의 하고, 위에서 제시한 기본 원칙을 동일하게 적용할 것을 권고한다.

h6.* Static SQL : SQL Repository 에 완성된 현태로 저장된 SQL
h6.* Dynamic SQL : SQL Repository 에 불완전한 형태로 저장된 후 런타임 시 상황과 조건에 따라 동적으로 생성되도록 작성된 SQL

h2.(2) 기본 원칙이 잘 지켜지지 않는 첫 번째 이유, 선택적 검색 조건
검색 조건이 다양해 사용자 선택에 따라 조건절이 동적으로 바뀌는 경우


Select 거래일자, 종목코드, 투자자유형?, 주문매체코드, 체결건수, 체결수량, 거래대금
From 일별종목거래
Where 거래일자 between :시작일자 and :종료일자
And    종목코드 = nvl(:종목코드, 종목코드)
And    투자자유형코드 = nvl(:투자자유형코드, 투자자유형코드)
And    주문매체구분코드 = nvl(:주문매체구분코드, 주문매체구분코드)

이렇게 코딩하면 사용자가 어떻게 값을 입력하더라도 단 한 개의 실행계획을 공유하면서 반복 재상용하게
되므로 라이브러리 캐시 효율 측면에서는 최상의 선택이다. 하지만 쿼리 수행 속도 측면에서는 어떤가?
인덱스를 이용한 액세스가 효과적인 상황에서 낭패를 보게 된다. 인덱스를 전혀 사용못하거나
(거래일자가 인덱스 선두 컬럼이 아닐 때) 상요하더라도 비효율적으로 사용하기 때문이다.

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

그렇다면 현실적인 대안은? 앞 절에서 이미 정리했듯이 Statics SQL 사용을 원칙으로 하되
사용자 입력 조건에 따라 발생될 수 있는 SQL 최대 개수가 너무 많을 때는 Dynamic SQL 사용을 하는 것이다.
개발 언어나 툴에서 Dynamic Method 를 제공하는 목적에 맞게, 부하를 최소화하는 수준에서 잘 활용하는 것이 최선이라고 생각한다.
다시 한번 강조하지만, 원칙은 Static SQL 로 작성하는 것이며, 방법이 없거나 SQL 이 너무 복잡할 때만
Dynamic SQL 을 꺼내 들려고 노력해야 한다.

h2.(4) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교
h4.A. OR 조건을 사용하는 경우


Select * from  일별종목거래
Whre (: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 으로 처리되므로 인덱스 활용이 필요할 때는 이 방식을 사용해선 안된다.

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


SELECT * FROM  일별종목거래
WHRE isu_cd like :isu_cd ||'%'
Execution Plan
-------------------------------------------------------------------
0     SELECT STATEMENT Optimizer=ALL_ROWS
1  0    TABLE ACCES ( BY LOCAL INDEX ROWID ) OF '일별종목거래' (TABLE)
2  1    INDEX (RANGE SCAN) OF '일별종목거래_PK' ( INDEX ( UNIQUE))

인덱스 사용이 가능하지만 사용자가 :isu_cd 값을 입력하지 않았을 때 Table Full Scan이 유리한데도
인덱스를 사용하게 되므로 성능이 나빠질 수 있다.

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


SELECT * FROM 일별종목거래
WHERE isu_cd = nvl(;isud_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 ACCES ( BY LOCAL INDEX ROWID ) OF '일별종목거래' (TABLE)
6  5             INDEX (RANGE SCAN) OF '일별종목거래_PK' ( INDEX ( UNIQUE))

h4.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 ACCES ( BY LOCAL INDEX ROWID ) OF '일별종목거래' (TABLE)
6  5             INDEX (RANGE SCAN) OF '일별종목거래_PK' ( INDEX ( UNIQUE))

C와 D 방식은, 사용자의 :isu_cd 입력 여부에 따라 Full Table Scan 과 Index Scan 으로 실행계획이 자동 분기된다.
*단 nvl 또는 decode 함수를 사용할 때는 해당 칼럼이 not Null 컬럼이어야 하며, null 허용 컬럼일 때는
결과 집합이 달라지므로 주의해야 한다{*}.


SQL> select * from dual where NULL = NULL
      선택된 레코드가 없습니다.
SQL> select * from dual where NULL IS NULL
     DU
     --
     X
     1 개의 행이 선택되었습니다.

Nvl 또는 decode 를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만
분기가 일어난다는 사실도 기억할 필요가 있고, 복잡한 옵션 조건을 처리할 때 이 방식에만 의존하기 어려운 이유가 여기에 있다.

h4.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 ACCES ( BY LOCAL INDEX ROWID ) OF '일별종목거래' (TABLE)
6  5             INDEX (RANGE SCAN) OF '일별종목거래_PK' ( INDEX ( UNIQUE))

5가지 방식에 대한 선택 기준을 정리해 보면 아래와 같다.
- # NOT NULL 컬럼일 때는 NVL, DECODE 를 사용하는 것이 편하다( C, D)
- # null 값을 허용하고 인덱스 액세스 조건으로 의미있는 컬럼이라면 union all 을 사용해(E)
  명식적으로 분기해야 한다.(E)
- # 인덱스 액세스 조건으로 참여하지 않는 경우, 즉 인덱스 필터 또는 테이블 필터 조건으로
  사용되는 컬럼이라면(:c is null or col = :c) 또는 (c like :c||'%') 어떤 방식을 사용해도 무방하다.(A,B)

최초작성자 : ~darkturtle
최초작성일 : 2009년 12월 05일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*