FUNCTION INDEXの性能再検証

<FUNCTION INDEXの性能再検証>
ペンネーム: 代打おやじ

今週は、筆者の都合により、RACのインストール以外の内容となり申し訳あり
ませんがお付き合いください。

過去にで、Oracle8iから実装されたFUNCTION
INDEXについて検証しましたが10gでは、はたしてどうなったのでしょう。

さあ、検証スタート

環境
OS:Windows2000 SP4
Oracle:10G R2(10.2.0.1.0)

FUCTION INDEXを使用するケースとしては、
・キー項目として文字列を使用しているが、大文字、小文字が混在している。
・検索字には大文字のみ使用する場合が操作する側でのUIである。

今回は、SCOTT/TIGERのEMP表を拡張し以下のテーブルを作成した

create table emp3
(
  empno  number(10),
  ename  varchar2(64),
  lename  varchar2(64),
  uename  varchar2(64),
  job  varchar2(9),
  mgr  number(10),
  hiredate  date,
  sal  number(7,2),
  comm  number(7,2),
  deptno  number(2)
)
/

create table emp6
(
  empno  number(10),
  ename  varchar2(64),
  lename  varchar2(64),
  uename  varchar2(64),
  job  varchar2(9),
  mgr  number(10),
  hiredate  date,
  sal  number(7,2),
  comm  number(7,2),
  deptno  number(2)
)
/

