Dynamic SQL을 Static SQL로 바꾸는 기법

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

(1) Dynamic SQL을 사용하는 방법을 선택하는 배경

• LP회원을 선택하는 팝업 창에서 사용자가 선택한 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이 필요하게 된다.
• SQL을 미리 작성해 두고 경우에 따라 그 중 하나를 선택하도록 프로그래밍 하는 게 번거로워 Dynamic SQL을 사용하는 방법을 선택하게 되는 것이다.

[Static SQL로 구현 예시1]

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

• 이 때 null 값을 입력하면 자동으로 결과집합에서 제외된다.

[Static SQL로 구현 예시2] decode문 사용

• 4개의 체크박스에 각각 내부적으로 'all', '01', '02', '03' 값이 부여돼 있다고 가정한다.

select * from LP회원
where 회원번호 in
	( decode(:a, 'all', '01', :b)
    	-- :a가 'all'이면 무조건 '01'포함 아니면 그냥 :b의 값 ('01' 또는 NULL)
      , decode(:a, 'all', '02', :c)
      	-- :a가 'all'이면 무조건 '01'포함 아니면 그냥 :c의 값 ('02' 또는 NULL)
      , decode(:a, 'all', '03', :d) )
      	-- :a가 'all'이면 무조건 '01'포함 아니면 그냥 :d의 값 ('03' 또는 NULL)

✅ oracle DECODE 문법

• DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3..........)
• 아래 로직과 같다.

  if (컬럼 == 조건1) {
    return "남자";
  } else if (컬럼 == 조건2) {
    return "여자";
  } else {
    return "기타";
  }```

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

1. IN-List 항목이 가변적이고 최대 개수 고정적인 경우

• 가능한 경우 수가 너무 많아 Static SQL로 일일이 작성해 두는 것은 불가능하다.
• 바인드 변수를 사용하는 것도 쉬운 일이 아니다. 실행 시 바인드 변수에 값을 입력하는 코딩을 그만큼 많이 해야 하기 때문이다.

해결 예시 (수시공시내역 테이블)

[해결1] 구분자로 연결해 String형 변수에 담아서 바인딩 후 실행

• 사용자가 선택한 분류코드를 ',' 등 구분자로 연결해 아래처럼 String형 변수에 담아서 바인딩하고 실행한다.
  👉 :inlist := '01,03,08,14,17,24,33,46,48,53'

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

• 참고로, 문자열을 처리하는 오라클 내부 알고리즘상 like 연산자보다 instr 함수를 사용하면 더 빠르므로 이와 같이 작성할 것을 권고한다.
• 컬럼을 가공한 형태이므로 분류코드를 인덱스 액세스 조건으로 사용 못해 성능상 문제가 될 수 있다.

[해결2]

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

• 컬럼을 가공한 형태이므로 분류코드를 인덱스 액세스 조건으로 사용 못해 성능상 문제가 될 수 있다.

[해결3] 인덱스를 사용 > IN-List를 사용할 때

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

• 인덱스 구성이 [분류코드 + 공시일자]일 때 유리하다.
• 인덱스 구성이 [공시일자 + 분류코드]일 때 사용자가 선택한 항목 개수가 소수일 때 유리하지만 다수일 때는 인덱스를 그만큼 여러 번 탐침해야 하는 단점이 있다.

[해결4] 인덱스를 사용 > like 또는 instr 함수를 사용할 때

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

• 인덱스 구성이 [분류코드 + 공시일자]일 때 이 인덱스를 사용하지 못하거나 Index Full Scan을 한다.
• 하지만 인덱스 구성이 [공시일자 + 분류코드]일 때 인덱스 깊이(루트에서 리프블록에 도달하기까지 거치는 블록 수)와 데이터 분포에 따라 유리할 수도 있다.

[해결5] 인덱스 구성이 [분류코드 + 공시일자]일 때는 인덱스를 좀더 효율적으로 액세스할 수 있는 방법을 강구할 수 있다.

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

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

• 수시공시분류 테이블은 100개 이내의 작은 테이블일 것이므로 Full Scan으로 읽더라도 비효율이 없다.
• 작은 테이블을 Full Scan으로 읽으면서 NL 조인 방식으로 분류코드 값을 수시공시내역 테이블에 던지면 수시공시내역 테이블에 있는 인덱스를 정상적으로 이용하면서 원하는 결과집합을 빠르게 얻을 수 있다.

[해결6] 해결 5와 비슷하다. > 수시공시분류 테이블을 임시로 만든다.

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

[3] 체크 조건 적용이 가변적인 경우

[예시1] 주식종목에 대한 회원사(=증권사)별 거래실적을 집계

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
from 일별거래실적 e
where 거래일자 = :trd_dd and 시장구분 = '유가'
and exists ( select 'x' from 종목 where 종목코드 = e.종목코드 and 코스피종목편입여부 = 'Y' )
group by 회원번호

• Exists 서브쿼리는 코스피에 편입된 종목만을 대상으로 거래실적을 집계하고자 할 때만 동적으로 추가한다.
• 라이브러리 캐시 최적화와는 그다지 상관이 없다.나올 수 있는 경우의 수가 두 개뿐이기 때문이다.
  • 'X' 또는 'Y'

[예시2] 주식종목에 대한 회원사(=증권사)별 거래실적을 집계2

• 사용자가 코스피 종목 편입 여부와 상관없이 전 종목을 대상으로 집계하려고 할 때는 서브쿼리를 수행할 필요가 없다.
• 무리하게 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 회원번호

• 따라서 2개의 SQL로 분리하는 게 더 낫다.

[예시3] 주식종목에 대한 회원사(=증권사)별 거래실적을 집계3

• 2개의 SQL로 분리하여 I/O 효율과 라이브러리 캐시 효율을 모두 달성 가능

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

[4] select-list가 동적으로 바뀌는 경우

사용자 선택에 따라 화면에 출력해야 할 항목이 달라지는 경우

[잘못된 예시]

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

[해결] Static SQL로 바꾸기 > decode 함수 또는 case 구문을 활용

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

[5] 연산자가 바뀌는 경우

• 조건절에 사용되는 입력항목이나 출력항목이 바뀌는 게 아니라 비교 연산자가 달라지는 경우
• 미만, 이하, 이상, 초과, 중 사용자가 선택하는 항목이 무엇이냐에 따라 <, <=, >, >= 4가지 중 하나의 연산자로 바꿔야 하므로 Dynamic SQL이 불가피하다.

[해결1] 아래처럼 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

각 컬럼이 메인에 따라 표준화된 데이터 타입과 자릿수를 할당받는 다.

도메인	데이터 타입
거래량	NUMBER(9)
거래대금	NUMBER(15)
가격연속성	NUMBER(5, 2)

[일평균거래대금 예시]

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

구분	between 시작값(:min3)	between 종료값(:max3)
이하	0	1000
미만	0	999
이상	1000	999999999
초과	1001	999999999

[가격연속성 예시]

• 도메인은 소수점 이하 2자리를 갖는 총 5자리 숫자형이므로 입력 가능한 최소값은 0.00, 최대값은 999,99이다.
• 따라서 사용자가 50%를 입력하면 사용자가 선택한 비교 연산자에 따라 아래와 같이 Between 시작값과 종료값을 바인딩

구분	between 시작값(:min5)	between 종료값(:max5)
이하	0.00	50.00
미만	0.00	49.99
이상	50.00	999.99
초과	50.01	999.99