容量管理はどうなっているか？

前回はFlashback Dropの基本的な動作を確認した。

テーブルを削除すると「リサイクルビン」という特別な場所に移動されるわけではなく、同じ表領域にセグメントとして存在しつつもエクステント情報から見えなくなるだけであることがわかった。

また、削除されたテーブルとそれに紐づくインデックスとPK制約は、BIN$で始まる名前にRenameされる。
そして、Flashback Dropによって削除前に戻されたテーブルは、元の名前に戻るが（任意の名前にすることは可能）インデックスとPK制約はBIN$で始まる名前から元に戻ることがないこともわかった。

それでは削除されたオブジェクトが「リサイクルビン」セグメントとして表領域の中に溜まっていった場合、有限な表領域はいずれ一杯になってしまうはずである。
WindowsやMacOSの場合明示的にゴミ箱を空にしないとディスク領域は解放されないが、OracleのリサイクルビンもPERGEコマンドを発行しないと解放されないのであろうか？

マニュアルをよく読めば答えは書いてあるのだが、実際に確かめてみよう。

初期状態

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

EMP表をコピーしてEMP1表を作成する

CTAS(Create Table As Select)でEMP表からEMP1表を作成する。

SQL> create table EMP1 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_SEG%
----------
        70


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


レコードが選択されませんでした。

EMP1表にインデックスを作成する

SQL> create unique index PK_EMP1 on EMP1 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_SEG%
----------
        80


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP1                          EMP1


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- -----------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


レコードが選択されませんでした。

インデックスPK_EMP1を作成したところまでを図にすると以下のようになる。

EMP1表をDropする

SQL> drop table EMP1;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_SEG%
----------
        80


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

エクステントの空き領域が初期状態と同じ40%になっているが、セグメントの空き領域が20%となっていることに注目してもらいたい。

EMP2表とインデックスを作成する

SQL> create table EMP2 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES


SQL> create unique index PK_EMP2 on EMP2 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP2                          EMP2


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

この時点で、セグメント使用率が100%に達していることがわかる。

EMP2表をDropする

ここでEMP2表を削除して表領域使用率を初期状態と同じ60%にしてみよう。

SQL> drop table EMP2;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

表領域としては40%の空きが確保できたが、セグメントは100%使用されている状態である。

この状態でさらにオブジェクトを作成してどうなるかを確認しよう。

EMP3表を作成する

ここからはオブジェクトを1つずつ作成する。

SQL> create table EMP3 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

まず、EMP1に該当するリサイクルビン・セグメントが解放され、EMP1に紐づくPK_EMP1も解放される。

これはDROPSCNの小さい、つまり先にDropされたものから解放される仕様となっているので、正確にはPK_EMP1 → EMP1の順に解放される。

20%分が解放され10%分が消費されたので、セグメントは差し引き90%使用されていることになる。

PK_EMP3を作成する

SQL> create unique index PK_EMP3 on EMP3 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

図はリサイクルビンの２つのオブジェクトを解放し、新たな２つのオブジェクトを作成したところである。

EMP4およびPK_EMP4を作成する

SQL> create table EMP4 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_EXT%
----------
        90


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90


レコードが選択されませんでした。

SQL> create unique index PK_EMP4 on EMP4 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_EXT%
----------
       100


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
PK_EMP4                          EMP4


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                80              80          100


レコードが選択されませんでした。

EMP2、PK_EMP2に該当するリサイクルビン・オブジェクトが解放され新たなオブジェクトが作成された。表領域の使用率は100%である。

ここまで、明示的なPURGE RECYCLEBINコマンドは一度も発行していない。つまりリサイクルビン・オブジェクトにより表領域中のセグメントが100%使用されていたとしても、Oracleは古いものから適宜解放し、新たなオブジェクトを作成する領域を自動的に確保する。

PK_EMP4のみ削除する

テーブルを削除するとテーブルに紐づくインデックスも自動的にリサイクルビンで管理されることはわかったが、インデックスのみ削除した場合はどうなるだろうか？

SQL> drop index PK_EMP4;

索引が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_EXT%
----------
        90


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90


レコードが選択されませんでした。

インデックスのみを削除してもリサイクルビンでは管理されない。単純に削除されて領域も解放される。

これは考えてみれば簡単なのだが、テーブルがあればインデックスは何度でも再作成可能なためである。

今日はここまで

前回のおさらい

Flashback Dropの検証を行う環境の初期状態である。

オブジェクトはテーブルとインデックス、あわせて５つ。すべて1エクステント＝8ブロック＝64KBずつを占めている。これの他に表領域の管理情報と思われる1エスクテント分を合せて480KBの領域となるので、全体（640KB）に対する使用率は60%、空き領域は40%となっている。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

