[요약]
Oracle에서 지원하는 Function Based Index (이하 FBI) 를 DB2에서 구현하여 테스트
[내용]
DB2 V9.7은 FBI를 공식적으로 지원하지 않기 때문에, 이와 비슷한 기능을 하도록 구성하여 성능 테스트를 수행
- 테이블 생성
DROP TABLE DB2INST1.ZIP
CREATE TABLE DB2INST1.ZIP (
ZIPCODE CHAR(7) NOT NULL,
SIDO CHAR(6) NOT NULL,
GUGUN VARCHAR(24) NOT NULL,
DONG VARCHAR(81) NOT NULL,
BUNJI VARCHAR(48),
SEQ INTEGER NOT NULL,
CONSTRAINT PK_ZIP PRIMARY KEY(SEQ)
)
CREATE INDEX DB2INST1.IX_ZIP ON DB2INST1.ZIP (ZIPCODE)
- 데이터 이행
IMPORT FROM ./zipcode_1_20100225.csv OF DEL COMMITCOUNT 1000 REPLACE INTO DB2INST1.ZIP
- 통계정보 갱신
RUNSTATS ON TABLE DB2INST1.ZIP ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL
- Predicate에 함수를 사용하지 않은 쿼리 작성 후, 수행
-- db2batch 입력 파일 (query1.bat)
--#COMMENT FBI Test on DB2 V9.7
--#SET PERF_DETAIL 0
--#BGBLK 1
--#COMMENT Predicate에 함수를 사용하지 않은 일반적인 경우
--#SET ROWS_OUT -1
SELECT *
FROM DB2INST1.ZIP
WHERE ZIPCODE LIKE '142-%';
--#EOBLK
db2batch -d SAMPLE -f query1.bat -i complete -o e YES p 1 r -1
* 292행 페치(fetch)됨, 292행 출력됨.
* 경과 시간: 0.039400초
* 블록 번호 1의 끝
* 요약 테이블:
유형 수 반복 총시간(초) 최소 시간(초) 최대 시간(초) 산술 평균 기하 평균 페치(fetch)된 행 출된 행
--------- ----------- ----------- -------------- ------------------ ------------------ -------------- -------------- -------------------- -------------
블록 1 1 0.039400 0.039400 0.039400 0.039400 0.039400 292 292
* 총 항목: 1
* 총시간: 0.039400초
* 최소 시간: 0.039400초
* 최대 시간: 0.039400초
* 산술 평균 시간: 0.039400초
* 기하 평균 시간: 0.039400초
---------------------------------------------
- 실행계획 확인
db2exfmt -d SAMPLE -1
Access Plan:
-----------
Total Cost: 15.135
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
2.64573
FETCH
( 2)
15.135
2
/----+-----\
2.64573 50838
IXSCAN TABLE: DB2INST1
( 3) ZIP
7.57267 Q1
1
|
50838
INDEX: DB2INST1
IX_ZIP
Q1
- Predicate에 함수를 사용한 쿼리 작성 후, 수행
-- db2batch 입력 파일 (query2.bat)
--#COMMENT FBI Test on DB2 V9.7
--#SET PERF_DETAIL 0
--#BGBLK 1
--#COMMENT Predicate에 함수를 테스트 목적으로 사용한 경우
--#SET ROWS_OUT -1
SELECT *
FROM DB2INST1.ZIP
WHERE TRIM(ZIPCODE) LIKE '142-%';
--#EOBLK
db2batch -d SAMPLE -f query2.bat -i complete -o e YES p 1 r -1
* 292행 페치(fetch)됨, 292행 출력됨.
* 블록 번호 1의 끝
* 요약 테이블:
유형 수 반복 총시간(초) 최소 시간(초) 최대 시간(초) 산술 평균 기하 평균 페치(fetch)된 행 출된 행
--------- ----------- ----------- -------------- ------------------ ------------------ -------------- -------------- -------------------- -------------
블록 1 1 0.051749 0.051749 0.051749 0.051749 0.051749 292 292
* 총 항목: 1
* 총시간: 0.051749초
* 최소 시간: 0.051749초
* 최대 시간: 0.051749초
* 산술 평균 시간: 0.051749초
* 기하 평균 시간: 0.051749초
---------------------------------------------
- 실행계획 확인
db2exfmt -d SAMPLE -1
Access Plan:
-----------
Total Cost: 473.96
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
5083.8
TBSCAN
( 2)
473.96
454
|
50838
TABLE: DB2INST1
ZIP
Q1
- Generate Always 옵션으로 함수가 적용된 컬럼을 테이블에 추가 후, 인덱스 생성
SET INTEGRITY FOR DB2INST1.ZIP OFF
ALTER TABLE DB2INST1.ZIP ADD COLUMN TRIM_ZIPCODE GENERATED ALWAYS AS (TRIM(ZIPCODE))
SET INTEGRITY FOR DB2INST1.ZIP IMMEDIATE CHECKED FORCE GENERATED
CREATE INDEX DB2INST1.IX_TRIM_ZIPCODE ON DB2INST1.ZIP (TRIM_ZIPCODE)
- 통계정보 갱신
RUNSTATS ON TABLE DB2INST1.ZIP ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL
- Predicate에 함수를 적용한 쿼리를 다시 수행
db2batch -d SAMPLE -f query2.bat -i complete -o e YES p 1 r -1
* 292행 페치(fetch)됨, 292행 출력됨.
* 블록 번호 1의 끝
* 요약 테이블:
유형 수 반복 총시간(초) 최소 시간(초) 최대 시간(초) 산술 평균 기하 평균 페치(fetch)된 행 출된 행
--------- ----------- ----------- -------------- ------------------ ------------------ -------------- -------------- -------------------- -------------
블록 1 1 0.014503 0.014503 0.014503 0.014503 0.014503 292 292
* 총 항목: 1
* 총시간: 0.014503초
* 최소 시간: 0.014503초
* 최대 시간: 0.014503초
* 산술 평균 시간: 0.014503초
* 기하 평균 시간: 0.014503초
---------------------------------------------
- 실행계획 확인
db2exfmt -d SAMPLE -1
Access Plan:
-----------
Total Cost: 224.481
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
154.13
FETCH
( 2)
224.481
29.6758
/----+-----\
154.13 50838
IXSCAN TABLE: DB2INST1
( 3) ZIP
7.61844 Q1
1
|
50838
INDEX: DB2INST1
IX_TRIM_ZIPCODE
Q1
- 테스트를 위해 추가했었던 컬럼 삭제
ALTER TABLE DB2INST1.ZIP DROP COLUMN TRIM_ZIPCODE CASCADE