10. Dynamic SQL 사용 기준
2010.07.04 05:46
▣ Dynamic SQL 사용에 관한 기본 원칙
(1) Static SQL을 지원하는 개발 환경이면 Static SQL로 작성하는 것을 원칙으로 한다.
=> precompile하므로 런타임시 안정적인 프로그램 build가 가능하다.
(2) Dynamic SQL 사용해도 무방한 경우
① Precompile 과정에서 컴파일 오류가나는 문장 - 스칼라서브쿼리, 분석함수, ANSI 조인
② SQL 개수가 많아 Static SQL로 일일이 작성하면 개발생산성이 떨어질 경우
(3) (2)번의 Dynamic SQL 문의 경우 조건절에서는 바인드 변수 사용을 원칙으로 한다.
특히 사용 빈도가 높고 조건절 컬럼의 값 종류가 많을 때 반드시 준수한다.
(4) (3)번의 원칙에서 아래 경우는 예외적으로 인정한다.
① DW, OLTP와 같이 Long Running 질의는 하드파싱 시간은 큰 문제가 안된다.
② OLTP의 경우 사용빈도가 낮은 질의는 하드파싱이 문제가 안됨
③ 조건절의 값 종류가 소수일때, 특히 값 분포가 일정하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도할 때, 예) 증권시장코드={유가, 코스닥,주식파생,상품파생}
- Static vs Dynamic
Static SQL -> SQL Repository에 완성된 형태로 저장된 SQL
Dynamic SQL -> SQL Repository에 불완전한 형태로 저장한 후 런타임때 동적으로 생성
▣ 기본원칙이 지켜지지 않는 경우, 선택적 검색 조건
- Dynamic SQL로 조건절을 동적으로 구성하게되는 이유는?
(1) 조건절이 다양해서 사용자의 선택에 따라 조건절이 동적으로 바뀌는 경우
<p317 그림> 웹입력 화면
조건절을 줄이고 필수입력 항목을 만들고, 기간 조건의 범위를 줄인다.
- 업무상 조회 조건이 다양한 경우(거래일자, 종목코드, 투자자유형, 주문매체)
<p318 그림> : 거래일자 필수 나머지는 선택이다.
(SQL 문 작성 예1) 라이브러리 캐시를 비효율적으로 사용한다.(하드파싱 많이...)
select 거래일자, 종목코드, 투자자유형코드, 주문매체코드, ...
from 일별종목거래
where 거래일자 between :시작 and :종료
%option
(%option = " and 종목코드= ... and 투자유형코드= ...“)
(SQL 문 작성 예2) 라이브러리 캐시는 효율적이지만 인덱스를 비효율적으로 사용하게 된다.(하드파싱 1번..
select 거래일자, 종목코드, 투자자유형코드, 주문매체코드, ...
from 일별종목거래
where 거래일자 between :시작 and :종료
and 종목코드= nvl(:종목코드, 종목코드)
and 투자유형코드= nvl(:투자자유형코드, 투자자유형코드)
...
(SQL 문 작성 예3) union all 사용하여 최적화
- 종목코드, 투자자유형코드, 주문매체코드에 따라 SQL 문 8가지 따로 작성
- 인덱스 구성은 변별력이 있는 컬럼으로 생성
index 1 : 종목코드+거래일자
index 2 : 투자자유형코드+거래일자+주문매체구분코드
index 3 : 거래일자+주문매체구분코드
- SQL 문 작성
select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where :종목코드 is not null
and 거래일자 between :시작 and :종료
and 종목코드=:종목코드
and 투자자유형코드=nvl(:투자자유형, 투자자유형코드)
and 주문매체구분코드=nvl(:주문매체, 주문매체구분코드)
union all
select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where :종목코드 is null and :투자자유형 is not null
and 거래일자 between :시작 and :종료
and 투자자유형코드=nvl(:투자자유형, 투자자유형코드)
and 주문매체구분코드=nvl(:주문매체, 주문매체구분코드)
union all
select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where :종목코드 is null and :투자자유형 is null
and 거래일자 between :시작 and :종료
and 주문매체구분코드=nvl(:주문매체, 주문매체구분코드)
▣ 선택적 검색 조건에 대한 현실적인 대안
- Static SQL 사용을 원칙으로 하되 입력조건에 따라 SQL 문이 너무 많을 때는 Dynamic SQL을 허용한다.
조건절 SQL이 많아도 그 중 일부만 사용되므로 실질적인 하드 파싱 부하는 없다.
다만, 라이브러리 캐시 효율화의 중심인 바인드 변수 사용 원칙은 준수한다.
- 예제 : PL/SQL에서 조건절을 동적으로 구성하고 바인드 변수 사용한 예제
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 :시작일자, :종료일자, :종목코드, :투자자유형코드; |
- Dynamic SQL 문은 인덱스를 구성할 때 불편하다. 힌트를 사용하기도 어렵다.
=> 완성된 형태의 SQL들은 SQL Repository에 저장된 것을 참조하고, 그렇지 않은 것들은 수행된 최종 SQL들을 수집하여
자주 나타나는 액세스 유형을 기준으로 인덱스를 설계한다.
=> 위 예제에서 Dynamic SQL로 작성했을 때 오라클 서버를 통해서 수집되는 최종 SQL 문들
(1) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between ? and ?
(2) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between ? and ?
and 종목코드=?
(3) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between ? and ?
and 투자유형코드=?
(4) select 거래일자, 투자자유형코드, 회원번호, 체결건수, 체결수량, 거래대금
from 일별종목거래
where 거래일자 between ? and ?
and 종목코드=? and 투자유형코드=?
- 힌트를 사용하려면 Static SQL을 사용해야한다.
▣ 선택적 검색 조건에 사용할 수 있는 기법 성능 비교
A. OR 조건을 사용하는 경우
select * from 일별종목거래 where (:isu_cd is null OR isu_cd=:isu_cd) |
- Table Full Scan으로 처리하므로 인덱스 활용이 필요할 때는 이 방식은 안된다.(null 비교하면 full scan?)
B. LIKE 연산자를 사용하는 경우
select * from 일별종목거래 where isu_cd LIKE :isu_cd || '%' ) |
- 인덱스를 사용하지만 사용자가 :isu_cd 값을 입력하지 않았을 때 Table Full Scan이 유리한데도
인덱스를 사용하게되므로 성능이 나빠질 수 있다.
C. NVL 함수를 사용하는 경우
select * from 일별종목거래 where isu_cd = NVL(:isu_cd, isu_cd) |
D. DECODE 함수를 사용하는 경우(DECODE(비교값, case1, 결과1, case2, 결과2, ..., 기본값)
select * from 일별종목거래 where isu_cd = decode(:isu_cd, null, isu_cd, :isu_cd) |
- C,D는 사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 자동 분기된다.
- nvl 또는 decode 함수를 사용할 때, 해당컬럼이 not null이어야하며 null을 허용할 때는 결과가 달라진다.
(null=null 비교는 DBMS에 따라 다르다)
E. union all을 사용하는 경우
select * from 일별종목거래 where isu_cd is null union all select * from 일별종목거래 where isu_cd is not null and isu_cd=:isu_cd |
- 둘중 하나를 선택 실행한다.
- (정리)
① not null 컬럼일 경우 nvl, decode를 사용하는 것이 편하다(C,D)
② null을 허용하고 인덱스액세스로 의미있는 컬럼이면 union all을 사용해 명시적으로 분기한다.(E)
③ 인덱스 액세스 조건으로 참여하지 않는 경우(인덱스 필터 또는 테이블 필터 조건으로 사용), (:c is null or col=:c), (c like :c || '%'), 어떤 방식을 사용해도 무관하다(A, B)
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
27 | 5. V$SYSSTAT [1] | 토시리 | 2010.06.14 | 9849 |
26 | 1. 블록 단위 I/O | 토시리 | 2010.07.12 | 9381 |
25 | 4. 커서 공유 | balto | 2010.06.28 | 9200 |
24 | 3. Single Block vs. Multiblock I/O | 휘휘 | 2010.07.12 | 9172 |
23 | 2. User Call vs. Recursive Call | 토시리 | 2010.07.07 | 9050 |
» | 10. Dynamic SQL 사용 기준 | balto | 2010.07.04 | 8643 |
21 | 1. 트랜잭션 동시성 제어 | 실천하자 | 2010.05.31 | 8632 |
20 | 2. AutoTrace | 실천하자 | 2010.06.06 | 8599 |
19 | 3. 비관적 vs. 낙관적 동시성 제어 | 휘휘 | 2010.06.07 | 8210 |
18 | 9. Snapshot too old | balto | 2010.05.30 | 8102 |
17 | 11. End-To-End 성능관리 | 휘휘 | 2010.06.14 | 8095 |
16 | 7. Response Time Analysis 방법론과 OWI | balto | 2010.06.13 | 8067 |
15 | 10. 대기 이벤트 | balto | 2010.05.30 | 8012 |
14 | 8. I/O 효율화 원리 | 휘휘 | 2010.07.19 | 7742 |
13 | 2. Memory vs. Disk I/O | 휘휘 | 2010.07.12 | 7439 |
12 | 부록 | 휘휘 | 2010.07.19 | 7238 |
11 | 1. SQL과 옵티마이저 | 휘휘 | 2010.06.28 | 7218 |
10 | 12. 데이터베이스 성능 고도화 정석 해법 | 휘휘 | 2010.06.14 | 7153 |
9 | 4장. 라이브러리 캐시 최적화 원리 | 휘휘 | 2010.06.28 | 6915 |
8 | 2장. 트랜잭션과 Lock | 운영자 | 2010.06.01 | 6896 |