Oracle中大数据量表的管理(分区表)

http://www.sina.com.cn 2006年07月26日 10:27 中国计算机报

<!-- 正文内部文字导航 : begin --><!-- 正文内部文字导航 : end -->

　　作者：摩托罗拉公司软件工程师 冯昕

　　1 简介

　　随着信息业的发展，在企业级数据库应用中，经常会有一些几十GB,上百GB的数据表。这些大数据量表的设计，维护及其备份都是数据库管理中的重点及其难点。本文就从设计<!--NEWSZW_HZH_BEGIN-->

<!--画中画广告开始-->

<!--科技新闻内页画中画开始--><!--科技新闻内页画中画结束-->

<!--画中画广告结束-->

<!-- 画中画下文字链广告（从上至下顺序为01，02，03，04文字，需加class=a01）-->

<!--F70BB90BB6BA--><!--nwy/uc/A--> <script></script><object id="ad_note" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="280" height="40" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0"> <param name="_cx" value="5080"> <param name="_cy" value="5080"> <param name="FlashVars"> <param name="Movie" value="http://image2.sina.com.cn/dy/zwyhzh/ad_note.swf"> <param name="Src" value="http://image2.sina.com.cn/dy/zwyhzh/ad_note.swf"> <param name="WMode" value="Transparent"> <param name="Play" value="-1"> <param name="Loop" value="-1"> <param name="Quality" value="High"> <param name="SAlign"> <param name="Menu" value="-1"> <param name="Base"> <param name="AllowScriptAccess"> <param name="Scale" value="ShowAll"> <param name="DeviceFont" value="0"> <param name="EmbedMovie" value="0"> <param name="BGColor" value="FFFFFF"> <param name="SWRemote"> <param name="MovieData"> <param name="SeamlessTabbing" value="1"> <param name="Profile" value="0"> <param name="ProfileAddress"> <param name="ProfilePort" value="0"> <param name="AllowNetworking" value="all"> <param name="AllowFullScreen" value="false"></object>

<!--NEWSZW_HZH_END-->、维护及其备份方面探讨一下大数据量表的管理。

　　2 设计

　　2.1 大表时效性

　　大数据量表的数据量一般来说是跟时间成正比的，时间越久,数据量越大。 在设计阶段首先要考虑这些大表的时效性。

　　通常情况，在一定的时间区间,数据的访问频度比较大，超过这个区间，数据的访问频度极小。这个时间区间根据不同的应用类型而不同，通常是几个月。超过这个时间区间的数据可以认为是历史数据，数据访问的可能性不打。在企业应用中，并不是所有的数据都需要保留在生产数据库中，对于这些历史数据，可以考虑离线存放，或者是存放在另外的数据库中，比如数据仓库等。

　　大表的时效性可以通过在表上加时间戳列来实现。

　　2.2 使用分区表

　　Oracle 8以后提供了分区表的功能，分区表可以把一个表的数据从物理和逻辑上分割成小的区域。 Oracle支持非常大的分区表，一个对象可以允许多达64000个分区。对于大表来说，使用分区表是首选方案。 分区表可以改善表的维护、备份、恢复及查询性能。

　　分区表有4种分区方式：

　　n Range Partitioning

　　n Hash Partitioning

　　n Composite Partitioning

　　n List Partitioning

　　对于有时效性的大表，可以采用按时间分区的 Range Partitioning表，例如按天分区的分区表。

　　CREATE TABLE Test(

　　DATATIME DATE NOT NULL,

　　P1 NUMBER NULL,

　　P2 NUMBER NULL,

　　P3 NUMBER NULL,

　　P4 NUMBER NULL,

　　P5 NUMBER NULL,

　　P6 NUMBER NULL,

　　P7 NUMBER NULL,

　　P8 NUMBER NULL,

　　CONSTRAINT PK_TEST PRIMARY KEY (datatime, p1,p2) USING INDEX LOCAL TABLESPACE USERINDEX

　　)

　　PARTITION BY RANGE (DATATIME)

