10._Dynamic_SQL_사용_기준

조회 수 3332 추천 수 0 2013.08.21 12:33:44
남송휘 *.73.93.2

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

  1. Static SQL 을 지원하는 개발환경이라면 Static SQL로 작성하는것이 원칙
  2. Dynamic SQL 을 사용해도 무방한 경우
    • PreCompile 과정에서 컴파일 에러가 나는 구문 ( Pro*C 에서 스칼라서브쿼리,분석함수등)
    • 상황과 조건에 따라 생성되는 SQL최대 개수가 많아 유지보수 비용이 커질때
  3. 조건절에 바인드 변수 사용 원칙, 사용빈도 가 높고, 조건절 컬럼의 값 종류가 많을때
  4. 3번조건의 예외
    • 배치 프로그램이나 DW,OLAP 등 정보계 시스템에사용되는 Long Running 쿼리
      (파싱시간이 쿼리 총소요시간에 차지하는 비중이 매우 낮을때)
    • OLTP 성 이라도 사용빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하 유발 가능성이 없을때
    • 조건절 컬럼의 값 종류 (distinct value)가 소수일때, 값 분포가 균일하지 않아
      옵티마이져가 컬럼 히스토그램 정보를 활용하도록 유도 할때
  • Static SQL을 지원하지 않는 개발환경에서의 static 과 dynamic sql 재정의 (편의상)
    • Static SQL
      • SQL Repository 에 완성된 형태로 저장된 SQL
    • Dynamic SQL
      • SQL Repository 에 불완전한 형태로 저장후 런타임시 상황과 조건에 따라
        동적으로 생성되도록 작성한 SQL


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


  • 대용량 테이블을 검색하는 애플리케이션에서 기간, 필수 검색조건없이 조회가능하도록 만든경우 성능 이슈 발생
    -> 설계 단계에서 성능을 고려한 업무 요건 도출 필요
  • 기간 검색만 필수이고 나머지는 선택일 경우

ex)

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

%option = “ and 종목코드 = ‘KR123456’ and 투자자유형코드 = ‘1000’ “

개선

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





  • 사용자가 어떻게 값을 입력하더라도 한개의 실행 계획 공유
    • so) 라이브러리 캐시 효율 최상
    • but) 인덱스 사용에 문제 발생 (사용못하거나 사용하더라도 비효율적일수 있음)



  • 라이브러리 캐시 효율과 i/o 효율 모두 고려한다면 각 조건별 입력에 따라 sql을 모두 분리
  • 3가지 선택적 입력이라면

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


  • but ) 현실적으로 어려움
  • so) 변별력이 좋은 컬럼 중심으로 2~3개의 sql로 분기
    • if 문을 이용해 분기하거나 union all을 사용

<인덱스 구성>


index01: 종목코드 + 거래일자
index02: 투자자유형코드 + 거래일자 + 주문매체구분코드
index03: 거래일자 + 주문매체구분코드


ddd.gif



  • 이방법 역시 매번  인덱스 구성전략을 고민하면서 개발하기는 힘들므로
  • union all 분기는 일반적인 sql 작성보다 튜닝차원에서 접근이 필요


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

  • static sql 사용을 원칙으로 하되 입력 조건에 따라 sql이 너무 많이 생성될때는 dynamic sql의 사용을 허가
    • sql 개수 많더라도 일부만 주로 사용되므로 실질적인 하드 파싱 부하는 거의 없음
    • 라이브러리 캐시 효율화의 핵심인 바인드 변수 사용 원칙은 준수
  • pl/sql 문에서 조건절을 동적으로 구성하되 바인드 변수를 사용하도록 코딩한 예 (java,pro*c활용)


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 :시작일자, 종료일자, :종목코드, :투자자유형코드;



  • Visual Basic 스타일로 변환 (delphi등 활용)



SQLStmt =”select 거래일자, 종목코드, 투자자유형코드 “&
    “    , 주문매체코드, 체결건수, 체결수량, 거래대금 “ &_
    “from 일별종목거래 where 거래일자 between ? and ? “

comm.parameters.append _
    comm.createparameter (“시작일자”,advarchar, adparaminput,8,시작일자.text)

comm.parameters.append _
    comm.createparameter (“종료일자”,advarchar, adparaminput,8,종료일자.text)

if 종목.text <>”” then
    sqlstmt=sqlstmt & “and 종목코드 =?”
    comm.parameters.append _
    comm.createparameter (“종목코드작일자”,advarchar, adparaminput,8,종목.text)
end if

if 투자자유형.목.text <>”” then
    sqlstmt=sqlstmt & “and 투자자유형코드 =?”
    comm.parameters.append _
    comm.createparameter (“투자자유형코드”,advarchar, adparaminput,8,투자자유형.text)
end if

comm.commandtext =sqlstmt
set rs.comm.execute




  • sql을 다이나믹하게 구성하면 sql repository에서 sql을 수집해
    테이블 액세스 유형을 분석하면서 인덱스를 설계할때 다소 불편해짐, 힌트를 사용해 튜닝하기도 곤란




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

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


a.png

  • 항상 table full scan 으로 처리 되므로 인덱스 활용시는 사용 불가



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


b.png

  • 인덱스 사용가능
  • but) :isu_cd 값을 입력하지 않을때 table full scan이 유리한데도 인덱스 사용


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


c.png


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


d.png

  • c,d 방식은 사용자의 :isu_cd 입력 여부에 따라 full table scan과 index scan으로 실행계획이 자동 분기
  • 해당 컬럼이 not null 컬럼이여야함, null  허용일경우 결과 집합이 달라짐

SQL> SELECT * FROM dual;

D
-
X

SQL> SELECT * FROM dual WHERE NULL is null;

D
-
X

SQL> SELECT * FROM dual WHERE NULL=null;

no rows selected

SQL>


    • SELECT * FROM dual; -> x 리턴
    • SELECT * FROM dual WHERE NULLis null;  -> x 리턴
    • SELECT * FROM dual WHERE NULL=null;  -> false 리턴, 아무것도 없음
  • nvl,decode 를 여러 컬럼에 사용했을경우 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어남
    -> 복잡한 옵션 처리시는 어려움


E. Union all 을 사용하는 경우


e.png



  • 정리
  1. not null 컬럼일 경우 nvl, decode 를 사용 하는것이 편함
  2. null 값을 허용하고 인덱스 조건으로 의미있는 컬럼이라면 union all을 사용해 명시적으로 분기
  3. 인덱스 액세스 조건 이 아닌경우 (인덱스 필터, 테이블 필터 조건으로 사용되는 컬럼)
    :c is null or col =:c 또는 c like :c ||’%’ 어떤 방식을 사용해도 무방




  • 오라클 고도화 원리와 해법 1 (bysql.net 2012년 1차 스터디)
  • 작성자:  남송휘
  • 최초작성일: 2012년 5월 6 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^