◇検証内容
作成したTABLE、INDEX_TESTに対して以下のSQLを実行する。
・パターン１
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
・パターン２
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_2) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
パターン１の場合、Hint句で指定されたINDEXが持っている項目はCOLUMN2
のみ。
（検索条件に指定されている項目の中にINDEXに含まれていない項目が存在
する。）
パターン２の場合、Hint句で指定されたINDEXはCOLUMN1とCOLUMN2、両方の
項目を持っている。
（検索条件に指定されている項目が全てINDEXに含まれている。）
両パターン共、指定されている検索条件は同一。
そして両パターン共、抽出してくる件数は100,000件。
２つのSQLを実行する際にSQL*Traceを取得し実行時間に差が出るかを検証
する。
果たして検索性能に差が出るだろうか？？
抽出してくる件数が一緒であれば、さほど性能に差は出ないように思われる。
IND_INDEX_TEST_1はCOLUMN2のみで構成されるINDEXなので、IND_INDEX_TEST
_2に比べオブジェクトのサイズが小さいはず。
それを考慮するとパターン１のほうが若干、速いだろうか？？
ついでに、参考値としてTABLE Full Scanさせた場合のTraceも取得してお
こう。
・パターン３
select /*+ FULL(INDEX_TEST) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
それぞれのSQLを発行する直前に
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
を発行しBUFFER_CACHEとSHARED_POOLを空っぽにした。
言うまでも無いが、これはBLOCKがメモリに乗ってしまうことにより正確な
性能が測れなくなることを防ぐ為。
◇検証結果
それでは取得したSQL*Traceの内容を確認してみよう。
・パターン１（COLUMN2のみのINDEXを使用。）
********************************************************************************
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.21 0.41 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 6.73 134.39 100197 106864 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 6.95 134.81 100197 106864 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106864 pr=100197 pw=0 time=106405267 us)
100000 INDEX RANGE SCAN IND_INDEX_TEST_1 (cr=6864 pr=197 pw=0 time=19448260 us)(object id 59176)
********************************************************************************
・パターン２（COLUMN1、COLUMN2の複合INDEXを使用。）
********************************************************************************
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_2) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.40 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 6.53 123.89 100267 106934 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 6.70 124.30 100267 106934 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106934 pr=100267 pw=0 time=113771208 us)
100000 INDEX RANGE SCAN IND_INDEX_TEST_2 (cr=6934 pr=267 pw=0 time=645296 us)(object id 59181)
********************************************************************************
まず、実行計画を確認してみよう。
どちらのSQLも間違いなく指定したINDEXを使用して検索をしている。
続いてelapsedを比較してみよう。
このままだと見難いのでtotalだけ抜き出して並べてみる。
cpu elapsed disk query current rows
-------- ---------- ---------- ---------- ---------- ----------
パターン１ 6.95 134.81 100197 106864 0 100000
パターン２ 6.70 124.30 100267 106934 0 100000
・・・・・・あれ？
なんか、おかしいな。
disk、queryはパターン１の方が少ない。
これは当初の予想通りIND_INDEX_TEST_1の方がCOLUMN1を持っていない分、
OBJECTのサイズが小さいのだろう。
にも関わらず、elapsedはパターン１の方が大きい値を示している。
つまり、それだけ時間がかかっているということになる。
その差、約10秒。
この時間差は、なんだろう？？
測定誤差かも？と思い何度か計測し直してみたが、やはり同様の差が出てし
まう。
では、この結果を正として、こうなる理由を考えてみよう。
続きは来週・・・
作成したTABLE、INDEX_TESTに対して以下のSQLを実行する。
・パターン１
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
・パターン２
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_2) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
パターン１の場合、Hint句で指定されたINDEXが持っている項目はCOLUMN2
のみ。
（検索条件に指定されている項目の中にINDEXに含まれていない項目が存在
する。）
パターン２の場合、Hint句で指定されたINDEXはCOLUMN1とCOLUMN2、両方の
項目を持っている。
（検索条件に指定されている項目が全てINDEXに含まれている。）
両パターン共、指定されている検索条件は同一。
そして両パターン共、抽出してくる件数は100,000件。
２つのSQLを実行する際にSQL*Traceを取得し実行時間に差が出るかを検証
する。
果たして検索性能に差が出るだろうか？？
抽出してくる件数が一緒であれば、さほど性能に差は出ないように思われる。
IND_INDEX_TEST_1はCOLUMN2のみで構成されるINDEXなので、IND_INDEX_TEST
_2に比べオブジェクトのサイズが小さいはず。
それを考慮するとパターン１のほうが若干、速いだろうか？？
ついでに、参考値としてTABLE Full Scanさせた場合のTraceも取得してお
こう。
・パターン３
select /*+ FULL(INDEX_TEST) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
それぞれのSQLを発行する直前に
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
を発行しBUFFER_CACHEとSHARED_POOLを空っぽにした。
言うまでも無いが、これはBLOCKがメモリに乗ってしまうことにより正確な
性能が測れなくなることを防ぐ為。
◇検証結果
それでは取得したSQL*Traceの内容を確認してみよう。
・パターン１（COLUMN2のみのINDEXを使用。）
********************************************************************************
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.21 0.41 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 6.73 134.39 100197 106864 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 6.95 134.81 100197 106864 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106864 pr=100197 pw=0 time=106405267 us)
100000 INDEX RANGE SCAN IND_INDEX_TEST_1 (cr=6864 pr=197 pw=0 time=19448260 us)(object id 59176)
********************************************************************************
・パターン２（COLUMN1、COLUMN2の複合INDEXを使用。）
********************************************************************************
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_2) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.40 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 6.53 123.89 100267 106934 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 6.70 124.30 100267 106934 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS BY INDEX ROWID INDEX_TEST (cr=106934 pr=100267 pw=0 time=113771208 us)
100000 INDEX RANGE SCAN IND_INDEX_TEST_2 (cr=6934 pr=267 pw=0 time=645296 us)(object id 59181)
********************************************************************************
まず、実行計画を確認してみよう。
どちらのSQLも間違いなく指定したINDEXを使用して検索をしている。
続いてelapsedを比較してみよう。
このままだと見難いのでtotalだけ抜き出して並べてみる。
cpu elapsed disk query current rows
-------- ---------- ---------- ---------- ---------- ----------
パターン１ 6.95 134.81 100197 106864 0 100000
パターン２ 6.70 124.30 100267 106934 0 100000
・・・・・・あれ？
なんか、おかしいな。
disk、queryはパターン１の方が少ない。
これは当初の予想通りIND_INDEX_TEST_1の方がCOLUMN1を持っていない分、
OBJECTのサイズが小さいのだろう。
にも関わらず、elapsedはパターン１の方が大きい値を示している。
つまり、それだけ時間がかかっているということになる。
その差、約10秒。
この時間差は、なんだろう？？
測定誤差かも？と思い何度か計測し直してみたが、やはり同様の差が出てし
まう。
では、この結果を正として、こうなる理由を考えてみよう。
続きは来週・・・