EMP表をDropする。

それでは、まず最初にEMP表をDropして状況を確認してみる。

SQL> drop table EMP;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        40


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                32              80           40


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   PK_EMP          DROP       INDEX      TS_SMALL      3323577 NO     YES
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   EMP             DROP       TABLE      TS_SMALL      3323581 YES    YES

テーブルとインデックスが「BIN$」で始まる名前にRenameされているだけでなく、PK制約もRenameされていることがわかる。

しかし、リサイクルビン情報にはFK制約の情報はない。つまり削除されている。

また、USER_RECYCLEBIN.CAN_UNDROP列を見ると、テーブルは「YES」となっているのに、インデックスの方は「NO」となっている。この違いについては次の操作で明らかになるので追って解説する。

さらに、セグメント情報を見ると、Dropされたオブジェクトは依然として「TS_SMALL」表領域内に存在していることがわかる。つまり、Drop（削除）されてゴミ箱（リサイクルビン）に入るというのは概念的なことであって、物理的には同じ表領域に存在しているのである。

SYSAUX表領域などに移動されるわけではない。

EMP表をDropした状態を図に示すと以下のようになる。

参照整合性制約（FK_EMP）については、リサイクルビンで管理されることなく即時に削除されていることがわかる。

Flashback Drop実行

それでは「FLASHBACK TABLE」コマンドにより、EMP表を削除前に戻してみよう。

SQL> flashback table EMP to before drop;

フラッシュバックが完了しました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   EMP
PK_DEPT                          DEPT


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   EMP                              P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

SQL> Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10);
Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(FD.BIN$YIfQE0ilRXy+VvPcGtg1WA==$0)に反しています

エクステントが復活したために、空き領域は初期状態の40%に戻っている。

テーブルは削除前と同じ名前に戻ったが、インデックスはRenameされたままである。さらにPK制約もRenameされたままであることがわかる。つまり、USER_RECYCLEBIN.CAN_UNDROP列が「YES」のものは削除前のオブジェクト名に戻るが、「NO」のものは戻らないことを示している。

さらに、参照整合性制約は削除されたままで元に戻ることはないがPK制約は有効であるので、制約違反となるレコードをInsertしようとするとORA-0001エラーが発生する。

インデックスやPK制約はテーブルが存在すれば、削除／再作成で元の名前に戻すことは可能であるため、Flashbck Dropの中ではRenameに関する動作が行われないのかもしれない。

今回はここまで。

Oracle10gからある地味な機能だが。。。

Flashback DropはOracle10gから登場した機能だが、正直必要性を感じることはなかった。
テーブルを間違って削除してしまうようなことは通常考えられないし、自分自身そのようなことで困ったことがなかったからだ。

ところが、過去3年間に2件ものヒューマン・エラーによるテーブルの削除事例を目にした。

いずれの事例でも幸いなことにバックアップが取得されていたので、Point-in-Timeリカバリによって最終的にはリカバリに成功したのだが、うち1件は本番環境での誤操作で、極めて莫大な損害が出たと聞いた。
他の事例も最終テスト段階での誤操作だったので、リカバリまでにテストを止めざるを得ず大きな影響を与えたらしい。

両方の事例で関係者に状況をヒアリングしたのだが、いずれも「Recyclebin」初期化パラメータはデフォルトの「on」から意図的に「off」となっており、Flashback Dropは使えない状態だった。

もし、Flashback Dropが使えていたら

もし、両方の事例で「Recyclebin」パラメータをデフォルトのままとしていたら、誤って削除してしまったテーブルを簡単な操作で、しかもほんの数秒で元に戻すことができたはずである。

一度でもFlashback Dropを体験したことのある人であればその効果が計り知れないことは容易に理解できるのであるが、残念なことにそれらでは共に使えなく、何時間もかけてバックアップから戻すはめになったのだ。

なぜRecyclebinをoffにしていたのか？

結論から言えばデフォルトのままで何ら問題はないのだが、なぜ意図的に「Recyclebin 」が「off」になっていたのかその理由を聞いてみた。

• テーブルを削除しても「ゴミ箱」に残るということは、SYSAUXかどこかにある「ゴミ箱」領域に移動するのではないか？余計なI/Oが発生するのは嫌だ。
• 「ゴミ箱」がいつの間にか一杯になって無駄な領域を占有するのは嫌だ。どうせ「ゴミ」なんだからさっさと消してしまいたい。
• 新機能にはいつも泣かされてきたから、こいつもきっと悪さをするに違いない。9iまではこんな機能なかったのだから要らない。
• 今のバージョンは11gだけど、10gの時からoffにしているからoffのまま。
• そもそも、間違ってDropするなんてことはしないので、余計な機能は要らない。

