1. 목적
Oracle에서 cubrid로 전환 작업시 다중컬럼 IN 조건문 사용으로 발생하는 비효율을 OR 문을 사용하여 개선하고
ibatis을 이용한 응용 개발시 성능과 편의성을 동시에 만족 시킬 수 있는 SQL 작성 방법에 대하여 알아본다.
2. 스키마 정보
테이블 정보
CREATE TABLE [athlete] (
[code] INTEGER AUTO_INCREMENT(16694, 1) NOT NULL,
[name] CHARACTER VARYING (40) NOT NULL,
[gender] CHARACTER(3),
[nation_code] CHARACTER(9),
[event] CHARACTER VARYING (30),
CONSTRAINT [pk_athlete_code] PRIMARY KEY([code]),
)
인텍스 정보
CREATE INDEX index_athlete_nation_gender ON athlete (nation_code, gender)
3. 다중컬럼 IN 처리 최적화
다중컬럼 IN 검색 조건
SELECT * FROM athlete
WHERE (nation_code , gender) IN ( ('KOR', ‘M’), ('JPN', 'W') )
OR 검색 조건
SELECT * FROM athlete
WHERE (nation_code = 'KOR' AND gender = 'M')
OR
(nation_code = 'JPN' AND gender = 'W')
설명
다중컬럼 IN 검색조건일 경우 Full Scan 하여 207 비용 발생 하였으며
OR 조건문을 이용한 검색 조건일 경우 INDEX Scan 하여 5 비용 발생하여 효율이 증가 하였다
4. 다중컬럼 IN 조건 SQL OR 조건 SQL로 변경 방법 (전자정부프레임워크 + ibatis + Cubrid)
AS-IS
Controller
HashMap<String, Object> map = new HashMap<String, Object>(); ArrayList<String> list1 = new ArrayList<String>(); ArrayList<String> name = new ArrayList<String>(); list1.add("('KOR', 'M')"); list1.add("('JPY', 'W')"); map.put("ID", list1);
List<?> sampleList = sampleService.selectSampleList(map); |
ibatis
<select id="sampleDAO.selectMapList" parameterClass="HashMap" resultClass="egovMap"> SELECT * FROM ATHLETE
WHERE (NATION_CODE, GENDER) IN <iterate property="ID" open="(" close=")" conjunction="," > $ID[]$ </iterate> ORDER BY NATION_CODE ASC </select> |
결과
Preparing Statement: SELECT * FROM ATHLETE WHERE (NATION_CODE, GENDER) IN ( ('KOR', 'M'), ('JPN', 'W') ) ORDER BY NATION_CODE ASC |
설명
$ 사용 함으로 Bind 변수 사용하지 못하여 조건 변경에 따른 하드파싱이 예상되며
인덱스 사용이 불가능하여 쿼리 수행에 비효율이 발생
TO-BE
Controller
ArrayList<HashMap<String, String>> list1 = new ArrayList<HashMap<String, String>>(); HashMap<String, String> map;
map = new HashMap<String, String>(); map.put("NATION_CODE", "KOR"); map.put("GENDER", "M"); list1.add(map);
map = new HashMap<String, String>(); map.put("NATION_CODE", "JPN"); map.put("GENDER", "W"); list1.add(map);
List<?> sampleList = sampleService.selectSampleList(list1); |
ibaits
<select id="sampleDAO.selectMapList" parameterClass="java.util.ArrayList" resultClass="egovMap"> SELECT * FROM ATHLETE WHERE <iterate conjunction="OR"> (nation_code = #[].NATION_CODE# AND GENDER = #[].GENDER#) </iterate> ORDER BY NATION_CODE ASC </select> |
결과
Preparing Statement: SELECT * FROM ATHLETE WHERE (nation_code = ? AND GENDER = ?) OR (nation_code = ? AND GENDER = ?) ORDER BY NATION_CODE ASC
Parameters: [KOR, M, JPN, W] |
설명
# 사용으로 Bind 변수를 사용 하고 그로 인하여 하드파싱을 피할 수 있으며
INDEX 이용으로 최적의 질의 수행이 가능하다