Oracle中大数据表的管理

简介

随着信息产业的发展，企业数据库应用中经常会出现一些几十GB、几百GB的数据表。这些大数据表的设计、维护和备份是数据库管理中的重点和难点。本文从设计、维护、备份等方面论述了大数据表的管理。

设计

大表的时效性

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

通常在一定的时间间隔内，数据访问的频率比较大，数据访问的频率极小。这个时间间隔根据不同的应用类型而不同，通常是几个月。超过这个时间间隔的数据可以认为是历史数据。在企业应用程序中，并非所有数据都需要保存在生产数据库中。对于这些历史数据，可以考虑离线存储或者存储在另一个数据库中，比如数据仓库。

大型表的及时性可以通过在表中添加时间戳列来实现。

使用分区表

Oracle后来提供了分区表的功能，可以在物理上和逻辑上将一个表的数据划分成小区域。Oracle支持非常大的分区表，一个对象可以允许多个分区。对于大型表，使用分区表是首选，可以提高表的维护、备份、恢复和查询性能。

有两种方法对分区表进行分区。

范围划分

散列分区

复合分区

列表分割

对于大型的时间敏感表，我们可以使用按时间分区的范围分区表，比如按天分区的分区表。

创建表测试(

数据时间？约会？不为空

p号？空

使用索引本地表空间USERINDEX的约束PK_TEST主键(datatime p p)

)

按范围分区(数据时间)

(分区测试值小于(截止日期(年月日))

……

);

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

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

维持

大型表的维护比较复杂，比如索引的维护、存储空间的维护、历史数据的清理等。使用分区表可以简化大型表的维护，但是如果表很多，手动创建和删除分区也是非常复杂和容易出错的。

本章以按天分区的分区表为例，讨论大型表的自动维护。

分区表的命名规则

分区表分区的命名应该基于一定的规则，以便于实现自动维护。在这个例子中，分区表partition by day的命名方法是TABLENAME_YYMMDD。例如，测试表的年、月、日分区命名为TEST _。

维护字典

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

表名:H_RETENTION

列类型为空？描述

Tablename Varchar()不是空表名

schemaname Varchar()不是空架构

Typeid Varchar () Not null表类型分区正常…

Retention Number() Not null保存此表的天数。

自动创建分区

对于按时间划分的分区表，如果不能及时创建新的数据分区，会导致数据无法插入分区表的严重后果，数据库会产生错误信息ora: inserted partition key没有映射到任何分区。

分区可以手动创建，也可以根据维护字典通过系统任务调度创建，通常在月末创建。

自动分区创建按如下方式实现

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

程序名:添加分区

描述:

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

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

过程add _ partition(VARCHAR中的v_schema)

是

游标c_td_table

是

选择表名

从哪里？h _保留

其中，typeid =分区

schemaname = UPPER (v_schema)

按表名排序；

v _ cur BINARY _ INTEGER

v_int二进制_整数；

v _ partition VARCHAR()；

v_date？日期；

v_days？号码；

sql_stmt？VARCHAR()；？用于保存sql语句的字符串

err _ msg VARCHAR()；

开始

v _ date:= TRUNC(ADD _ MONTHS(SYSDATE)MM)；

v_days :=

TO _ NUMBER(TO _ CHAR(LAST _ DAY(ADD _ MONTHS(SYSDATE))DD))；

v _ cur:= DBMS _ SQL open _ cursor；

对于c_td_table中的v_table

环

v _ date:= TRUNC(ADD _ MONTHS(SYSDATE)MM)；

v _ partition:= v _ table tablename；

为我在v_days

环

开始

sql_stmt :=

更改表格

|| v_schema

|| v_table表名

||添加分区

| | v _分区

|| _

|| TO_CHAR (v_date YYMMDD)

||小于(截止日期(

|| TO_CHAR (v_date + YYYY MM DD)

| | YYYY MM DD))；

DBMS _ SQL parse(v _ cur SQL _ stmt DBMS _ SQL native)；

v _ int:= DBMS _ SQL EXECUTE(v _ cur)；

例外

当别人

然后

err_msg :=

v _分区

||:创建

|| TO_CHAR (v_date YYMMDD)

||分区不成功！错误信息:

| | SQLERRM

log_insert(错误消息)；？您可以定义自己的log_insert函数

麻省理工学院；

结束；

v _ date:= v _ date+；

结束循环；

DBMS _ SQL close _ cursor(v _ cur)；

结束；

自动删除过期的分区

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

u很快

u占用更少的回滚表空间。

u生成较少的日志。

u释放空间

如果有全局索引，您需要在删除分区后重建索引

分区的自动删除按如下方式实现

Lishi Xinzhi/Article/program/Oracle/201311/18275