淺談Oracle中大數據量表的管理

　　簡介

　　隨著信息業的發展在企業級數據庫應用中經常會有一些幾十GB上百GB的數據表這些大數據量表的設計維護及其備份都是數據庫管理中的重點及其難點本文就從設計維護及其備份方面探討一下大數據量表的管理

　　設計

　　大表時效性

　　大數據量表的數據量一般來說是跟時間成正比的時間越久數據量越大在設計階段首先要考慮這些大表的時效性

　　通常情況在一定的時間區間數據的訪問頻度比較大超過這個區間數據的訪問頻度極小這個時間區間根據不同的應用類型而不同通常是幾個月超過這個時間區間的數據可以認為是歷史數據數據訪問的可能性不打在企業應用中並不是所有的數據都需要保留在生產數據庫中對於這些歷史數據可以考慮離線存放或者是存放在另外的數據庫中比如數據倉庫等

　　大表的時效性可以通過在表上加時間戳列來實現

　　使用分區表

　　Oracle 以後提供了分區表的功能分區表可以把一個表的數據從物理和邏輯上分割成小的區域 Oracle支持非常大的分區表一個對象可以允許多達個分區對於大表來說使用分區表是首選方案分區表可以改善表的維護備份恢復及查詢性能

　　分區表有種分區方式

　　n Range Partitioning

　　n Hash Partitioning

　　n Composite Partitioning

　　n List Partitioning

　　對於有時效性的大表可以采用按時間分區的 Range Partitioning表例如按天分區的分區表

　　CREATE TABLE Test(

　　DATATIME DATE NOT NULL

　　P NUMBER NULL

　　CONSTRAINT PK_TEST PRIMARY KEY (datatime pp) USING INDEX LOCAL TABLESPACE USERINDEX

　　)

　　PARTITION BY RANGE (DATATIME)

　　(PARTITION Test_ VALUES LESS THAN (TO_DATE(YYYYMMDD))

　　……

　　);

　　對於按時間分區仍然不能滿足性能需求的表還可以根據應用需求使用子分區對表進一步細化

　　應用設計中要充分利用分區表的特性對大表的訪問要完全避免全表訪問縮小訪問范圍在查詢條件中盡量使用分區的列

　　維護

　　大表的維護工作比較繁瑣索引的維護存儲空間的維護歷史數據的清理等等使用分區表可以簡化大表的維護工作但是如果表很多的話手動的創建刪除分區也是一件很繁瑣而且容易出錯的事情

　　此章節以按天分區的分區表為例討論大表的自動維護

　　分區表的命名規則

　　分區表分區的命名應當按照一定的規則命名以利於自動維護的實現本例采用按天分區的分區表分區的命名方式為TABLENAME_YYMMDD例如TEST表的年月日的分區命名為TEST _

　　維護字典

　　在數據庫中創建維護字典表存放需要自動維護的分區表的信息包括表名schema表的類型數據在數據庫中的保留時間等信息

　　Table Name: H_RETENTION

　　Column Type Null? Description

　　tablename Varchar() Not null 表名

　　schemaname Varchar() Not null Schema

　　typeid Varchar() Not null 表類型 PARTITION NORMAL …

　　retention Number() Not null 該表的保存天數

　　自動創建分區

　　對於按時間分區的分區表若不能及時創建新的數據分區會導致數據無法插入到分區表的嚴重後果數據庫會產生報錯信息ORA: inserted partition key does not map to any partition插入失敗

　　創建分區可以手工創建也可以根據維護字典通過系統的任務調度來創建分區通常是在月底創建下個月的分區

　　自動創建分區實現如下

　　/**************************************************************************

　　Program Name:Add_Partition

　　Description:

　　創建某個用戶下個月的所有分區

　　***************************************************************************/

　　PROCEDURE add_partition (v_schema IN VARCHAR)

　　CURSOR c_td_table

　　SELECT tablename

　　FROM h_retention

　　WHERE typeid ＝ PARTITION

　　AND schemaname = UPPER (v_schema)

　　ORDER BY tablename;

　　v_cur BINARY_INTEGER;

　　v_int BINARY_INTEGER;

　　v_partition VARCHAR ();

　　v_date DATE;

　　v_days NUMBER;

　　sql_stmt VARCHAR (); String used to save sql statement

　　err_msg VARCHAR ();

　　BEGIN

　　v_date := TRUNC (ADD_MONTHS (SYSDATE ) MM);

　　v_days :=

　　TO_NUMBER (TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE )) DD));

　　v_cur := DBMS_SQLopen_cursor;

　　FOR v_table IN c_td_table

　　LOOP

　　v_date := TRUNC (ADD_MONTHS (SYSDATE ) MM);

　　v_partition := v_tabletablename;

　　FOR i IN v_days

　　LOOP

　　BEGIN

　　sql_stmt :=

　　ALTER TABLE

　　|| v_schema

　　|| v_tabletablename

　　|| ADD PARTITION

　　|| v_partition

　　|| _

　　|| TO_CHAR (v_date YYMMDD)

　　|| VALUES LESS THAN (TO_DATE(

　　|| TO_CHAR (v_date + YYYYMMDD)

　　|| YYYYMMDD)) ;

　　DBMS_SQLparse (v_cur sql_stmt DBMS_SQLnative);

　　v_int := DBMS_SQLEXECUTE (v_cur);

　　EXCEPTION

　　WHEN OTHERS

　　THEN

　　err_msg :=

　　v_partition

　　|| : Create

　　|| TO_CHAR (v_date YYMMDD)

　　|| partition unsuccessfully! Error Information:

　　|| SQLERRM;

　　log_insert (err_msg); You can define your own log_insert function

　　COMMIT;

　　END;

　　v_date := v_date + ;

　　END LOOP;

　　DBMS_SQLclose_cursor (v_cur);

　　END;

　　自動刪除過期分區

　　為了釋放存儲空間並提高大表的性能要從數據庫中刪除大表中過期的歷史數據刪除操作可以手工執行也可以通過系統的任務調度來自動刪除分區表數據刪除只需要刪除相應的數據分區與delete相比有如下好處

　　u 速度快

　　u 占用回滾表空間少

　　u 產生日志量少

　　u 釋放空間

　　如果有global的索引刪除分區後需要重建索引

　　自動刪除分區實現如下

　　當前分區表的分區情況可以通過Oracle的數據字典dba_tab_partitions獲得然後與維護字典中的數據保留天數進行比較刪除過期的數據分區

From:http://tw.wingwit.com/Article/program/Oracle/201311/18275.html