1 简介
随着信息业的发展,在企业级数据库应用中,经常会有一些几十GB,上百GB的数据表。这些大数据量表的设计,维护及其备份都是数据库管理中的重点及其难点。本文就从设计<!---->
<!---->
<!----> |
<!---->
|
|
<!----><!---->
<script></script>
|
|
|
|
|
、维护及其备份方面探讨一下大数据量表的管理。
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获得, 然后与维护字典中的数据保留天数进行比较,删除过期的数据分区。
分享到:
相关推荐
Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主数据管理Oracle主...
### MS SQL Server中大数据量表的查询优化 #### 摘要 在MS SQL Server环境中如何有效处理记录条数超过2000万且每日增加20万条的数据表,这是许多开发人员面临的挑战。本文将通过实际案例来探讨这个问题,并提供一...
它能够从Oracle数据库中提取数据,并通过自定义的模板来呈现这些数据,从而生成高质量的报表、发票、信件等。 #### 二、测试环境 本文档基于以下环境进行测试: - **应用环境**:Oracle E-Business Suite (EBS) ...
浅谈Oracle数据库安全管理.pdf
浅谈ORACLE数据库安全管理策略.pdf
### MSSQLServer中大数据量表的查询优化 #### 摘要 在MSSQL Server中处理记录条数超过2000万且每日增量达20万条的大数据量表,是许多开发者面临的一项挑战。本文通过一个实际案例探讨了解决这一问题的方法及其潜在...
浅谈ORACLE数据库的管理与维护.pdf
Oracle数据库的安全管理策略是保障企业数据安全的关键环节。Oracle数据库作为关系型数据库的代表,其安全性涉及多个层面,包括用户管理、权限管理、资源限制管理等。以下将详细阐述这些策略。 首先,数据库系统安全...
Oracle数据库作为一款广泛应用于管理信息系统、企业数据处理、互联网和电子商务领域的关系型数据库管理系统,其性能优化对于确保系统的稳定性和高效运行至关重要。随着数据量的快速增长,数据库性能优化成为数据库...
Oracle SOA主数据管理解决方案Oracle SOA主数据管理解决方案Oracle SOA主数据管理解决方案Oracle SOA主数据管理解决方案Oracle SOA主数据管理解决方案Oracle SOA主数据管理解决方案Oracle SOA主数据管理解决方案...
Oracle数据同步技术是企业级数据库管理的关键组成部分,通过Standby/DataGuard和Stream等工具,能够实现高效、安全的数据同步,满足业务连续性、灾难恢复等需求。随着Oracle数据库的不断升级,这些技术也在持续优化...
### 浅谈Oracle优化排序的操作 #### 概念与机制 在探讨Oracle排序操作的优化之前,我们首先需要理解Oracle数据库中的排序是如何发生的以及它所依赖的资源。Oracle的排序操作通常发生在以下几种情况下:创建索引、...
浅谈ORACLE数据库RAC集群备份.pdf
Oracle 大数据量操作性能优化 Oracle大数据量操作性能优化是指在处理大量数据时,如何提高 Oracle 数据库的性能。该优化技术涉及到多个方面,包括分区、Direct Insert、并行和排序处理等。 分区是 Oracle 中的一种...
如何有效地管理和维护这些大数据量表,提高应用系统的性能并简化数据管理,成为了一个亟待解决的问题。 #### 二、解决策略 针对大数据量表的处理,可以采取两种基本策略:分区处理和分表处理。 ##### 1. 分表处理 ...
【总结】Oracle数据库管理是一个复杂而精细的工作,涉及到多个层面,包括但不限于系统监控、性能优化、安全管理和数据备份。良好的数据库管理实践对于任何依赖于Oracle数据库的企业来说都是至关重要的,它直接影响到...
浅谈ORACLE数据库调优.pdf
标题中的“浅谈ORACLE与SQL SERVER的差异”暗示了我们将探讨两个主要的数据库管理系统——Oracle和Microsoft SQL Server之间的区别。这两个系统都是广泛使用的数据库解决方案,各有其特点和优势。 在性能方面,...
Oracle 数据字典是数据库管理系统中的一个重要组成部分,它存储着关于Oracle数据库结构、权限、对象以及系统设置等元数据。在数据库管理和开发过程中,了解和掌握数据字典对于优化查询、故障排查以及数据库设计都至...