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を作成します。
２つのテーブルを使用することで、その性能を比較して見ます。
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秒ありますので、ほとんど、この部分によって差がでている事が判明しました。
と、予想通りの結果がでてきました。