insert into emp3
(EMPNO, ENAME, LENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select
  empno,
  ename,
  lower(ename) lename,
  upper(ename) uename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno
 from emp
/
insert into emp6
(EMPNO, ENAME, LENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select
  empno,
  ename,
  lower(ename) lename,
  upper(ename) uename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno
 from emp
/

emp3表は、FUNCTION INDEXを使用するテーブルとして使用します。
emp6は、大文字に変換したカラムを用意し、そのカラムにINDEXを作成します。
2つのテーブルを使用することで、その性能を比較して見ます。

emp3にて使用するINDEXとして、lenameに対してupper関数を使用した検索用と
して
CREATE INDEX EMP3_UP_INDEX1 ON EMP3(UPPER(LENAME));
通常の検索用として
CREATE INDEX EMP3_INDEX2 ON EMP3(ENAME);

emp6にて使用するINDEXとして
CREATE INDEX EMP6_INDEX1 ON EMP6(UENAME);
通常の検索用として
CREATE INDEX EMP6_INDEX2 ON EMP6(ENAME);

emp3および、emp6に対する検索を実施した結果は、
emp3に対する検索
select EMPNO, ENAME, LENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO
from EMP3
where upper(lename)=upper('MARTIN86873')
/
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 5
    TABLE ACCESS BY INDEX ROWID EMP3
        INDEX RANGE SCAN EMP3_UP_INDEX1

emp6に対する検索
select EMPNO, ENAME, LENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO
from EMP6
where uename=upper('MARTIN86873')
/
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 5
    TABLE ACCESS BY INDEX ROWID EMP6
        INDEX RANGE SCAN EMP6_INDEX1

となり、同様のコストにて検索できることが確認できました。

では、挿入にたいする性能はどうでしょうか。

emp3、emp6ともに、70万行まで挿入を繰り返し、更に8万行弱追加挿入するこ
とを実行し、実行時間を計測してみます。

alter system flush shared_pool;

alter system flush buffer_cache;

alter session set events='10046 trace name context forever, level 12' 

insert into EMP3
(EMPNO, ENAME, LENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select B.EMPNO+20000000, concat(B.ENAME,'30'), concat(B.LENAME,'30'), concat(B.UENAME,'30'), B.JOB,
  B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO
from EMP5 B

77968行が作成されました。

SQL Traceの結果は

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.12       1.25         51        262          0           0
Execute      1     10.78     262.04      11122      10125     499356       77968
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     10.90     263.30      11173      10387     499356       77968

1行挿入にかかる時間は、3.38ms、CPU時間は0.14msとなりました。

alter system flush shared_pool;

alter system flush buffer_cache;

alter session set events='10046 trace name context forever, level 12' 

insert into EMP6
(EMPNO, ENAME, LENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select B.EMPNO+20000000, concat(B.ENAME,'30'), concat(B.LENAME,'30'), concat(B.UENAME,'30'), B.JOB,
  B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO
from EMP5 B
 
77968行が作成されました。

SQL Traceの結果は

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.06          0          0          0           0
Execute      1     10.29     283.77      12014      10070     499392       77968
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     10.34     283.84      12014      10070     499392       77968

1行挿入にかかる時間は、3.64ms、CPU時間は0.13msとなりました。

となり、FUNCTION INDEXを使用しても、検索要件を満たす構造にテーブルを変
更した場合と比較して、まったく問題が無いと考えられる。
ましてや、大文字検索用に別のカラムを追加することを検討するならば、積極
的にFUNCTION INDEXを使用するほうが良いのではないでしょうか。

さて、もう少し実際に行われる業務に近い形でテーブルを作成しなおして検証
してみます。

さあテーブルを作成し、INDEXも作成しましょう。

これまで使用してきたテーブルを利用して、初期テーブルを作成します。
emp_orgは、元のemp表に近い形で作成します。

create table emp_org as
select EMPNO, LENAME ENAME, JOB, MGR, HIREDATE, SAL, COMM, 
	DEPTNO
from EMP6;
emp_uppは、emp表に大文字を格納するカラムを追加しておきます。

create table emp_upp as
select EMPNO, LENAME ENAME,UENAME, JOB, MGR, HIREDATE, SAL, COMM, 
	DEPTNO
from EMP6 

emp_org表に作成するインデックスは、empnoに対しユニークなインデックスと、
enameに対して、FUNCTION INDEXおよび通常のインデックスを作成します。

create index emp_org_idx1 on emp_org(empno);

create index emp_org_idx2 on emp_org(ename);

create index emp_org_idx3 on emp_org(upper(ename));

emp_upp表に作成するインデックスは、empnoに対しユニークなインデックスと、
enameとuenameに対して、通常のインデックスを作成します。
create index emp_upp_idx1 on emp_upp(empno);

create index emp_upp_idx2 on emp_upp(ename);

create index emp_upp_idx3 on emp_upp(uename);

さて、準備が出来ましたので、繰り返しINSERTを実施し、100万行まで、追加
しておきましょう。

select count(a.empno) from emp_org a;
で、行数を確認します。
1020332行になっています。

では、以下のSQL文にてINSERTを試みます。

まずは、emp_orgへの63772行INSERTです。

alter system flush shared_pool;

alter system flush buffer_cache;

alter session set timed_statistics = true ;

alter session set events='10046 trace name context forever, level 12';

insert into EMP_ORG
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select B.EMPNO+3587000, concat(B.ENAME,'43'), B.JOB,
	B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO
from EMP5 B
;
commit;

次に、emp_uppへの63772行INSERTです。

alter system flush shared_pool;

alter system flush buffer_cache;

alter session set timed_statistics = true ;

alter session set events='10046 trace name context forever, level 12';

insert into EMP_UPP
(EMPNO, ENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select B.EMPNO+3587000, concat(B.ENAME,'43'), concat(upper(B.ENAME),'43'), B.JOB,
  B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO
from EMP5 B
;
commit;

SQL TRACEの内容を確認してみましょう。

insert into EMP_ORG
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select B.EMPNO+3587000, concat(B.ENAME,'43'), B.JOB,
	B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO
from EMP5 B
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       2.12         51        273          0           0
Execute      1      9.84     187.54       8098      16049     417643       63772
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.92     189.66       8149      16322     417643       63772

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
  63772  TABLE ACCESS FULL EMP5 (cr=693 pr=691 pw=0 time=380149 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      7295        0.53        114.74
  db file scattered read                         44        0.80          7.34
  control file sequential read                  209        0.60         10.05
  Data file init write                           47        0.43          8.79
  db file single write                           11        0.03          0.10
  control file parallel write                    33        0.11          0.99
  rdbms ipc reply                                11        0.48          3.02
  log file switch completion                      3        0.99          1.58
  latch: enqueue hash chains                      1        0.00          0.00
  latch: shared pool                              1        0.00          0.00
  undo segment extension                          1        0.00          0.00
  log file sync                                   1        0.14          0.14
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.03          0.03
********************************************************************************

insert into EMP_UPP
(EMPNO, ENAME, UENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
select B.EMPNO+3587000, concat(B.ENAME,'43'), concat(upper(B.ENAME),'43'), B.JOB,
  B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO
from EMP5 B


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.14       1.99         15        225          0           0
Execute      1      9.28     348.85       8939      15091     418649       63772
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.42     350.85       8954      15316     418649       63772

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
  63772  TABLE ACCESS FULL EMP5 (cr=693 pr=691 pw=0 time=904508 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      8153        1.14        278.01
  db file scattered read                         44        1.23          8.09
  control file sequential read                  133        4.63         13.61
  Data file init write                           34        0.68          7.26
  db file single write                            7        0.08          0.31
  control file parallel write                    21        0.23          0.98
  rdbms ipc reply                                 8        0.81          2.84
  log file switch completion                      3        0.98          1.76
  latch: shared pool                              1        0.01          0.01
  log file sync                                   1        0.13          0.13
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.17          0.17

おや、emp_uppへのINSERTが、前回に比べて格段に時間が掛かっています。
emp_orgへは、189.66秒なのに対し、emp_uppには、350.85秒です。
この違いは、どこにあるのでしょう。
差は、161.19秒ですね。Elapsed timeの詳細を見てみると
emp_orgへは

 db file sequential read                      7295        0.53        114.74
emp_uppへは
 db file sequential read                      8153        1.14        278.01

となっています。
その差が163.27秒ありますので、ほとんど、この部分によって差がでている事が判明しました。

と、予想通りの結果がでてきました。