FBI (Function Based Index) 테스트

[요약]

Oracle에서 지원하는 Function Based Index (이하 FBI) 를 DB2에서 구현하여 테스트

 

[내용]

DB2 V9.7은 FBI를 공식적으로 지원하지 않기 때문에, 이와 비슷한 기능을 하도록 구성하여 성능 테스트를 수행

 

1. 테이블 생성

 

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)

 

2. 데이터 이행

 

IMPORT FROM ./zipcode_1_20100225.csv OF DEL COMMITCOUNT 1000 REPLACE INTO DB2INST1.ZIP

 

3. 통계정보 갱신

 

RUNSTATS ON TABLE DB2INST1.ZIP ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL

 

4. 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초

---------------------------------------------

 

5. 실행계획 확인

 

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

 

6. 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초

---------------------------------------------

 

7. 실행계획 확인

 

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

 

8. 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)

 

9. 통계정보 갱신

 

RUNSTATS ON TABLE DB2INST1.ZIP ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL

 

10. 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초

---------------------------------------------

 

11. 실행계획 확인

 

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

 

12. 테스트를 위해 추가했었던 컬럼 삭제

 

ALTER TABLE DB2INST1.ZIP DROP COLUMN TRIM_ZIPCODE CASCADE