　　(PARTITION Test_060101 VALUES LESS THAN (TO_DATE('2006-01-02','YYYY-MM-DD')),

　　(PARTITION Test_060102 VALUES LESS THAN (TO_DATE('2006-01-03','YYYY-MM-DD')),

　　……

　　);

　　对于按时间分区仍然不能满足性能需求的表， 还可以根据应用需求，使用子分区对表进一步细化。

　　应用设计中，要充分利用分区表的特性，对大表的访问要完全避免全表访问，缩小访问范围。在查询条件中，尽量使用分区的列。

　　3 维护

　　大表的维护工作比较繁琐，索引的维护，存储空间的维护，历史数据的清理等等，使用分区表可以简化大表的维护工作，但是如果表很多的话，手动的创建、删除分区也是一件很繁琐，而且容易出错的事情。

　　此章节以按天分区的分区表为例讨论大表的自动维护。

　　3.1 分区表的命名规则

　　分区表分区的命名应当按照一定的规则命名，以利于自动维护的实现。本例采用按天分区的分区表，分区的命名方式为TABLENAME_YYMMDD，例如：TEST表的2006年6月1日的分区命名为TEST _060601。

　　3.2 维护字典

　　在数据库中创建维护字典表，存放需要自动维护的分区表的信息，包括表名，schema，表的类型，数据在数据库中的保留时间等信息。

　　Table Name: H_RETENTION

　　Column Type Null? Description

　　tablename Varchar2(30) Not null 表名

　　schemaname Varchar2(30) Not null Schema

　　typeid Varchar2(20) Not null 表类型1. PARTITION2. NORMAL3. …。。

　　retention Number(3) Not null 该表的保存天数。

　　3.3 自动创建分区

　　对于按时间分区的分区表，若不能及时创建新的数据分区，会导致数据无法插入到分区表的严重后果，数据库会产生报错信息ORA-14400: inserted partition key does not map to any partition，插入失败。

　　创建分区可以手工创建，也可以根据维护字典，通过系统的任务调度来创建分区。通常是在月底创建下个月的分区。

　　自动创建分区实现如下：

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

　　Program Name:Add_Partition

　　Description:

　　创建某个用户下个月的所有分区

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

　　PROCEDURE add_partition (v_schema IN VARCHAR2)

　　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 VARCHAR2 (30);

　　v_date DATE;

　　v_days NUMBER;

　　sql_stmt VARCHAR2 (1000); -- String used to save sql statement

　　err_msg VARCHAR2 (300);

　　BEGIN

　　v_date := TRUNC (ADD_MONTHS (SYSDATE, 1), 'MM');

　　v_days :=

　　TO_NUMBER (TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, 1)), 'DD'));

　　v_cur := DBMS_SQL.open_cursor;

　　FOR v_table IN c_td_table

　　LOOP

　　v_date := TRUNC (ADD_MONTHS (SYSDATE, 1), 'MM');

　　v_partition := v_table.tablename;

　　FOR i IN 1 .. v_days

　　LOOP

　　BEGIN

　　sql_stmt :=

　　'ALTER TABLE '

　　|| v_schema

　　|| '.'

　　|| v_table.tablename

　　|| ' ADD PARTITION '

　　|| v_partition

　　|| '_'

　　|| TO_CHAR (v_date, 'YYMMDD')

　　|| ' '

　　|| 'VALUES LESS THAN (TO_DATE('''

　　|| TO_CHAR (v_date + 1, 'YYYY-MM-DD')

　　|| ''',''YYYY-MM-DD'')) ';

　　DBMS_SQL.parse (v_cur, sql_stmt, DBMS_SQL.native);

　　v_int := DBMS_SQL.EXECUTE (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 + 1;

　　END LOOP;

　　END LOOP;

　　DBMS_SQL.close_cursor (v_cur);

　　END;

　　3.4 自动删除过期分区

　　为了释放存储空间并提高大表的性能，要从数据库中删除大表中过期的历史数据。删除操作可以手工执行，也可以通过系统的任务调度来自动删除。分区表数据删除只需要删除相应的数据分区，与delete相比，有如下好处：

　　u 速度快

　　u 占用回滚表空间少

　　u 产生日志量少

　　u 释放空间

　　如果有global的索引，删除分区后需要重建索引。

　　自动删除分区实现如下：

　　当前分区表的分区情况可以通过Oracle的数据字典dba_tab_partitions获得, 然后与维护字典中的数据保留天数进行比较，删除过期的数据分区。