マニュアルを熟読すれば全くの誤解であることがわかるのだが、一度誤解してしまうとベテランと言われるエンジニアほど頑ななままになる。

だから、デフォルトからわざわざ変更することになる。

検証してみた

「マニュアルにはこう書いてある。」と言っても誤解を解くのは難しいし、自分自身どのような仕組みで動いているのか知りたかったので検証してみることにした。

準備

容量管理をどのようにしているのかを確認したいので、640KBという小さな表領域を作成することにした。

環境は、Oracle 12c EE (12.1.0.2)for Windows(Non-PDB)を使用した。

CREATE TABLESPACE TS_SMALL
DATAFILE 'C:\APP\ORACLEHOME\ORADATA\ORA12C\TS_SMALL01.DBF' SIZE 640K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

データ作成

データはSCOTTスキーマのオブジェクトをインポートすることで簡単に作成した。

確認用スクリプト

stsck.sql

@@uext
@@useg
@@uind
@@ucon
@@usep
@@rbin

uext.sql

TTITLE LEFT '--- エクステント情報 ---'
col EXTENT_NAME for a32
col EXTENT_TYPE for a15
select
 SEGMENT_NAME EXTENT_NAME
,SEGMENT_TYPE EXTENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_EXTENTS
order by
 EXTENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_EXT%"
from USER_EXTENTS
/

useg.sql

TTITLE LEFT '--- セグメント情報 ---'
col SEGMENT_NAME for a32
col SEGMENT_TYPE for a15
select
 SEGMENT_NAME
,SEGMENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_SEGMENTS
order by
 SEGMENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_SEG%"
from
 USER_SEGMENTS
/

uind.sql

TTITLE LEFT '--- インデックス情報 ---'
col INDEX_NAME for a32
col TABLE_NAME for a32
select
 INDEX_NAME
,TABLE_NAME
from
 USER_INDEXES
order by
 INDEX_NAME
;
TTITLE OFF

ucon.sql

TTITLE LEFT '--- 制約情報 ---'
col CONSTRAINT_NAME for a32
col TABLE_NAME for a32
col INDEX_NAME for a32
select
 CONSTRAINT_NAME
,TABLE_NAME
,CONSTRAINT_TYPE
,INDEX_NAME
,STATUS
from
 USER_CONSTRAINTS
order by
 CONSTRAINT_NAME
;
TTITLE OFF

usep.sql

TTITLE LEFT '--- 表領域使用率 ---'
select * from DBA_TABLESPACE_USAGE_METRICS
where TABLESPACE_NAME = 'TS_SMALL'
/

rbin.sql

TTITLE LEFT '--- リサイクルビン情報 ---'
select
 OBJECT_NAME
,ORIGINAL_NAME
,OPERATION
,TYPE
,TS_NAME
,DROPSCN
,CAN_UNDROP
,CAN_PURGE
from
 USER_RECYCLEBIN
order by
 DROPSCN
/

初期状態

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

続く

APPROX_COUNT_DISTINCT関数とは

APPROX_COUNT_DISTINCT関数とは、重複していない値の近似カウントを集計する関数で、例えば次のような問い合わせにおいて、prod_idでグループ化したうちの重複していないcust_idのカウントを集計する。

SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

これを従来のCOUNT関数を使って書くと

SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

となる。

マニュアルからの抜粋

Oracle® Database新機能ガイド
12cリリース1 (12.1)
B71327-05

には以下の記述がある。

1.2 重複していない値の近似カウント

最適化された新しいSQL関数APPROX_COUNT_DISTINCT()により、重複していない値の近似カウントが集約されます。大量のデータの処理速度が完全集約よりも大幅に上がりますが、これは特に、重複していない値が多数含まれるデータ・セットの場合に顕著であり、完全集約結果との偏差は無視できる程度です。

現在のデータ分析では、一般的な操作で重複していない値をカウントすることが求められます。処理時間およびリソース消費を桁違いに最適化すると同時にほとんど完全な結果を提供することにより、既存の処理速度を上げ、分析の洞察力を数段高めることができます。

実際に試してみた

マニュアルに書いてある「桁違いの最適化」とは一体どんなものなのか気になったので、サンプルスキーマの「SH.SALES」表（918,843件）を題材に実際に確認してみた。

以下は、COUNT関数とAPPROX_COUNT_DISTINCT関数の結果を横に並べたものである。
（前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。）

１回目にORDER BYあり、２回目にORDER BYなしの結果をそれぞれ確認した。

なお、各測定前に共有プールとバッファ・キャッシュのフラッシュを行ってから、SQL文の実行を行っている。

