Help us understand the problem. What is going on with this article?

SELECT文のSAMPLE句を試す(Oracle Database 19c)

テーブルのサンプリング検索

 大規模なテーブルから一定のサンプルを取得するためには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未満)で指定してください。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away