目的
超巨大なテーブルの件数をサクッと知りたい！
最近数百億〜兆レコードを持つテーブルを触る機会がありました。
SELECT COUNT(1)しても10分以上かかる！ということが頻繁にあったため、簡単に件数を知る方法を調べていたら行き着きました。
それが「DBA_TAB_MODIFICATIONS」ディクショナリです。
DBA_TAB_MODIFICATIONSとは
表の統計情報を収集した時点から現時点までの、表に対するINSERT/UPDATE/DELETE/TRUNCATEがどの程度行われたかを保持するデータ・ディクショナリです。
SQL> desc dba_tab_modifications
名前 NULL? 型
------------------ -------- ------------------------------------------
TABLE_OWNER VARCHAR2(30) #←スキーマ名
TABLE_NAME VARCHAR2(30) #←テーブル名
PARTITION_NAME VARCHAR2(30) #←パーティション名
SUBPARTITION_NAME VARCHAR2(30) #←サブパーティション名
INSERTS NUMBER #←最後に統計が取られた以降のINSERTレコード数
UPDATES NUMBER #←最後に統計が取られた以降のUPDATEレコード数
DELETES NUMBER #←最後に統計が取られた以降のDELETEレコード数
TIMESTAMP DATE #←最後に変更された日時
TRUNCATED VARCHAR2(3) #←最後に統計が取られた以降にTRUNCATEされたか(Yes/No)
DROP_SEGMENTS NUMBER #←最後に統計が取られた以降にDROPされたセグメント数
実際にDBA_TAB_MODOFICATIONSがどう変わっていくかを見てみます
SQL> show user
ユーザーは"TEST_USER"です。
SQL> -- テーブルを作成する
SQL> create table test_table(id number(10), name varchar2(100));
表が作成されました。
SQL> -- テーブルのディクショナリ情報を見てみる（統計が取られていないことを確認）
SQL> select owner, table_name, num_rows, last_analyzed from dba_tables where owner = 'TEST_USER' and table_name = 'TEST_TABLE';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
---------- ------------- ---------- -------------------
TEST_USER TEST_TABLE
SQL> -- DBA_TAB_MODIFICATIONSを見てみる
SQL> -- 統計も取られておらず、データのDMLも行われていないため、レコードが存在しない
SQL> select count(1) from dba_tab_modifications where table_owner = 'TEST_USER' and table_name = 'TEST_TABLE';
COUNT(1)
----------
0
SQL> --1レコードINSERTする
SQL> insert into test_table values(1, 'Mike');
1行が作成されました。
SQL> commit;
SQL> --再度DBA_TAB_MODIFICATIONSを見てみるが0件
SQL> select count(1) from dba_tab_modifications where table_owner = 'TEST_USER' and table_name = 'TEST_TABLE';
COUNT(1)
----------
0
SQL> --Oracleのメモリに残っている情報をDBA_TAB_MODIFICATIONSに書き出す
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQLプロシージャが正常に完了しました。
SQL> select count(1) from dba_tab_modifications where table_owner = 'TEST_USER' and table_name = 'TEST_TABLE';
COUNT(1)
----------
1
SQL> --insertが1件となっている
SQL> select table_owner, table_name, inserts, updates, deletes, truncated, timestamp
2 from dba_tab_modifications
3 where table_owner = 'TEST_USER' and table_name = 'TEST_TABLE';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TRU TIMESTAMP
------------ ------------- ---------- ---------- ---------- --- -------------------
TEST_USER TEST_TABLE 1 0 0 NO
2019/03/04 12:24:22
SQL> --統計情報をとる
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST_USER', tabname => 'TEST_TABLE', cascade => FALSE);
PL/SQLプロシージャが正常に完了しました。
SQL> 統計情報が取られた後にDMLを実行していないためレコードがない
SQL> select table_owner, table_name, inserts, updates, deletes, truncated, timestamp
2 from dba_tab_modifications
3 where table_owner = 'TEST_USER' and table_name = 'TEST_TABLE';
レコードが選択されませんでした。
注意点
DBA_TAB_MODIFICATIONSの情報は常に更新されているわけではないため、精度の高い情報が欲しい場合には、直前にDBMS_STATS.FLUSH_DATABASE_MONITORING_INFOを実行する必要があります。
近似値程度でもよければ実行する必要はありません。
便利な使い方
データウェアハウスのような超巨大なテーブルのデータ件数を知りたい、でもSELECT COUNT(1) 〜ではとてもではないけど時間がかかりすぎる場合、DBA_TAB_MODIFICATIONSとDBA_TABLESを使うことでデータ件数をサクッと知ることができます。
例えば、368億件のレコードを持つテーブルに対して件数を取得する場合、
- SELECT COUNT(1)〜だと12分以上
- DBA_TAB_MODIFICATIONSとDBA_TABLESを使うと2秒
と非常に効率が良いことがわかります。
SQL> --テーブルの件数取得だけで12分かかる
SQL> select /*+ PARALLEL(6) */ count(1) from TEST_SCHEMA.LARGE_TABLE;
COUNT(1)
-------------
36803211715
経過: 00:12:04.73
SQL>
SQL> select t.owner, t.table_name, t.num_rows, m.inserts, m.updates, m.deletes, m.truncated, t.num_rows+m.inserts-m.deletes as num_rows_real
2 from dba_tables t
3 left outer join dba_tab_modifications m
4 on t.owner = m.table_owner
5 and t.table_name = m.table_name
6 where t.owner = 'TEST_SCHEMA'
7 and t.table_name = 'LARGE_TABLE';
OWNER TABLE_NAME NUM_ROWS INSERTS UPDATES DELETES TRU NUM_ROWS_REAL
---------------- ------------------ ------------- ---------- ---------- ---------- --- -------------
TEST_SCHEMA LARGE_TABLE 35602049044 2387593647 0 1186430976 NO 36803211715
経過: 00:00:02.49
SQL>