00001 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00001 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                  
00002  |  2  FROM sales                                                                                               ||00002  |  2  FROM sales                                                                                               
00003  |  3  GROUP BY prod_id                                                                                         ||00003  |  3  GROUP BY prod_id                                                                                         
00004  |  4  ORDER BY prod_id;                                                                                        ||00004  |  4  ORDER BY prod_id;                                                                                        
00005  |                                                                                                              ||00005  |                                                                                                              
00006  |   PROD_ID Number of Customers                                                                                ||00006  |   PROD_ID Number of Customers                                                                                
00007  |---------- -------------------                                                                                ||00007  |---------- -------------------                                                                                
00008 L|        13                2492                                                                                ||00008 R|        13                2516                                                                                
00009 L|        14                2039                                                                                ||00009 R|        14                2030                                                                                
00010 L|        15                2122                                                                                ||00010 R|        15                2105                                                                                
00011 L|        16                2384                                                                                ||00011 R|        16                2367                                                                                
00012 L|        17                2100                                                                                ||00012 R|        17                2093                                                                                
00013 L|        18                3028                                                                                ||00013 R|        18                2975                                                                                
00014 L|        19                2617                                                                                ||00014 R|        19                2630                                                                                
00015 L|        20                3795                                                                                ||00015 R|        20                3791                                                                                
00016 L|        21                2334                                                                                ||00016 R|        21                2365                                                                                
00017 L|        22                1416                                                                                ||00017 R|        22                1408                                                                                
00018 L|        23                5183                                                                                ||00018 R|        23                5104                                                                                
00019  |                                                                                                              ||00019  |                                                                                                              
00020  |   PROD_ID Number of Customers                                                                                ||00020  |   PROD_ID Number of Customers                                                                                
00021  |---------- -------------------                                                                                ||00021  |---------- -------------------                                                                                
00022 L|        24                4817                                                                                ||00022 R|        24                4754                                                                                
00023 L|        25                5068                                                                                ||00023 R|        25                4991                                                                                
00024 L|        26                4948                                                                                ||00024 R|        26                4894                                                                                
00025 L|        27                3801                                                                                ||00025 R|        27                3726                                                                                
00026 L|        28                4572                                                                                ||00026 R|        28                4537                                                                                
00027 L|        29                2295                                                                                ||00027 R|        29                2305                                                                                
00028 L|        30                6154                                                                                ||00028 R|        30                6134                                                                                
00029 L|        31                5586                                                                                ||00029 R|        31                5463                                                                                
00030 L|        32                4100                                                                                ||00030 R|        32                4025                                                                                
00031 L|        33                5389                                                                                ||00031 R|        33                5278                                                                                
00032 L|        34                4192                                                                                ||00032 R|        34                4142                                                                                
00033  |                                                                                                              ||00033  |                                                                                                              
00034  |   PROD_ID Number of Customers                                                                                ||00034  |   PROD_ID Number of Customers                                                                                
00035  |---------- -------------------                                                                                ||00035  |---------- -------------------                                                                                
00036 L|        35                4965                                                                                ||00036 R|        35                4950                                                                                
00037 L|        36                3302                                                                                ||00037 R|        36                3327                                                                                
00038 L|        37                4802                                                                                ||00038 R|        37                4735                                                                                
00039 L|        38                3395                                                                                ||00039 R|        38                3452                                                                                
00040 L|        39                3433                                                                                ||00040 R|        39                3404                                                                                
00041 L|        40                5972                                                                                ||00041 R|        40                5858                                                                                
00042 L|        41                3738                                                                                ||00042 R|        41                3759                                                                                
00043 L|        42                3951                                                                                ||00043 R|        42                3938                                                                                
00044 L|        43                3016                                                                                ||00044 R|        43                3056                                                                                
00045 L|        44                2571                                                                                ||00045 R|        44                2587                                                                                
00046 L|        45                3549                                                                                ||00046 R|        45                3499                                                                                
00047  |                                                                                                              ||00047  |                                                                                                              
00048  |   PROD_ID Number of Customers                                                                                ||00048  |   PROD_ID Number of Customers                                                                                
00049  |---------- -------------------                                                                                ||00049  |---------- -------------------                                                                                
00050 L|        46                4107                                                                                ||00050 R|        46                4054                                                                                
00051 L|        47                3198                                                                                ||00051 R|        47                3176                                                                                
00052 L|        48                6010                                                                                ||00052 R|        48                5883                                                                                
00053 L|       113                4640                                                                                ||00053 R|       113                4598                                                                                
00054 L|       114                4230                                                                                ||00054 R|       114                4183                                                                                
00055 L|       115                3847                                                                                ||00055 R|       115                3832                                                                                
00056 L|       116                4929                                                                                ||00056 R|       116                4924                                                                                
00057 L|       117                4672                                                                                ||00057 R|       117                4632                                                                                
00058 L|       118                4214                                                                                ||00058 R|       118                4200                                                                                
00059 L|       119                4898                                                                                ||00059 R|       119                4858                                                                                
00060 L|       120                5224                                                                                ||00060 R|       120                5135                                                                                
00061  |                                                                                                              ||00061  |                                                                                                              
00062  |   PROD_ID Number of Customers                                                                                ||00062  |   PROD_ID Number of Customers                                                                                
00063  |---------- -------------------                                                                                ||00063  |---------- -------------------                                                                                
00064 L|       121                3517                                                                                ||00064 R|       121                3499                                                                                
00065 L|       122                1572                                                                                ||00065 R|       122                1559                                                                                
00066 L|       123                4491                                                                                ||00066 R|       123                4441                                                                                
00067 L|       124                4309                                                                                ||00067 R|       124                4310                                                                                
00068 L|       125                4679                                                                                ||00068 R|       125                4650                                                                                
00069 L|       126                4253                                                                                ||00069 R|       126                4202                                                                                
00070 L|       127                4757                                                                                ||00070 R|       127                4710                                                                                
00071 L|       128                5196                                                                                ||00071 R|       128                5135                                                                                
00072 L|       129                2425                                                                                ||00072 R|       129                2419                                                                                
00073 L|       130                5428                                                                                ||00073 R|       130                5336                                                                                
00074 L|       131                5013                                                                                ||00074 R|       131                4907                                                                                
00075  |                                                                                                              ||00075  |                                                                                                              
00076  |   PROD_ID Number of Customers                                                                                ||00076  |   PROD_ID Number of Customers                                                                                
00077  |---------- -------------------                                                                                ||00077  |---------- -------------------                                                                                
00078 L|       132                4770                                                                                ||00078 R|       132                4688                                                                                
00079 L|       133                5201                                                                                ||00079 R|       133                5114                                                                                
00080 L|       134                3629                                                                                ||00080 R|       134                3594                                                                                
00081 L|       135                3996                                                                                ||00081 R|       135                3943                                                                                
00082 L|       136                 397                                                                                ||00082 R|       136                 395                                                                                
00083 L|       137                3993                                                                                ||00083 R|       137                3977                                                                                
00084 L|       138                2763                                                                                ||00084 R|       138                2716                                                                                
00085 L|       139                4217                                                                                ||00085 R|       139                4106                                                                                
00086 L|       140                5097                                                                                ||00086 R|       140                5032                                                                                
00087 L|       141                3546                                                                                ||00087 R|       141                3499                                                                                
00088 L|       142                3136                                                                                ||00088 R|       142                3071                                                                                
00089  |                                                                                                              ||00089  |                                                                                                              
00090  |   PROD_ID Number of Customers                                                                                ||00090  |   PROD_ID Number of Customers                                                                                
00091  |---------- -------------------                                                                                ||00091  |---------- -------------------                                                                                
00092 L|       143                2375                                                                                ||00092 R|       143                2395                                                                                
00093 L|       144                2289                                                                                ||00093 R|       144                2254                                                                                
00094 L|       145                2814                                                                                ||00094 R|       145                2807                                                                                
00095 L|       146                4796                                                                                ||00095 R|       146                4716                                                                                
00096 L|       147                3050                                                                                ||00096 R|       147                3073                                                                                
00097 L|       148                5150                                                                                ||00097 R|       148                5049                                                                                
00098  |                                                                                                              ||00098  |                                                                                                              
00099  |72行が選択されました。                                                                                        ||00099  |72行が選択されました。                                                                                        
00100  |                                                                                                              ||00100  |                                                                                                              
00101 L|経過: 00:00:01.27                                                                                             ||00101 R|経過: 00:00:00.92                                                                                             
00102  |                                                                                                              ||00102  |                                                                                                              
00103  |実行計画                                                                                                      ||00103  |実行計画                                                                                                      
00104  |----------------------------------------------------------                                                    ||00104  |----------------------------------------------------------                                                    
00105 L|Plan hash value: 932069919                                                                                    ||00105 R|Plan hash value: 4109827725                                                                                   
00106  |                                                                                                              ||00106  |                                                                                                              
00107 L|------------------------------------------------------------------------------------------------------------  ||00107 R|----------------------------------------------------------------------------------------------                
00108 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00108 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                
00109 L|------------------------------------------------------------------------------------------------------------  ||00109 R|----------------------------------------------------------------------------------------------                
00110 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00110 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00111 L||   1 |  SORT GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00111 R||   1 |  SORT GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00112 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00112 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00113 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00113 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00114 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00114 R|----------------------------------------------------------------------------------------------                
00115 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                              
00116 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                              
00117  |                                                                                                              ||00115  |                                                                                                              
00118  |                                                                                                              ||00116  |                                                                                                              
00119  |統計                                                                                                          ||00117  |統計                                                                                                          
00120  |----------------------------------------------------------                                                    ||00118  |----------------------------------------------------------                                                    
00121 L|       1936  recursive calls                                                                                  ||00119 R|       1956  recursive calls                                                                                  
00122  |          0  db block gets                                                                                    ||00120  |          0  db block gets                                                                                    
00123 L|       4914  consistent gets                                                                                  ||00121 R|       4921  consistent gets                                                                                  
00124 L|       1772  physical reads                                                                                   ||00122 R|       1729  physical reads                                                                                   
00125  |          0  redo size                                                                                        ||00123  |          0  redo size                                                                                        
00126 L|       2133  bytes sent via SQL*Net to client                                                                 ||00124 R|       2134  bytes sent via SQL*Net to client                                                                 
00127  |        595  bytes received via SQL*Net from client                                                           ||00125  |        595  bytes received via SQL*Net from client                                                           
00128  |          6  SQL*Net roundtrips to/from client                                                                ||00126  |          6  SQL*Net roundtrips to/from client                                                                
00129  |        136  sorts (memory)                                                                                   ||00127  |        136  sorts (memory)                                                                                   
00130  |          0  sorts (disk)                                                                                     ||00128  |          0  sorts (disk)                                                                                     
00131  |         72  rows processed                                                                                   ||00129  |         72  rows processed                                                                                   
00132  |                                                                                                              ||00130  |                                                                                                              
00133  |                                                                                                              ||00131  |                                                                                                              
00134 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00132 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                  
00135  |  2  FROM sales                                                                                               ||00133  |  2  FROM sales                                                                                               
00136  |  3  GROUP BY prod_id;                                                                                        ||00134  |  3  GROUP BY prod_id;                                                                                        
00137  |                                                                                                              ||00135  |                                                                                                              
00138  |   PROD_ID Number of Customers                                                                                ||00136  |   PROD_ID Number of Customers                                                                                
00139  |---------- -------------------                                                                                ||00137  |---------- -------------------                                                                                
00140 L|        22                1416                                                                                ||00138 R|        22                1408                                                                                
00141 L|        25                5068                                                                                ||00139 R|        25                4991                                                                                
00142 L|        30                6154                                                                                ||00140 R|        30                6134                                                                                
00143 L|        34                4192                                                                                ||00141 R|        34                4142                                                                                
00144 L|        42                3951                                                                                ||00142 R|        42                3938                                                                                
00145 L|        43                3016                                                                                ||00143 R|        43                3056                                                                                
00146 L|       123                4491                                                                                ||00144 R|       123                4441                                                                                
00147 L|       129                2425                                                                                ||00145 R|       129                2419                                                                                
00148 L|       138                2763                                                                                ||00146 R|       138                2716                                                                                
00149 L|        13                2492                                                                                ||00147 R|        13                2516                                                                                
00150 L|        28                4572                                                                                ||00148 R|        28                4537                                                                                
00151  |                                                                                                              ||00149  |                                                                                                              
00152  |   PROD_ID Number of Customers                                                                                ||00150  |   PROD_ID Number of Customers                                                                                
00153  |---------- -------------------                                                                                ||00151  |---------- -------------------                                                                                
00154 L|        29                2295                                                                                ||00152 R|        29                2305                                                                                
00155 L|        44                2571                                                                                ||00153 R|        44                2587                                                                                
00156 L|        47                3198                                                                                ||00154 R|        47                3176                                                                                
00157 L|       113                4640                                                                                ||00155 R|       113                4598                                                                                
00158 L|       116                4929                                                                                ||00156 R|       116                4924                                                                                
00159 L|       120                5224                                                                                ||00157 R|       120                5135                                                                                
00160 L|       128                5196                                                                                ||00158 R|       128                5135                                                                                
00161 L|       147                3050                                                                                ||00159 R|       147                3073                                                                                
00162 L|       121                3517                                                                                ||00160 R|       121                3499                                                                                
00163 L|       134                3629                                                                                ||00161 R|       134                3594                                                                                
00164 L|       144                2289                                                                                ||00162 R|       144                2254                                                                                
00165  |                                                                                                              ||00163  |                                                                                                              
00166  |   PROD_ID Number of Customers                                                                                ||00164  |   PROD_ID Number of Customers                                                                                
00167  |---------- -------------------                                                                                ||00165  |---------- -------------------                                                                                
00168 L|        14                2039                                                                                ||00166 R|        14                2030                                                                                
00169 L|        20                3795                                                                                ||00167 R|        20                3791                                                                                
00170 L|        21                2334                                                                                ||00168 R|        21                2365                                                                                
00171 L|        26                4948                                                                                ||00169 R|        26                4894                                                                                
00172 L|        31                5586                                                                                ||00170 R|        31                5463                                                                                
00173 L|       132                4770                                                                                ||00171 R|       132                4688                                                                                
00174 L|       148                5150                                                                                ||00172 R|       148                5049                                                                                
00175 L|       141                3546                                                                                ||00173 R|       141                3499                                                                                
00176 L|        24                4817                                                                                ||00174 R|        24                4754                                                                                
00177 L|        32                4100                                                                                ||00175 R|        32                4025                                                                                
00178 L|        46                4107                                                                                ||00176 R|        46                4054                                                                                
00179  |                                                                                                              ||00177  |                                                                                                              
00180  |   PROD_ID Number of Customers                                                                                ||00178  |   PROD_ID Number of Customers                                                                                
00181  |---------- -------------------                                                                                ||00179  |---------- -------------------                                                                                
00182 L|       117                4672                                                                                ||00180 R|       117                4632                                                                                
00183 L|       119                4898                                                                                ||00181 R|       119                4858                                                                                
00184 L|       133                5201                                                                                ||00182 R|       133                5114                                                                                
00185 L|       137                3993                                                                                ||00183 R|       137                3977                                                                                
00186 L|       143                2375                                                                                ||00184 R|       143                2395                                                                                
00187 L|        17                2100                                                                                ||00185 R|        17                2093                                                                                
00188 L|        23                5183                                                                                ||00186 R|        23                5104                                                                                
00189 L|        35                4965                                                                                ||00187 R|        35                4950                                                                                
00190 L|        37                4802                                                                                ||00188 R|        37                4735                                                                                
00191 L|        38                3395                                                                                ||00189 R|        38                3452                                                                                
00192 L|        48                6010                                                                                ||00190 R|        48                5883                                                                                
00193  |                                                                                                              ||00191  |                                                                                                              
00194  |   PROD_ID Number of Customers                                                                                ||00192  |   PROD_ID Number of Customers                                                                                
00195  |---------- -------------------                                                                                ||00193  |---------- -------------------                                                                                
00196 L|       125                4679                                                                                ||00194 R|       125                4650                                                                                
00197 L|       135                3996                                                                                ||00195 R|       135                3943                                                                                
00198 L|        33                5389                                                                                ||00196 R|        33                5278                                                                                
00199 L|        40                5972                                                                                ||00197 R|        40                5858                                                                                
00200 L|        41                3738                                                                                ||00198 R|        41                3759                                                                                
00201 L|        45                3549                                                                                ||00199 R|        45                3499                                                                                
00202 L|       114                4230                                                                                ||00200 R|       114                4183                                                                                
00203 L|       130                5428                                                                                ||00201 R|       130                5336                                                                                
00204 L|       131                5013                                                                                ||00202 R|       131                4907                                                                                
00205 L|       136                 397                                                                                ||00203 R|       136                 395                                                                                
00206 L|       140                5097                                                                                ||00204 R|       140                5032                                                                                
00207  |                                                                                                              ||00205  |                                                                                                              
00208  |   PROD_ID Number of Customers                                                                                ||00206  |   PROD_ID Number of Customers                                                                                
00209  |---------- -------------------                                                                                ||00207  |---------- -------------------                                                                                
00210 L|        18                3028                                                                                ||00208 R|        18                2975                                                                                
00211 L|        27                3801                                                                                ||00209 R|        27                3726                                                                                
00212 L|        36                3302                                                                                ||00210 R|        36                3327                                                                                
00213 L|       115                3847                                                                                ||00211 R|       115                3832                                                                                
00214 L|       146                4796                                                                                ||00212 R|       146                4716                                                                                
00215 L|       124                4309                                                                                ||00213 R|       124                4310                                                                                
00216 L|       142                3136                                                                                ||00214 R|       142                3071                                                                                
00217 L|       145                2814                                                                                ||00215 R|       145                2807                                                                                
00218 L|        15                2122                                                                                ||00216 R|        15                2105                                                                                
00219 L|        19                2617                                                                                ||00217 R|        19                2630                                                                                
00220 L|        39                3433                                                                                ||00218 R|        39                3404                                                                                
00221  |                                                                                                              ||00219  |                                                                                                              
00222  |   PROD_ID Number of Customers                                                                                ||00220  |   PROD_ID Number of Customers                                                                                
00223  |---------- -------------------                                                                                ||00221  |---------- -------------------                                                                                
00224 L|       118                4214                                                                                ||00222 R|       118                4200                                                                                
00225 L|       126                4253                                                                                ||00223 R|       126                4202                                                                                
00226 L|       127                4757                                                                                ||00224 R|       127                4710                                                                                
00227 L|        16                2384                                                                                ||00225 R|        16                2367                                                                                
00228 L|       122                1572                                                                                ||00226 R|       122                1559                                                                                
00229 L|       139                4217                                                                                ||00227 R|       139                4106                                                                                
00230  |                                                                                                              ||00228  |                                                                                                              
00231  |72行が選択されました。                                                                                        ||00229  |72行が選択されました。                                                                                        
00232  |                                                                                                              ||00230  |                                                                                                              
00233 L|経過: 00:00:00.90                                                                                             ||00231 R|経過: 00:00:00.73                                                                                             
00234  |                                                                                                              ||00232  |                                                                                                              
00235  |実行計画                                                                                                      ||00233  |実行計画                                                                                                      
00236  |----------------------------------------------------------                                                    ||00234  |----------------------------------------------------------                                                    
00237 L|Plan hash value: 2475333094                                                                                   ||00235 R|Plan hash value: 3604305554                                                                                   
00238  |                                                                                                              ||00236  |                                                                                                              
00239 L|------------------------------------------------------------------------------------------------------------  ||00237 R|----------------------------------------------------------------------------------------------                
00240 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00238 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                
00241 L|------------------------------------------------------------------------------------------------------------  ||00239 R|----------------------------------------------------------------------------------------------                
00242 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00240 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00243 L||   1 |  HASH GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00241 R||   1 |  HASH GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00244 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00242 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00245 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00243 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00246 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00244 R|----------------------------------------------------------------------------------------------                
00247 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                              
00248 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                              
00249  |                                                                                                              ||00245  |                                                                                                              
00250  |                                                                                                              ||00246  |                                                                                                              
00251  |統計                                                                                                          ||00247  |統計                                                                                                          
00252  |----------------------------------------------------------                                                    ||00248  |----------------------------------------------------------                                                    
00253 L|       2432  recursive calls                                                                                  ||00249 R|       2313  recursive calls                                                                                  
00254  |          0  db block gets                                                                                    ||00250  |          0  db block gets                                                                                    
00255 L|       5669  consistent gets                                                                                  ||00251 R|       5458  consistent gets                                                                                  
00256 L|       1794  physical reads                                                                                   ||00252 R|       1765  physical reads                                                                                   
00257  |          0  redo size                                                                                        ||00253  |          0  redo size                                                                                        
00258 L|       2133  bytes sent via SQL*Net to client                                                                 ||00254 R|       2134  bytes sent via SQL*Net to client                                                                 
00259  |        595  bytes received via SQL*Net from client                                                           ||00255  |        595  bytes received via SQL*Net from client                                                           
00260  |          6  SQL*Net roundtrips to/from client                                                                ||00256  |          6  SQL*Net roundtrips to/from client                                                                
00261 L|        239  sorts (memory)                                                                                   ||00257 R|        229  sorts (memory)                                                                                   
00262  |          0  sorts (disk)                                                                                     ||00258  |          0  sorts (disk)                                                                                     
00263  |         72  rows processed                                                                                   ||00259  |         72  rows processed                                                                                   
00264 L|                                                                                                              ||       |                                                                                                              

