메뉴 건너뛰기

bysql.net

11._Static_SQL_구현을_위한_기법들

2012.05.06 22:09

dasini 조회 수:3948

Dynamic SQL을 자주 사용하는 이유

.  조건절에 IN-List 항목이 가변적으로 변할때

 

 4_15.png


 

(1) IN-List 항목이 가변적이지만 최대 경우 수가 적은 경우

 

  >>  Static 방식

 

select * from LP회원 where  회원번호 in ('01')

select * from LP회원 where  회원번호 in ('01','02)
select * from LP회원 where  회원번호 in ('01','03')
select * from LP회원 where  회원번호 in ('01','02','03')
select * from LP회원 where  회원번호 in ('02')
select * from LP회원 where  회원번호 in ('02','03')
select * from LP회원 where  회원번호 in ('03')

 

 

   >>>  Static SQl로 구현

select * from LP 회원 where 회원번호 in ( :a , :b, : c)

 

  >>>  decode 문 사용시 ( 4개의 체크 박스에 각각 내부적으로 'all','01','02','03' 부여 되있다 가정)

 

select * from LP 회원

where 회원번호 in (decode (:a, 'all','01',':b')

                                   ,decode (:a, 'all','01',':c')

                                   ,decode (:a, 'all','01',':d')

                                    )

 

 

 

 

(2) IN-List 항목이 가변적이고 최대 경우 수가 아주 많은 경우

 

  4_16.png 

 

  . 가능한 경우 수가 너무 많아 Static SQL로 일일이 작성해 두는 것은 불가능해 보임

 

 

>>  칼럼과 변수 위치를 서로 바꿈

 

select * from 수시공시내역

where 공시일자 = : 일자

and :inlist like '%' || 분류코드 || '%'

 

 

 사용자가 선택한 분류코드를 'comma' 등 구분자로 연결해 아래 처럼 String 형 변수에 담아서 바인딩하고 실행함

: inlist := '01,03,08,14,17,24,33,46,48,53'

 

>>> 문자열을 처리하는 오라클 내부 알고리즘상 like 연산자보다 instr 함수를 사용하면 더 빠르므로 아래와 같이 권고

 

select * from 수시공시내역

where 공시일자 := 일자

and INSTR(:inlist, 분류코드) > 0

 

 

 

 

> index 구성에 따른 다른 사용의 예

 

1. IN-List 를 사용 할 때

select * from 수시공시내역

where 공시일자 = ;일자

and 분류코드 in (.....)

 

2. like 또는 instr 함수를 사용할 때

select * from 수시공시내역

where 공시일자 =: 일자

and INSTR(:inlist, 분류코드) > 0

 

 

.  인덱스 구성이 [분류코드 +공시일자] 일때

             1번이 유리

             2번 sql문은 인덱스를 사용하지 못하거나 Index Full scan 해야함

 

 

 . 인덱스 구성이 [공시일자 + 분류코드] 일때

             사용자가 선택한 항목의 개수가 소수일때는 1번이 유리,

             하지만 인덱스를 그만큼 여러번 탐침해야 하므로 2번이 유리할 수도 있음 (유, 불리는 인덱스 깊이와 데이터 분포에 따라 결정됨)

 

 

.  인덱스 구성이 [분류코드 +공시일자] 일때

 

<인덱스를 효율적으로 액세스 할수 있는 방법1>

 

select /*+ ordered use_nl(B)*/ b.*

from (select 분류코드

          from  수시공시분류

          where INSTR(:inlist, 분류코드) > 0 ) A

          , 수시공시내역 B

where B.분류코드= A.분류코드

 

   -> 수시공시분류 테이블은 100개 이내의 작은 테이블일 것이므로 Full Scan 으로 읽더라도 비효율은 없음

   -> 작은 테이블을 Full Scan으로 읽으면서 NL조인 방식으로 분류코드 값을 수시공시내역 테이블에 던져줌

 

 

<인덱스를 효율적으로 액세스 할수 있는 방법2>

 

select /*+ ordered use_nl(B)*/ b.*

from (select substr(:inlist, (rownum-1) *2+1,2)

          from  수시공시분류

          where rownum <= length(:inli ) A

          , 수시공시내역 B

where B.분류코드= A.분류코드

 

 

   ->  드라이빙 집합으로 수시공시분류 테이블을 사용

          이 SQL을 실행할 때는 사용자가 선택한 분류코드를 아래처럼 구분자 없이 연결해 String 형 변수에 담아서 바인딩함

 :inlist := '01030814172433464853'

 

인라인뷰 A만 먼저 실행시켜 보면, 아래 10개 레코드를 갖는 집합이 됨

01

03

08

14

17

24

33

46

48

53

코드값 길이가 3자리면 숫자 2를 모두 3으로 변환하면 됨 

 

 

 

<인덱스를 효율적으로 액세스 할수 있는 방법3>

 

 

select /*+ ordered use_nl(B)*/ b.*

from (select substr(:inlist, (level-1) *2+1,2)

          from dual

          connet by level <= length(:inlist)/2)A

          , 수시공시내역 B

where B.분류코드= A.분류코드 

 

  . 방법 2와 같은 방식, dual 테이블을 이용해 집합을 동적으로 생성

 

 

 

 

(3) 체크 조건이 가변적인 경우

 

 > 주식종목에 대한 회원사별 거래실적을 집계하는 쿼리

 

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)

from 일별거래실적 e

where 거래일자 =: trd_dd

and 시장구분 = '유가'

and exists(

      select 'x'

      from  종목

     where 종목코드 = e.종목코드

     and  코스피종목편입여부 = 'Y'

)

group by 회원번호

 

 

>>> Static SQL로 구현함

 

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)

from 일별거래실적 e

where 거래일자 =: trd_dd

and 시장구분 = '유가'

and exists(

      select 'x'

      from  종목

     where 종목코드 = e.종목코드

     and  코스피종목편입여부 = decode(:check_yn,'y','y',코스피종목편입여부)

)

group by 회원번호

 

 

 

 . 전종목을 대상으로 집계하고자 할때

    --> 성능이 떨어짐 [Query : 8515]

 

 . 사용자가 코스피 편입 종목만으로 집계하고자 할때 

  --> 통합하기 전과 블록 I/O가 같음,     2개 SQL로 분리하는 편이 낫음

 

  >>>     굳이 하나의 SQL로 통합하고 싶다면 아래와 같이 함

 

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)

from 일별거래실적 e

where 거래일자 =: trd_dd

and 시장구분 = '유가'

and exists(

      select 'x' from dual where :check_yn = 'N'

      union all

      select 'x'

      from  종목

     where 종목코드 = e.종목코드

     and  코스피종목편입여부 = 'Y'

     and  :check_yn = 'Y')

group by 회원번호

 

--> exists 서브 쿼리는 존재 여부만 체크하므로 그 안에 union all을 사용하면 조인에 성공하는첫 번째 레코드를 만나는

    순간 더는 진행하지 않고 true를 리턴

 

[전 종목을 대상으로 집계하고자 할 때]

--> 위 쿼리가 실행될 때 :check_yn 변수에 'N'값이 들어오면 , union all 아래쪽 종목 테이블과는 조인을 시도하지 않고

      곧바로 exists 서브쿼리를 빠져나옴

      10 g 부터 dual 테이블은 fast dual 방식으로 수행되기 때문에 i/o는 발생되지 않음

 

[사용자가 코스피 편입 종목만으로 집계하고자 할때]

-->  동일한 성능

 

 

 

(4) select-list가 동적으로 바뀌는 경우

 

4_17.png

 

 

 

 

 

 

 

 

 

 

/* 1: 평균 2: 합계 */

if ( pfmStrCmpTrim(INPUT->inData.gubun,"1",1) ==0){

   snprint(...," avg(계약수), avg(계약금액), avg(미결제약정금액) ");

} else {

   snprint(...," avg(계약수), avg(계약금액), avg(미결제약정금액) ");

}

 

 

>>> Static SQL로 변환

 

/*1:평균 2: 합계 */

 decode(:gubun, '1', avg(계약수), sum(계약수)),

 decode(:gubun, '1', avg(계약금액), sum(계약금액)),
 decode(:gubun, '1', avg(미결제약정금액), sum(미결젱약정금액)),

 

 >>> 사례2

 

/* 1: 평균 2: 누적 */

if ( pfmStrCmpTrim(INPUT->inData.gubun,"1",1) ==0){

   snprint(...," sum(계약수) 거래량, sum(거래대금) 거래대금 ");

} else {

   snprint(...," sum(누적거래량) 거래량, sum(누적거래대금) 거래대금 ");

}

 

 

  >>> Static SQL로 변환

 

/* 1: 개별 2: 누적 */

 decode(:gubun ,'1', sum(거래량), sum(누적거래량)) 거래량,

 decode(:gubun ,'1', sum(거래대금), sum(누적거래대금)) 거래대금

 

>>> 성능이 나빠지는 예 (decode 함수를 집계대상 건수만큼 반복 수행함)

 

 

/* 1: 개별 2: 누적 */

 sum(decode(:gubun ,'1',거래량, 누적거래량)) 거래량,

 sum(decode(:gubun ,'1',거래대금, 누적거래대금)) 거래대금

 

 

 

(5) 연산자가 바뀌는 경우

 비교연산자가 그때그때 달라지는 경우

 

 

 

>> 아래처럼 SQL 을 작성하고 바인딩하는 값을 바꾸면 됨

 

where 거래미형성률      between :min1 and :max1

and 일평균거래량          between :min2 and :max2
and 일평균거래대금      between :min3 and :max3
and 호가스프레드비율 between :min4 and :max4
and 가격연속성               between :min5 and :max5
and 시장심도                   between :min6 and :max6

and  거래체결률              between :min7 and :max7

 

 . 각 컬럼은 아래와 같은 도메인에 따라 표준화된 데이터 타입과 자리수 할당 받음

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

이 게시물을
목록
목록
쓰기
태그