超簡単にテストデータを増幅する①
とりあえずDBに大量のデータを突っ込みたいことってあるよね、という話。
例えば「負荷試験のために数年後を想定したデータ量に増幅する」とか。
しかもリリース前のシステムだったりすると、それはもはやデータを「増幅する」というよりは「作成する」と言った方が正しい苦行になることも。
さて、どうやって乗り切るか。
やり方1 Excelで頑張る
思考停止して急場をしのぐならExcelのオートフィルと関数に頼って大量のINSERT文を作るのもアリといえばアリだと思う。
1万レコード未満なら多分これで全然OK。
DBも選ばないし汎用的。
ただし100万レコード作るとかになってくるとそうも言ってられない。
多分無限に時間がかかる。
やり方2 CSVをSQL*Loaderでロードする
みんな一番やりそうなのが簡単なスクリプトを書いてCSVを作ってからSQL*Loaderで流し込むっていう方法なんじゃなかろうか。
一回CSVを作っちゃえば出し入れも簡単だし、Oracleじゃなくても大体CSVからロードする機能はあるので汎用的でGood。
ただこれも100GBレベルのデータをいくつも作るとかになってくるとつらい。
一時的に置くにしても場所に困るし、そもそも一回ファイルに吐き出したデータをもう一回DBに入れるのが実は二度手間になってる。
領域と時間の無駄。
やり方3 SQLで既存データを増やす
サンプルデータとか既存データとか元から入ってるデータがあるならこれが一番早いと思う。
直接DBからDBにデータを入れるだけなので極めてシンプル。
insert into emp_copy select , empno + 1000 -- 適当に重複しないように , ename -- そのまま増やして良さそうなものはそのままコピー , job , mgr + 1000 -- 増幅した範囲同士で紐づくようにしてみる , hiredate + trunc(dbms_random.value(1,356)) -- 適当にばらけさせてみる , sal , comm , deptno from emp ;
元データの分布に沿って増幅できるので手軽。
PL/SQLで適当に繰り返しておけばいくらでも増やせる。
declare begin for i in 1 .. 10 loop insert into emp_copy select , empno + 1000 * i : , hiredate + 365 * i -- これで1年ずらしてコピーできる : from emp ; end loop; end; /
ただ大体こんな風にうまくいくことはない。
やり方4 SQLでどこからともなくデータを生み出す
DB上で直接データを作りながら投入しちゃう方法。
これだけ覚えていってもらえれば良い気がする。
基本的には既存データを増やすのと同じ考え方。
データを作らないといけない都合上値を一定の範囲に分散させたりする小細工は関数で頑張る。
insert into test select , level -- 連番 , lpad(level, 8, '0') -- 連番を8桁パディング , mod(level - 1, 8) + 1 -- 1～8の範囲を繰り返し , trunc((level - 1)/ 8) + 1 -- 1を8回、2を8回、3を8回、と繰り返し from dual connect by level <= 100000000 ;
これで1億レコードを虚空の彼方から召喚できる。
connect by は階層問い合わせの構文なので本来はツリーを辿るようなSELECT文で使うもの。
levelは辿る階層の深さの指定。
詳しくは検索すればいっぱい出て来るし微妙にわかりにくいので割愛。
今回の書き方で言えばとにかく level で指定した分のレコードがどこからともなくわいてくるみたいな動きになる。
その他のポイント
データをDBに入れるときにネックになるのは当然I/O。
いろいろ無駄な読み書きや処理を省けばその分高速になる。
ぶっちゃけ何も考えずにやると1日かかっても終わらないデータロードが、ちゃんと対策してやれば10分で終わったりするので舐めない方が良い。
マジで。
インデックスとか制約とか全部外す
これがあると無茶苦茶遅いのでとにかく全部外すか無効かする。
ロードが終わってから create index ～ parallel nologging とオプションを付けて再作成してやれば良い。
ただし作り終わった後に noparallel & logging にしてやるのを忘れるとつらいので絶対直すこと。NOARCHIVEモードにする
検証環境やリリース前の環境ならアーカイブログなんかいらないのでOFFにしちゃう。テーブルを nologging にする
ロードするテーブルの属性を alter table ～ nologging で変更しておく。
あわせてNOARCHIVEモードになっている必要があるのでオプションだけ変えて満足しないこと。
これでほとんどREDOログが出なくなる。ダイレクトパスインサートする
insert /*+ append */ into test select ～ とヒントを入れてやるとバッファを経由しないI/Oになる。
めちゃめちゃ高速。
代わりにHWM以降に書き込まれる点には注意。
テスト用に真っ新なテーブルを作ってやる場合なんかは気にしなくてOK。
まとめ
とにかくSQLだけで完結する方が省エネなのでちょっとSQLで頑張ってみましょう。
テストデータの増幅みたいな割と遊べるところで変なSQL書いてみても良いんじゃないかと。
あとデータロードを高速化する方法は本当に覚えておいて損はない、というか圧倒的に速くなるのでそのうち計測の検証してみる予定。
遅延ブロッククリーンアウトを観測する①
テーマ
遅延ブロッククリーンアウトって意外とホイホイ起こることがわかったので検証。
再現方法とか実際どういう動きをしてるかとか見れたら良いかなと。
遅延ブロッククリーンアウトについては↓参照。 www.shift-the-oracle.com
ものすごーく分かりにくいが
- 通常のブロッククリーンアウトの場合
- 遅延ブロッククリーンアウトの場合
ということだと理解した。
多分あってるんじゃないかな。
方針
トランザクション中の各所でブロックダンプを取ってITLの変化を確認してみる。
検証1（通常のブロッククリーンアウト）
SQL> create tablespace test datafile '/tmp/test.dbf' size 100M --まっさらの表領域作成; Tablespace created. SQL> create table test(col char(1000)) tablespace test --適当な表作成; Table created. SQL> insert into test select '0' from dual connect by level <= 50 --多分10ブロックくらい使う; 50 rows created. SQL> commit --バッファに存在する（と思われる）うちにコミット; Commit complete. SQL> alter system flush buffer_cache --ダンプ取得用に書き出し; System altered. SQL> select extent_id ,file_id ,block_id ,blocks from dba_extents where segment_name = 'TEST'; EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ---------- 0 5 128 8 1 5 136 8 SQL> alter system dump datafile 5 block min 128 block max 144 --TEST表のブロックをダンプ; System altered.
大体内容はコメントとして書いたので各操作の詳しい説明は割愛。
思惑としては単純にDMLを実行して通常通りのブロッククリーンアウトが行われた場合にITLがどういう状態になっているか見たいだけ。
取得したブロックダンプからITLだけ抽出すると以下。
$ egrep "^ Itl|^0x0|^Block header" orcl_ora_15980.trc Block header dump: 0x01400083 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.018.0000030f 0x00c08ee9.00d6.09 --U- 7 fsc 0x0000.0011c632 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400084 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.018.0000030f 0x00c08ee9.00d6.0b --U- 7 fsc 0x0000.0011c632 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400085 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.018.0000030f 0x00c08ee9.00d6.0d --U- 7 fsc 0x0000.0011c632 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400086 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.018.0000030f 0x00c08ee9.00d6.0f --U- 7 fsc 0x0000.0011c632 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ： （略）
「Itl」はデフォルト値「2」なので「0x01-2」の2スロットで今回は「0x01」の方が使われてるわけ。
「Uba」がUNDOセグメントに対するポインタ。
「Flag」と「Scn/Fsc」はトランザクション完了のフラグとタイミングを記録してるっぽい。
ちゃんとブロッククリーンアウトされている。
検証2（遅延ブロッククリーンアウト）
コミットする前に一回ブロックをフラッシュしてバッファが溢れた状況を再現する。
(新たに表領域＆テーブルを再作成する手順は略) SQL> insert into test select '0' from dual connect by level <= 50; 50 rows created. SQL> alter system flush buffer_cache --バッファを全て書き出してクリア; System altered. SQL> commit; Commit complete. SQL> select extent_id ,file_id ,block_id ,blocks from dba_extents where segment_name = 'TEST'; EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ---------- 0 5 128 8 1 5 136 8 SQL> alter system dump datafile 5 block min 128 block max 144; System altered.
これでITLにはまだトランザクションの終了が記録されないままデータだけが更新された状態でデータファイルに書き出されているはず。
取得したブロックダンプからITLを見てみると以下。
$ egrep "^ Itl|^0x0|^Block header" orcl_ora_15940.trc Block header dump: 0x01400083 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.0f ---- 7 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400084 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.11 ---- 7 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400085 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.13 ---- 7 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400086 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.15 ---- 7 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ： （略）
「Flag」と「Scn/Fsc」が空のままなのでトランザクション中の状態と思われる。
この後SELECTを実行することで遅延ブロッククリーンアウトがされるはず。
SQL> alter system dump datafile 5 block min 128 block max 144; System altered. SQL> set autotrace traceonly SQL> select count(*) from test; Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST | 50 | 5 (0)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 41 consistent gets 14 physical reads 620 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter system flush buffer_cache; System altered. SQL> alter system dump datafile 5 block min 128 block max 144; System altered.
「redo size」が地味に620程発生している。
SELECTしているだけなので普通ならREDOが発生することはないはず。
この時点でなんらか更新がされていることが分かる。
なんとなく読み取りが多いのもUNDOセグメントを読みに言っているからじゃなかろうか。
もっと詳細に見たい場合はEVENT 10046トレースとかとってみると良いかもしれない。
取得したブロックダンプを見てみる。
Block header dump: 0x01400083 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.0f C--- 0 scn 0x0000.0011c47f 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400084 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.11 C--- 0 scn 0x0000.0011c47f 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400085 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.13 C--- 0 scn 0x0000.0011c47f 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Block header dump: 0x01400086 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.009.0000035b 0x00c00abe.00b3.15 C--- 0 scn 0x0000.0011c47f 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 ： （略）
SELECTしただけなのにITLが更新されている。
遅延ブロッククリーンアウトが発生したっぽい。やったぜ！
いまいち「Flag」列が「C」だったり「U」だったりする条件は良く分からない。
まとめ
遅延ブロッククリーンアウトの様子は確認できた。
ちなみにそもそもバッファキャッシュを経由しないダイレクトパスインサートの場合も同様にブロッククリーンアウトが遅延された状態になった。
しかもその後SELECTしてもブロッククリーンアウトされなかった 。
きっと確実に大量の遅延ブロッククリーンアウトが発生してしまうダイレクトパスインサートの場合だけ気を利かせて余計な負荷が掛からないようにしてくれてるんだと思う。
これはこれで微妙に問題がありそう。
INDEX SCANのフィルタ述語を検証する②
テーマ
前の記事から継続。
orcl.hatenablog.com
今回は索引第一列のカーディナリティを下げてみる。
準備
SQL> create table test2(col1 number not null ,col2 number not null); Table created. SQL> create index idx_test2 on test2(col1 ,col2); Index created. SQL> insert into test2 select mod(level ,10) + 1 ,1000001 - level from dual connect by level <= 1000000; 1000000 rows created. SQL> select col1 ,count(*) from test2 group by col1 order by 1; COL1 COUNT(*) ---------- ---------- 1 100000 2 100000 3 100000 4 100000 5 100000 6 100000 7 100000 8 100000 9 100000 10 100000 10 rows selected.
INSERT文が気持ち分かりにくいが要はCOL1を1～10の範囲に限定しているだけ。
検証1
とりあえず比較用に索引の第一列（COL1）全てを検索する条件。
SQL> select /*+ index(test2 idx_test2) */ count(1) from test2 where col1 >= 1; COUNT(1) ---------- 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 3780423660 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2717 (1)| 00:00:33 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST2 | 1004K| 12M| 2717 (1)| 00:00:33 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2652 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
読み取りブロック数は2652。
検証2
索引第二列（COL2）を一意に絞る条件を追加。
SQL> select /*+ index(test2 idx_test2) */ count(1) from test2 where col1 >= 1 and col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 3780423660 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2717 (1)| 00:00:33 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST2 | 21 | 546 | 2717 (1)| 00:00:33 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1 AND "COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2652 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
RANGE SCANになって読み取りブロック数は検証1のときと同じ。
やはり前回の記事同様に「COL2=1」はfilterしかしてないように見える。
accessとfilter両方に出ている場合はもう実質filterだと思えば良いのか？
検証3
ヒント句でSKIP SCANを指定。
SQL> select /*+ index_ss(test2 idx_test2) */ count(1) from test2 where col1 >= 1 and col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2609643709 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 26 | 44 (14)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX SKIP SCAN| IDX_TEST2 | 21 | 546 | 44 (14)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1 AND "COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
述語はRANGE SCANのときと変わらないが読み取りブロックが劇的に減った。
索引の前の方の列のカーディナリティが低いとある程度効率的な読み取りが出来る様子。
なんとなくSKIP SCANの動きも分かったような気がする。
まとめ
accessとfilterで重複して述語が出ている場合は
- RANGE SCANの場合は実質filterしかしていないと思って良さそう（多分）
- SKIP SCANの場合はカーディナリティによってはaccessできそう（表現怪しい）
そもそもCHAR(2)くらいの種別コードやフラグ列なんかの索引でもないとSKIP SCAN自体採用されないのかもしれない。
↓こういう記事もあるし。
www.shift-the-oracle.com
INDEX SCANのフィルタ述語を検証する①
一発目の記事から早速地味な検証。
テーマ
INDEX SCANした時にaccessとfilterが重複してることが稀に良くある。
↓こういうの。
Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1"=1 AND "COL3"=1000) filter("COL3"=1000)
accessなのかfilterなのか良く分からないので検証してみる。
そもそも再現パターンがよく分かっていないが何となく索引の第一列目の絞込み効率が良くないときに出そうな気がする。
autotraceで読み取りブロック数を比較するのが手っ取り早いかなと思う。
accessとかfilterについてはこの辺が参考になるかも。
http://use-the-index-luke.com/ja/sql/explain-plan/oracle/filter-predicates
http://www.csus4.net/d/2011/06/30/predicate/
準備
とりあえず検証用テーブル作成。
読み取りブロック数がある程度多い方が分かりやすいのでいっぱい入れる。
SQL> create table test1(col1 number not null ,col2 number not null); Table created. SQL> create index idx_test1 on test1(col1 ,col2); Index created. SQL> insert into test1 select level ,1000001 - level from dual connect by level <= 1000000; 1000000 rows created. SQL> select * from (select * from test1 order by 1) where rownum <= 10; COL1 COL2 ---------- ---------- 1 1000000 2 999999 3 999998 4 999997 5 999996 6 999995 7 999994 8 999993 9 999992 10 999991 10 rows selected.
COL1、COL2はそれぞれ適当にユニークな数値を入れる。
検証1
とりあえず索引の第一列（COL1）が全て当てはまる条件で検索する。
SQL> select /*+ index(test1 idx_test1) */ count(1) from test1 where col1 >= 1; COUNT(1) ---------- 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 3196887443 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2993 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST1 | 1164K| 14M| 2993 (1)| 00:00:36 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2924 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
これで索引全体を読み取っているはず。
このときconsistent getsの値を見るに2924ブロック。
検証2
今度は索引の第二列（COL2）で一意に絞れる条件を足してみる。
これで例の実行計画が再現できる気がする。
SQL> select /*+ index(test1 idx_test1) */ count(1) from test1 where col1 >= 1 and col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 3196887443 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2993 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST1 | 23 | 598 | 2993 (1)| 00:00:36 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1 AND "COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2924 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
思惑通りに「COL2=1」がaccessとfilter両方に出てきた。
検証1のときと同じ読み取り量なので実質filterしかしてないように見える。
少なくともaccessに出ているからといって楽観視して良いものでもないということは言えそう。
検証3
今度はヒント句を変えてSKIP SCANにしてみる。
なんとなくSKIP SCANならいける気がする。
SQL> select /*+ index_ss(test1 idx_test1) */ count(1) from test1 where col1 >= 1 and col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2617422197 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 26 | 44 (14)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | INDEX SKIP SCAN| IDX_TEST1 | 23 | 598 | 44 (14)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL1">=1 AND "COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2928 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
むしろ若干増えた。
これは一応RANGE SCANとは違う動きをしている気がする。
検証4
ついでに索引の第一列を条件に含まないパターンも試してみる。
SQL> select /*+ index(test1 idx_test1) */ count(1) from test1 where col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2208524269 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 2995 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX FULL SCAN| IDX_TEST1 | 23 | 299 | 2995 (1)| 00:00:36 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2924 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
FULL SCANなので検証1とほぼ同じ動きで予想通り。
accessも糞もない気がするがもうお作法的なものなのかもしれない。
検証5
今度は検証4と同じ条件でSKIP SCANを試してみる。
SQL> select /*+ index_ss(test1 idx_test1) */ count(1) from test1 where col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2617422197 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 4003 (1)| 00:00:49 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX SKIP SCAN| IDX_TEST1 | 23 | 299 | 4003 (1)| 00:00:49 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL2"=1) filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2928 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
検証3と同じ読み取りブロック数に見える。
というかFULL SCANと変わらなそうなのでSKIP SCANの存在意義が怪しい。
若干本題とはずれるが、もしかして索引の第一列のカーディナリティが高すぎるのでうまく第二列から索引の構造を使えてないのかも？（勘）
検証6
せっかくなのでFAST FULL SCANの試してみる。
SQL> select /*+ index_ffs(test1 idx_test1) */ count(1) from test1 where col2 = 1; COUNT(1) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2671621383 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 816 (1)| 00:00:10 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX FAST FULL SCAN| IDX_TEST1 | 23 | 299 | 816 (1)| 00:00:10 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COL2"=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2939 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
完全にfilterだけになって分かりやすくなった。
FAST FULL SCANは索引のツリー構造を無視してリーフを全部読み取るはず。
accessの余地がないのは明らかか。
まとめ
微妙。
やはり索引第一列のカーディナリティが高すぎて変化が出ていない部分があるかもしれない。
ただしaccessに出ていても実質filterしか出来てないということはありそう。
