◇仮説の証明
それでは前述の仮説が正しいかどうか検証してみよう。
INDEXに存在しない項目が検索条件に入っていることによって性能が劣化し
ているのであれば、その項目を検索条件から外してやれば良いのだ。
新たに以下のSQLを実行しSQL*Traceを取得する。
・パターン４
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN2 = 1;
「COLUMN1 = 99999」という条件を外しても検索結果は変わらず100,000件。
なので他の条件は同一となる。
この検索でパターン２と同程度の検索性能が出れば仮説は正しいと思える。
それではSQL*Traceを取得し内容を確認してみよう。
・パターン４（COLUMN2のみのINDEXを使用。）
********************************************************************************
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN2 = 1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.26 0.46 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 6668 6.20 119.91 100197 106864 0
100000
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6670 6.46 120.38 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=96560385 us)
100000 INDEX RANGE SCAN IND_INDEX_TEST_1 (cr=6864 pr=197 pw=0
time=1748328 us)(object id 59176)
********************************************************************************
パターン１、パターン２の時と比較してelapsedの値は、どうだろうか？
totalの行だけ並べて比較してみよう。
cpu elapsed disk query current
rows
-------- ---------- ---------- ---------- ----------
----------
パターン１ 6.95 134.81 100197 106864 0
100000
パターン２ 6.70 124.30 100267 106934 0
100000
パターン４ 6.46 120.38 100197 106864 0
100000
結果としてパターン２よりも4Sec.程度速くなった。
これは物理的なデータの読み込み数（disk）や論理的なデータの読み込み数
（query）の差だろう。
上記の結果から
「検索条件に指定されている項目の中にINDEXに含まれていない項目が存在す
る場合、検索条件に指定されている項目が全てINDEXに含まれているよりも性
能劣化が起こる。」
ということが言える。
以上でINDEXに存在しない項目を検索条件に入れた際の性能に対する影響の
検証を終わる。
◇おまけ
実はパターン３というSQLもTraceを取得していたのを覚えているだろうか？
・パターン３
select /*+ FULL(INDEX_TEST) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
このSQLは性能の参考値としてTraceを取得していた。
せっかくなので、このSQLもTraceを確認してみよう。
********************************************************************************
select /*+ FULL(INDEX_TEST) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.14 0.55 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 6668 5.67 70.95 140847 147442 0
100000
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6670 5.81 71.50 140847 147442 0
100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS FULL INDEX_TEST (cr=147442 pr=140847 pw=0
time=40961305 us)
********************************************************************************
・・・・・・。
・・・・・・。
・・・・・・。
・・・・・・。
・・・・・・。
・・・・・・。
正直、この結果にはちょっとびっくり。
何がって？
分かりやすいようにtotalの行だけ他の３つのSQLと並べて比較してみよう。
cpu elapsed disk query current
rows
-------- ---------- ---------- ---------- ----------
----------
パターン１ 6.95 134.81 100197 106864 0
100000
パターン２ 6.70 124.30 100267 106934 0
100000
パターン３ 5.81 71.50 140847 147442 0
100000
パターン４ 6.46 120.38 100197 106864 0
100000
もう御理解いただけただろうか？？
elapsedの値に注目してほしい。
TABLE FULL SCANさせたパターン３のSQLがINDEX SCANさせた他のSQLよりも
性能が良い。。。
一般的に検索を実行する場合、INDEXで抽出対象となるRECORDをTABLE全体の
件数の5%以下に絞り込むことが出来るのであればINDEX SCANの方が性能が良い
と言われている。
今回の検索ではINDEXでTABLEの件数の1%まで絞り込めるにも関わらずTABLE
FULL SCANの方が性能が良いとは・・・。
まぁ、ケースバイケースということだな。
検索を実行する時は、あまり一般論に囚われずちゃんと実環境に則した環境
で性能評価を行った方が良い。
それでは前述の仮説が正しいかどうか検証してみよう。
INDEXに存在しない項目が検索条件に入っていることによって性能が劣化し
ているのであれば、その項目を検索条件から外してやれば良いのだ。
新たに以下のSQLを実行しSQL*Traceを取得する。
・パターン４
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN2 = 1;
「COLUMN1 = 99999」という条件を外しても検索結果は変わらず100,000件。
なので他の条件は同一となる。
この検索でパターン２と同程度の検索性能が出れば仮説は正しいと思える。
それではSQL*Traceを取得し内容を確認してみよう。
・パターン４（COLUMN2のみのINDEXを使用。）
********************************************************************************
select /*+ INDEX(INDEX_TEST IND_INDEX_TEST_1) */ *
from INDEX_TEST
where COLUMN2 = 1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.26 0.46 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 6668 6.20 119.91 100197 106864 0
100000
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6670 6.46 120.38 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=96560385 us)
100000 INDEX RANGE SCAN IND_INDEX_TEST_1 (cr=6864 pr=197 pw=0
time=1748328 us)(object id 59176)
********************************************************************************
パターン１、パターン２の時と比較してelapsedの値は、どうだろうか？
totalの行だけ並べて比較してみよう。
cpu elapsed disk query current
rows
-------- ---------- ---------- ---------- ----------
----------
パターン１ 6.95 134.81 100197 106864 0
100000
パターン２ 6.70 124.30 100267 106934 0
100000
パターン４ 6.46 120.38 100197 106864 0
100000
結果としてパターン２よりも4Sec.程度速くなった。
これは物理的なデータの読み込み数（disk）や論理的なデータの読み込み数
（query）の差だろう。
上記の結果から
「検索条件に指定されている項目の中にINDEXに含まれていない項目が存在す
る場合、検索条件に指定されている項目が全てINDEXに含まれているよりも性
能劣化が起こる。」
ということが言える。
以上でINDEXに存在しない項目を検索条件に入れた際の性能に対する影響の
検証を終わる。
◇おまけ
実はパターン３というSQLもTraceを取得していたのを覚えているだろうか？
・パターン３
select /*+ FULL(INDEX_TEST) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1;
このSQLは性能の参考値としてTraceを取得していた。
せっかくなので、このSQLもTraceを確認してみよう。
********************************************************************************
select /*+ FULL(INDEX_TEST) */ *
from INDEX_TEST
where COLUMN1 = 99999
and COLUMN2 = 1
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.14 0.55 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 6668 5.67 70.95 140847 147442 0
100000
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 6670 5.81 71.50 140847 147442 0
100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS FULL INDEX_TEST (cr=147442 pr=140847 pw=0
time=40961305 us)
********************************************************************************
・・・・・・。
・・・・・・。
・・・・・・。
・・・・・・。
・・・・・・。
・・・・・・。
正直、この結果にはちょっとびっくり。
何がって？
分かりやすいようにtotalの行だけ他の３つのSQLと並べて比較してみよう。
cpu elapsed disk query current
rows
-------- ---------- ---------- ---------- ----------
----------
パターン１ 6.95 134.81 100197 106864 0
100000
パターン２ 6.70 124.30 100267 106934 0
100000
パターン３ 5.81 71.50 140847 147442 0
100000
パターン４ 6.46 120.38 100197 106864 0
100000
もう御理解いただけただろうか？？
elapsedの値に注目してほしい。
TABLE FULL SCANさせたパターン３のSQLがINDEX SCANさせた他のSQLよりも
性能が良い。。。
一般的に検索を実行する場合、INDEXで抽出対象となるRECORDをTABLE全体の
件数の5%以下に絞り込むことが出来るのであればINDEX SCANの方が性能が良い
と言われている。
今回の検索ではINDEXでTABLEの件数の1%まで絞り込めるにも関わらずTABLE
FULL SCANの方が性能が良いとは・・・。
まぁ、ケースバイケースということだな。
検索を実行する時は、あまり一般論に囚われずちゃんと実環境に則した環境
で性能評価を行った方が良い。