2020年5月16日 星期六

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

以下將讀取系統資料表以取得資料表之筆數及所使用空間但深受資料庫統計值更新時間而影響正確性如欲提高精準度則可請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
可查詢自身所建立以及授權存取等物件相關資料
2
取得db_block_size
ALL_TABLES即可取得可讀取資料表(自建或授權)再由v$parameterdb_block_size參數即可計算出所使用空間執行結果如下圖:
統計值搜集可使用指定單一資料表(Table)或擁有者(Owner)進行指令則分別為dbms_stats.gather_table_statsdbms_stats.gather_schema_stats將指定搜集特定資料表統計值為例當執行完成相關統計值將變異動可判讀LAST_ANALYZED最後分析時間
exec dbms_stats.gather_table_stats('owner', 'table_name' );



ü   MSSQL
#
系統表格
說明
1
資料表
2
資料分割任何資料表索引等物件(不包含全文檢索)均各別分佈在一個或以上partition中。
3
儲存配置單位
如需取得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
資料分割所屬物件(TableView)識別碼。 
index_id
int
資料分割所屬物件中參照至sys.indexes識別碼。
0:資料表(sys.indexes.nameNULL); 1:叢集索引;
2 或以上:非叢集索引
hobt_id
bigint
表示資料 HoBT的識別碼。
rows
bigint
所屬物件(TableView)近似資料筆數

sys.allocation_units
資料行名稱
資料類型
描述
allocation_unit_id
bigint
配置單位的識別碼。 在資料庫中,這是唯一的。
type
tinyint
配置單位類型(0:已卸除;1:同資料列資料;2:大型物件)
container_id
bigint
與配置單位相關聯的儲存體容器識別碼。
l   Type: 13
container_id = sys.partitions.hobt_id
l   type: 2
container_id = sys.partitions.partition_id
l   0:延遲卸除的配置單位
total_pages
bigint
所配置或保留的總頁數。
used_pages
bigint
實際使用的總頁數。
請注意: 依文件上所述與sys.partitions連結時應依據儲存配置單位類型而定以大部份情況應使用hobt_id但就觀察hobt_id/ partition_id之資料內容為相同(後續應再測試)

參考資料:


1 則留言: