11 Static SQL 구현을 위한 기법들

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

!Static SQL 구현01.png!

  • LP회원을 선택하는 그림 4-15 팝업 창에서 사용자가 선택한 LP회원 목록을 선택하고 자 할 때, Static 방식으로 SQL을 작성하려면 아래 7개 SQL을 미리 작성해야함.

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')

선택 가능한 회원수가 4개로 늘어나면 15개, 5개변 31개의 SQL이 필요함. Dynamic SQL을 사용하는 손쉬운 방법을 선택하게 됨.

  • Static SQL로 구현

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

  • 4개의 체크 박스에 각각 내부적으로 'all' , '01' '02' , ' 03' 값이 부여돼 있다고 가정하고 decode문을 사용

select * from LP회원
where 회원번호 in ( decode(:a, 'all', '01', :b)
                  , decode(:a, 'all', '02', :c)
                  , decode(:a, 'all', '03', :d) )

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

!Static SQL 구현02.png!

  • 가능한 경우 수가 너무 많아 Static SQL로 작성하기가 어려움, 바인드 변수를 사용하는 것도 쉽지 않다.
  • SQL 조건절에는 대개 좌변에 컬럼을 두고 우변에는 그것과 비교할 상수 또는 변수를 위치시킨다 하지만 여기서는 생각을 바꿔 컬럼과 변수 위치를 서로 바꿔보자.

select * from 수시공시내역
where 공시일자 = :일자
and :inlist like '%' || 분류코드 || '%'

  • 분류코드를 ,로 구분하여 String 변수에 담아 바인딩, :inlist := '01,03,08,14,17,24,33,46,48,53'

select * from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist, 분류코드) > 0

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

  • (1) IN-List를 사용할 때

select * from 수시공시내역
where 공시일자 = :일자
and 분류코드 in ( ... )

  • (2) like 또는 instr 함수를 사용할 때

select * from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist, 분류코드) > 0

인텍스 구성이 분류코드 + 공시일자일 때는 당연히 1번이 유리, 2번은 인텍스를 사용하지 못하거나 Index Full Scan
하지만 인텍스 구성이 공시일자 + 분류코드일 때는 항목 개수가 소수일 경우 1번이 유리, 다수일 경우 2번이 유리 할 수도 있다.(인덱스 깊이와 데이터 분포에 따라)

  • 소수 항목만으로 조회하거나 인텍스 구성이 분류코드 + 공시일자일 때는 인텍스를 좀더 효율적으로 액세스할 수 있는 방법
  • <방법1> 작은 테이블 Full Scan으로 NL 조인 방식으로 분류코드 값을 해당 테이블 조회

select /*+ ordered use_nl(B) */ B.*
from ( select 분류코드
          from 수시공시분류
          where INSTR(:inlist, 분류코드) > 0 ) A
	     , 수시공시내역 B
where B.분류코드 = A.분류코드  

  • <방법2> 분류코드를 String형 변수에 담아 바인딩
    :inlist := '01030814172433464853'

se1ect /*+ ordered use n1(B) */ B.*
frαn (se1ect substr( : in1ist, (rownum-1) * 2+1, 2)
from 수시공시분류
where rownum <= 1ength ( :inlist) / 2) A
' 수시공시내역 B
where B . 분류코드 A . 분류코드  

  • <방법3> 〈방법 2>와 같은 방식이라고 할 수 있다. 다만, dual 테이블을 이용해 집합을 동적으로 생성

select /*+ ordered use nl (B) */ B.*
from (select substr (: inlist, (level-l )*2+1, 2)
from dual
connect by level <= length( :inlist) / 2) A
, 수시공시내역 B
where B . 분류코드 A. 분류코드

  • (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 회원번호

코스피 종목 편입 여부와 상관없이 전 종목을 집계시에는 서브쿼리 수행할 필요가 없다. SQL을 무리하게 통합함으로써 항상 서브쿼리 수행이라는 비효율적 결과를 안게 됨.
전 종목을 대상으로 집계하고자 할 때 블록 I/O가 8518 발생
시용자가 코스피 편입 종목만으로 집계하고자 할 때] 블록 I/O가 8208 발생


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를 리턴한다.
전 종목을 대상으로 집계하고자 할 때
union all 아래쪽 종목 태이블과는 조인을 시도하지 않고 곧바로 Exists 서브쿼리를 빠져 나온다. 1Og부터 DUAL 테이블은 FASTDUAL 방식으로 수행되기 때문에 블록 1/0는 발생하지 않는다.
사용지가 표스피 편입 종목만으로 집계하고자 할 때
동일한 성능

  • (4) select-list가 동적으로 바뀌는 경우
    사용자 선택에 따라 화변에 출력해야 할 항목이 달라지는 경우

!Static SQL 구현03.png!


/* 1 : 평균 2: 합계 */
if( pfmStrCmpTrim(INPUT->inData.gubun, "1", 1) == 0){
	snprintf(..., " avg(계약수), avg(계약금액), avg(미결제약정금액) ");
} else {
	snprintf(..., " sum(계약수), sum(계약금액), sum(미결제약정금액) ");
}


/* 1 : 평균 2: 합계 */
decode(:gubun, '1', avg(계약수), sum(계약수)),
decode(:gubun, '1', avg(계약금액), sum(계약금액)),
decode(:gubun, '1', avg(미결재약정금액), sum(미결재약정금액)),

Decode, Case 이용

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

!Static SQL 구현04.png!


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

거래량 도메인은 9자리 숫자형이고 정수 값만 허용하므로 입력 가능한 최소값은 0 최대값은 999.999.999이다.
따라서 시용자가 1000주를 입력하면 사용자가 선택한 비교 연산자에 따라 아래와 같이 Between 시작값과 종료값을 바인딩

구분minmax이하01000
미만0999
이상1000999999999
초과1001999999999

연산자에 따라 바인딩 값 변경