简介
随着信息业的发展在企业级数据库应用中经常会有一些几十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
P NUMBER NULL
P NUMBER NULL
P NUMBER NULL
P NUMBER NULL
P NUMBER NULL
P NUMBER 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))
(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)
IS
CURSOR c_td_table
IS
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;
END LOOP;
DBMS_SQLclose_cursor (v_cur);
END;
自动删除过期分区
为了释放存储空间并提高大表的性能要从数据库中删除大表中过期的历史数据删除操作可以手工执行也可以通过系统的任务调度来自动删除分区表数据删除只需要删除相应的数据分区与delete相比有如下好处
u 速度快
u 占用回滚表空间少
u 产生日志量少
u 释放空间
如果有global的索引删除分区后需要重建索引
自动删除分区实现如下
当前分区表的分区情况可以通过Oracle的数据字典dba_tab_partitions获得 然后与维护字典中的数据保留天数进行比较删除过期的数据分区