何が違うのか？

行番号の後に「L」あるいは「R」がついている行は、左右の結果が異なるものを示している。

近似値というからには両者はほとんど同じ結果でたまに違う値となることを予想していたのだが、あにはからんや（72行という結果は同じだが）すべての行で異なるカウント値となった。

また、統計情報はほとんど同じだが、実行計画が大きく異なっている。

COUNT関数の場合「PARTITION RANGE ALL」で全行を取得した後、「HASH GROUP BY」で約35万9千行のビユーを作成（その際17MBのHASH AREAを使用）し、さらに「SORT GROUP BY」で72行に絞り込んでいる。

一方、APPROX_COUNT_DISTINCT関数の場合全行を取得するまでは同じだが、「SORT GROUP BY APPROX」という新たなオペレーションによって一気に72行まで絞り込んでいる。その際コストの上積み分もほとんどない。（514→538）

今回の場合、テーブルサイズがそれほど大きくないので、実行時間で大きな違いはなかったが、巨大なテーブルの場合は「HASH GROUP BY」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。

正確なカウントは必要なのか？

検索条件を入力して「◯件ヒットしました。」と表示させるアプリケーションは多いが、その値の正確性を１桁単位で求めてもあまり意味のないことが多い。

チューニングの現場ではCOUNT関数の負荷が問題となっていることが多いので、このAPPROX_COUNT_DISTINCT関数は意外と使えるかもしれない。