04.라이브러리 캐시 최적화 원리 - 11.Static SQL 구현을 위한 기법들

Dynamic SQL을 자주사용하게되는 두번째 사례는 조건절에 IN-LIST 항목이 가변적으로 변할때 입니다.
여기서 소개하는 사례들은 Dynamic SQL로 작성하더라도 생성될 수 있는 최대 SQL개수가 그다지 많지 않기 때문에 라이브러리 캐시에 그렇게 많은 부하를 주지는 않습니다.
문제는 이런저런 이유로 Dynamic SQL을 사용하는 순간 조건절 비교 값까지 습관적으로 literal 상수값을 사용하도록 개발한다는 데에 있습니다. 그런 뜻에서 꼭 필요한 경우가 아니라면 가급적 Static SQL로 작성하는 습관과 능력을 기를 수 있도록 몇 가지 사례를 소개하겠습니다.

(1)IN-LIST항목이 가변적이지만 최대 경우 수가 적은 경우
Static 방식으로 3가지 회원을 선택하는 경우 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 (‘01’,’02’,’03’);
Dynamic SQL로 구현하려면 아래와 같습니다.
select * from LP회원 where 회원번호 in (:a, :b, :c);
사용자가 입력하지 않은 항목에 null값을 입력하면 자동으로 결과집합에서 제외됩니다.
상황에 따라 decode문을 사용해야 할 떄도 있습니다.
내부적으로 ‘all’,’01’,’02’,’03’이 부여돼있다고 가정하고 작성한 것입니디.
select * From LP회원
where 회원번호 in (decode (:a,’all’,’01’,:b),decode(:a,’all’,’02’,:c),decode(:a,’all’,’03’,:d))

(2)IN-LIST항목이 가변적이고 최대 경우 수가 아주 많은 경우
in-list 항목이 가변적이고 최대 개수도 고정적이지만 경우 수가 많아 static SQL을 일일이 작성해 두는것은 불가능하고 바인드 변수를 사용하는 것도 쉽지 않습니다. 실행 시 바인드 변수에 값을 입력하는 코딩을 그만큼 많이 해야 하기 때문입니다.
SQL조건절에는 대게 좌변에 컬럼을 두고 우변에는 그것과 비교할 상수 또는 변수를 위치시킵니다. 이곳에서는 컬럼과 변수위치를 서로 바꿉니다.
select * from 수시공시내역
where 공시일자 = :일자
and :inlist like ‘%’ ||분류코드||’%’
조건절을 위와 같이 작성하고 사용자가 선택한 분류코드 등 구분자로 연결해 아래처럼 string형 변수에 담아서 바인딩하고 실행시키면 됩니다.
:inlist := ‘01,03,08,14,17,24,33,46,48,53’
참고로 문자열을 처리하는 오라클 내부 알고리즘 상 like 연산자보다 instr함수를 사용하면 더빠르므로 아래와 같이 작성해도 됩니다.(권고)
select * from 수시공시내역
where 공시일자 = :일자
and INSTR(:inlist,분류코드) > 0

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

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

(4)select - list 가 동적으로 바뀌는 경우
decode 함수 또는 case 구문을 활용하면 됩니다.
/* 1:평균 , 2:합계 */
decode(:gubun,’1’,avg(계약수),sum(계약수)),
decode(:gubun,’1’,avg(계약금액),sum(계약금액)),
decode(:gubun,’1’,avg(미결계약정금액),sum(미결제약정금액)),

(5)연산자가 바뀌는 경우
아래처럼 SQL을 작성하고 바인딩하는 값을 바꾸면 됩니다.
where 거래미형성률 between :min1 and :max1
and 일평균거래량 between :min2 and :max2
...

일평균 거래량은 도메인 9자리 숫자형이라고 정수값만 허용하면 속성을 가지고 있으면, 입력가능한 최소값은 0 최대값은 999,999,999입니다. 따라서 사용자가 1000주를 입력하면 사용자가 선택한 비교 연산자에 따라 아래와 같이 between 값을 바인딩 하면 됩니다.
이하 0 1000
미만 0 999
이상 1000 999999999
초과 1001 999999999