SQL QnA: 取得資料表之筆數及所用空間

以下將讀取系統資料表以取得資料表之筆數及所使用空間，但深受資料庫統計值更新時間而影響正確性，如欲提高精準度則可請DBA先進行統計值更新，或者逐一下SELECT COUNT(*) FROM Table_Name以取得筆數，但需以FULL TABLE SCAN方式進行，執行效不佳。以下僅針對簡便使用之系統資料表方式說明。

	SQL
ORACLE	SELECT OWNER , TABLE_NAME , NUM_ROWS , BLOCKS * (SELECT value --由參數檔中取得db_block_size FROM v$parameter WHERE NAME = 'db_block_size' ) /1024/1024 Total_MB , LAST_ANALYZED FROM ALL_TABLES WHERE 1=1 AND OWNER NOT IN ('SYS','SYSTEM') ORDER BY NUM_ROWS DESC NULLS LAST 註: 由於部分Table之筆數為為空，因此使用NULLS LAST關鍵字將NULL置於最後，後續將撰文說明特殊排序法。
MSSQL	SELECT OBJECT_SCHEMA_NAME(pat.object_id) AS Owner , OBJECT_NAME(pat.object_id) AS Table_Name , pat.rows AS Num_Rows , CAST((SUM(au.total_pages)8) / 1024.0 AS numeric(12, 2)) AS Total_MB , CAST((SUM(au.used_pages)8) / 1024.0 AS numeric(12, 2)) AS Used_MB , CAST((SUM(au.total_pages)8) / 1024.0 AS numeric(12, 2)) - CAST((SUM(au.used_pages)8) / 1024.0 AS numeric(12, 2)) AS Unused_MB FROM sys.partitions pat INNER JOIN sys.allocation_units au ON pat.partition_id = au.container_id WHERE 1=1 --AND pat.object_id = OBJECT_ID('dbo.Log_Info') --指定資料表 GROUP BY pat.object_id , pat.rows ORDER BY 3 DESC , Table_Name

ü ORACLE

#	系統View	說明
1	ALL_TABLES	可查詢自身所建立以及授權存取等物件相關資料
2	v$parameter	取得db_block_size

由ALL_TABLES即可取得可讀取資料表(自建或授權)，再由v$parameter之db_block_size參數即可計算出所使用空間。執行結果如下圖:

統計值搜集可使用指定單一資料表(Table)或擁有者(Owner)進行，指令則分別為dbms_stats.gather_table_stats及dbms_stats.gather_schema_stats，將指定搜集特定資料表統計值為例，當執行完成相關統計值將變異動，可判讀LAST_ANALYZED最後分析時間。

exec dbms_stats.gather_table_stats('owner', 'table_name' );

ü MSSQL

#	系統表格	說明
1	sys.tables	資料表
2	sys.partitions	資料分割，任何資料表及索引等物件(不包含全文檢索)均各別分佈在一個或以上partition中。
3	sys.allocation_units	儲存配置單位

如需取得MSSQL資料筆數、空間使用之資訊，參考官方文件說明及驗證，可讀取sys.partitions(資料分割)及sys.allocation_units(儲存配置單位)兩者即可，執行結果如下所示。

可使用DBCC UpdateUsage指令更新頁面及資料筆數統計值，由於重新搜集需耗用許多資源，需審慎使用，語法如下。

DBCC UpdateUsage('db_Name' [, 'table_name']) WITH COUNT_ROWS

系統資料表對應關係整理如下表所示，另外也節錄整理官網中資訊整理如下。

Object	Relation	Object
sys.tables	1:N	sys.partitions
sys.partitions	1:1	sys.allocation_units

sys.partitions

資料行名稱	資料類型	描述
partition_id	bigint	資料分割識別碼(資料庫層級)。
object_id	int	此資料分割所屬物件(Table、View)識別碼。
index_id	int	此資料分割所屬物件中參照至sys.indexes識別碼。 0:資料表(sys.indexes.name為NULL); 1:叢集索引; 2 或以上:非叢集索引
hobt_id	bigint	表示資料表或 HoBT的識別碼。
rows	bigint	所屬物件(Table、View)近似資料筆數。

sys.allocation_units

資料行名稱	資料類型	描述
allocation_unit_id	bigint	配置單位的識別碼。在資料庫中，這是唯一的。
type	tinyint	配置單位類型(0:已卸除;1:同資料列資料;2:大型物件)
container_id	bigint	與配置單位相關聯的儲存體容器識別碼。 l Type: 1、3 container_id = sys.partitions.hobt_id。 l type: 2 container_id = sys.partitions.partition_id。 l 0:延遲卸除的配置單位
total_pages	bigint	所配置或保留的總頁數。
used_pages	bigint	實際使用的總頁數。