テーブルのサンプリング検索
大規模なテーブルから一定のサンプルを取得するためにはSELECT文のテーブル名にSAMPLE句を指定します。SAMPLE句には取得レコード割合またはSAMPLE BLOCK句を使って取得ブロック割合を指定します。以下は100万レコード格納されたテーブルから1%取得するSELECT文です。実行計画にINDEX SAMPLE FAST FULL SCANが出力されています。
SQL> SELECT COUNT(*) FROM data1 SAMPLE(1);
COUNT(*)
----------
9870
実行計画
----------------------------------------------------------
Plan hash value: 2698143828
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 571 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | INDEX SAMPLE FAST FULL SCAN| SYS_C0011721 | 10000 | 107K| 571 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
2098 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL*Net to client
398 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ブロック単位でサンプリング割合を指定することもできます。
SQL> SELECT COUNT(*) FROM data1 SAMPLE BLOCK(1);
COUNT(*)
----------
0
実行計画
----------------------------------------------------------
Plan hash value: 2698143828
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | INDEX SAMPLE FAST FULL SCAN| SYS_C0011721 | 10000 | 107K| 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
404 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の割合がとても小さいことがわかります。SAMPLE指定の場合は約2000ブロック読んでいます。この値はINDEX FAST FULL SCANを行っているインデックス全体とほぼ一致します。一方でSAMPLE BLOCK指定の場合は11ブロックのみ取得しているためシステム負荷が小さいことがわかります。
しかしBLOCKサンプリングが行われるためには制約があります。マニュアルには以下の記述があります。
ブロック・サンプリングは、全表スキャン中または高速全索引スキャン中にのみ使用可能です。より効率的な実行パスが存在する場合、ブロック・サンプリングは実行されません。特定の表または索引に対するブロック・サンプリングを確実に実行する場合は、FULLまたはINDEX_FFSのヒントを使用します。
INDEX RANGE SCANが行わる場合にBLOCK句は無視されます。
SQL> SELECT COUNT(*) FROM data1 SAMPLE BLOCK(1) WHERE c2='UPDATE';
COUNT(*)
----------
0
実行計画
----------------------------------------------------------
Plan hash value: 674498903
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX RANGE SCAN| IDX1_DATA1 | 1 | 11 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"='UPDATE')
filter(ORA_HASH(ROWID,0,397908702,'SYS_SAMPLE_BLOCK',0)<42949673)
統計
----------------------------------------------------------
9 recursive calls
6 db block gets
5 consistent gets
0 physical reads
1044 redo size
572 bytes sent via SQL*Net to client
422 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
FULLヒントを指定すると実行計画にTABLE ACCESS SAMPLEが表示され、サンプリングが行われることが確認できます。
SQL> SELECT /*+ FULL(data1) */ COUNT(*) FROM data1 SAMPLE BLOCK(1) WHERE c2='UPDATE';
COUNT(*)
----------
0
実行計画
----------------------------------------------------------
Plan hash value: 4179533279
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS SAMPLE| DATA1 | 1 | 11 | 8 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"='UPDATE')
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
サンプル割合
サンプル割合として指定できるのはマニュアル上は「0.000001以上100未満」になっています。0.000001以上はわかりますが、100未満という値はどこまで大きくできるのか確認してみました。99.99999998835846以上になるとエラーORA-30562が発生することがわかりました。
SQL> SELECT COUNT(*) FROM data1 SAMPLE (99.9999999883584599999999);
COUNT(*)
----------
999999
SQL> SELECT COUNT(*) FROM data1 SAMPLE (99.99999998835846);
SELECT COUNT(*) FROM data1 SAMPLE (99.99999998835846)
*
行1でエラーが発生しました。:
ORA-30562:
SAMPLEの割合(%)は、必ず範囲内(0.000001以上100未満)で指定してください。