ablog

不器用で落着きのない技術者のメモ

SQL のみで全テーブルのレコード数を数える方法

SQL マスターの id:niwanos さんがツイートされてた URL が気になったので見てみると、Oracle ACE の明智重蔵さんが動的にSQLを作成せずに自スキーマの全テーブルのレコード数を数えるSQLを掲載されていた。

select
   table_name,
   to_number(
     extractvalue(
       xmltype(
    dbms_xmlgen.getxml('select count(*) c from '||table_name))
       ,'/ROWSET/ROW/C')) count
 from user_tables
 WHERE TABLE_NAME NOT LIKE 'BIN$%'
   and (iot_type != 'IOT_OVERFLOW' or iot_type is null)
order by table_name;
http://www.oracle.co.jp/forum/thread.jspa?threadID=35001130

では、実行してみる。

SQL>set pagesize 50000
SQL>select
   table_name,
   to_number(
     extractvalue(
       xmltype(
    dbms_xmlgen.getxml('select count(*) c from '||table_name))
       ,'/ROWSET/ROW/C')) count
 from user_tables
 WHERE TABLE_NAME NOT LIKE 'BIN$%'
   and (iot_type != 'IOT_OVERFLOW' or iot_type is null)
order by table_name;

TABLE_NAME			    COUNT
------------------------------ ----------
AQ$_INTERNET_AGENTS			4
AQ$_INTERNET_AGENT_PRIVS		3
AQ$_QUEUES			       35
AQ$_QUEUE_TABLES		       18
AQ$_SCHEDULES				0
DEF$_AQCALL				0
DEF$_AQERROR				0
DEF$_CALLDEST				0
DEF$_DEFAULTDEST			0
DEF$_DESTINATION			0
DEF$_ERROR				0
DEF$_LOB				0
DEF$_ORIGIN				0
DEF$_PROPAGATOR 			0
DEF$_PUSHED_TRANSACTIONS		0
HELP				      919
LOGMNRC_DBNAME_UID_MAP			0
LOGMNRC_GSBA				0
LOGMNRC_GSII				0
LOGMNRC_GTCS				0
LOGMNRC_GTLO				0
LOGMNRP_CTAS_PART_MAP			0
LOGMNRT_MDDL$				0
LOGMNR_AGE_SPILL$			0
LOGMNR_ATTRCOL$ 			0
LOGMNR_ATTRIBUTE$			0
LOGMNR_CCOL$				0
LOGMNR_CDEF$				0

...

おお、すごいですね。
実行環境は以下の通りです。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> !uname -a
Linux centos54.ablog.jp 2.6.18-164.el5 #1 SMP Thu Sep 3 03:33:56 EDT 2009 i686 i686 i386 GNU/Linux

以前、同じようなことをする PL/SQL(ログインユーザが所有するテーブル一覧とその行数を取得するPL/SQL - ablog)を書きましたが、こんなにゴテゴテ書かなくてもいいんですね。