Oracle表分区

2024-07-10

Oracle表分区（通用5篇）

Oracle表分区篇1

表空间:是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表,所以称作表空间。

分区表:当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。

1 Oracle提供了以下几种分区类型

1)范围分区(range);

2)哈希分区(hash);

3)列表分区(list);

4)范围-哈希复合分区(range-hash);

5)范围-列表复合分区(range-list)。

ODS(Operational Data Store)是数据仓库体系结构中的一个可选部分,ODS具备数据仓库的部分特征和OLTP系统的部分特征,它是“面向主题的、集成的、当前或接近当前的、不断变化的”数据。

2 ODS有四种类型

1)类ODS,与应用系统的数据延迟为1～2秒,实时或近似实时;

2)类ODS,与应用系统的数据延迟为2～4小时;

3)类ODS,与应用系统的数据延迟为12～24小时;

4)类ODS,数据仓库中部分决策分析数据回流至ODS中。

可以说,数据延迟时间越短,ODS建设难度越高,其中I类ODS的建设难度最高,建设成本也是最高的。从现阶段的应用来看,我单位的ODS建设目标是III类,ODS数据与应用系统的数据延迟保持在24小时左右。

3 现状分析

目前,我单位系统内部有很多的子系统,如核心业务系统数据RUCCDTA、核心业务系统参数RUCCCMN、中间业务AGENT、支付系统数据、反洗钱、支票影像、身份证核查、客户信息CIF等多个系统平台等。他们之间的信息存在着重复和不一致的情况,如客户资料、信贷数据等。如何综合应用这些数据,首要任务就是进行数据整合,ODS的特点恰恰可以担当起这付重任。

下面以核心业务系统数据RUCCDTA平台内CMVCA、SVDSA表为例,介绍如何利用oracle数据库的range分区及range子分区特性即范围-范围复合分区(range-range)技术,建设ODS数据交换平台。

4 表在oracle数据库中的规划设计

图1为ODS系统架构图。

4.1 设计说明

1)建立系统默认的表空间、索引表空间,不需要分区的表建立在此给定的空间上

2)建立40个表空间(每个机构对应一个表空间,其原因是利于数据的保存,以后可以针对每个机构进行数据导入和导出,也利于针对每个机构的数据查询)

3)创建数据表,以机构range作分区,每个表共有40个表分区

4)在每个机构表分区中再按照日期中的年range进行子分区,从系统上线的2003年到现在2009年共7年。因此每个表的分区共有:40个表分区*7=280个分区

5)对每一个表建立LOCAL索引(相当于在每一个微分区内建立索引)

6)每个最小分区的数据量:以CMVCA表为例,400G/40个机构=10G/每机构/每年。每个子分区的数据量不大,利于数据的导出、保存及导入。

4.2 详细操作步骤

4.2.1 创建系统缺省表空间:(用户默认表空间)

4.2.2 用户

4.2.3 创建分区表空间

表空间脚本:crtspace_dta.sql,根据机构数量生成对应的表空间

4.2.4 创建分区表

1)CMVCA交易日志文件脚本(带表分区VCSBNO、子分区VCTRDT)

2)交易细文件脚本(表分区DSSBNO、子分区DSTRDT)

5 创建表索引

6 结束语

至此,大数据表的在Oracle数据库中分区设计及实施已完成,此操作步骤已经在我单位的ODS建设中完全验证正确。本文的目的也是为了向其他在ODS建设过程中遇到类似问题的同行提供一点借鉴,希望能对他们有所帮助,也希望他们在大数据表处理过程中能够建设起结构清晰数据交换平台。

摘要：该单位目前要在业务系统和数据仓库之间建立一个ODS数据交换平台。此ODS数据平台用于存放从各个业务系统直接抽取出来的数据,这些数据从数据结构、数据之间的逻辑关系上都与原业务系统基本保持一致,在ODS数据平台中利用SCHEME方式进行区分。到目前为止,我单位的业务数据已运行多年,基本信息数据、历史归档数据庞大。如何规划一个设计合理、运行安全、查询快捷、性能高效的ODS数据交换平台就显得尤其重要。该文详细论述了如何利用oracle的表空间、表分区、子分区技术并结合我单位目前业务数据的特点来建立一个高效、安全、稳定的ODS数据交换平台。

关键词：数据库,oracle,表分区,子分区,表空间,LOCAL索引

参考文献

[1]18Partitioned Tables and Indexes[EB/OL].http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10743/partconc.htm.

[2]Oracle Database Concepts10g Release1(10.1)Part Number B10743-01(18Partitioned Tables and Indexes)[EB/OL].http://www.ora-cle.com/technology/documentation/database10g.html.

[3]Oracle Database Concepts10g Release2(10.2)Part Number B14231-02(17Managing Partitioned Tables and Indexes)[EB/OL].http://www.oracle.com/pls/db102/homepage.

Oracle表分区篇2

针对以上问题，本文讨论了如何应用Oracle表分区、索引分区等相关技术来解决海量读写性能和可维护性问题。根据特定的数据模式，使用预定义的函数和架构对表进行分区，将具有相同分区键的所有行都直接放置到特定的位置，这样对表进行查询时只需要在特定表分区内进行扫描，而不必进行全表扫描。这样做的益处是缩短了磁盘检索时间，加快了查询速度，当把各分区分布在不同的物理磁盘上时，提高这些磁盘的并行处理能力以优化查询性能来减轻数据库的运行压力和维护压力，提高了数据库和相关应用系统的性能和效率。

1 采用分区管理海量数据的好处

1)利用表分区技术将一个大表水平分区成若干小的分区。查询时只需访问某些分区数，减少了对大表的全表扫描，缩短了磁盘检索时间，加快了查询速度。

2)将数据加载到现有分区表的新分区中时，最大减少了对其他分区中的数据访问的影响，并且性能相当于将同样数据加载到新的空表中。

3)允许通过将分区移入和移出分区表来维护分区，当删除和增加一个分区时，最大程度的减少了对其他分区的访问影响。

4)对数据表进行分区后利于数据的备份与恢复，并且可以灵活的分配磁盘使用，极大地提高了磁盘性能，便于管理。

2 Oracle分区技术

针对大型数据表的管理，Oracle数据库管理系统提供了范围分区、列表分区、散列分区、组合范围-列表分区、组合范围-散列五种分区方案，它们的特点如下：

1)范围分区(Range Partitioning)：由一定范围值的分区关键字指定每个分区，这是最常用的分区类型，而且常常分区键为日期类型。

2)列表分区(List Partitioning)：由列表值的分区关键字指定每个分区，能够准确地控制数据行到分区的映射，用一种自然地方式组织无序和无关联的数据集。

3)散列分区(Hash Partitioning)：将散列算法用于分区关键字来确定指定行的分区，通过散列函数控制数据行到分区的映射，分区易于实现，并且有助于提高并行查询，连接等操作的执行性能。

4)组合分区(Composite Partitioning)：组合分区首先进行范围分区，然后在每个子分区中使用散列或列表分区。组合范围-散列分区结合了范围分区提供的增强的管理性和散列分区并行化查询的优势。组合范围-列表分区结合了范围分区的可管理性和列表分区用于准确控制的优势。

上面四种分区方案都具有各自不同的应用特征，因此在选择何种分区方案前需要对实施的对象进行综合分析，在实际应用中需根据需求及分区键的属性特征选定一种适合的分区方法。下面将结合纳税申报业务案例说明如何应用分区方案。

3 Oracle分区技术在纳税申报平台的应用

3.1 选择分区表的策略

在纳税申报系统中，数据库中存储的申报业务明细表(person_taxdetail)集中了全市数据，其历史数据：历史业务报明细从表(TAX_FORM)、历史申报业务明细主表(FORM_ALL_DATA)已有上亿，而且每月有五百多万的数据增长量，每天晚上各种日、旬、月报表批量程序都需要运行，数据访问量巨大，因此我们选择此表进行数据的分区技术改造并进行有效的分区管理，是提高系统性能的关键。

申报业务明细表person_taxdetail的表结构定义如下：

历史业务报明细从表(TAX_FORM)结构定义如下：

历史申报业务明细主表(FORM_ALL_DATA)结构定义如下：

分区键的选择也是分区方案中的关键步骤。分区键的值分布得越平均，各分区的空间大小就越均等，磁盘I/O负载就越均衡，因此分区键决定了你将使用哪一种分区方案。在表person_taxdetail中，有send_date字段记录了申报明细的时间标签。根据业务的需求，不同应用的申报明细数据需要保留的时间不同，多则2年，少则3个月。因此，我们采用了范围分区方案，分区键选为send_date(申报时间)，以1个月的时间范围划分不同分区，同时保留近两年的分区数据。

图1说明了对申报业务明细表(person_taxdetail)及历史申报业务明细主表和历史业务报明细从表(tax_form,form_all_data)应用范围分区后的结构。

3.2 建立分区表的分区索引

一般来说，创建索引可以有效提高检索数据库的效率，一个建有合理索引的数据库的数据库应用系统可能比一个没有建立索引的数据库应用系统效率高几十倍，但并不是索引越多越好，在那些经常需要修改的数据列上建立索引，将导致索引B*树的不断重组，造成系统性能的下降和存储空间的浪费。对于一个大型表建立的索引，有时并不能改善查询的速度，反而会影响整个数据库的性能。所以，在对一个大型表进行分区之后，可以建立相应的分区索引。当我们确定了分区策略和分区键后，我们就要根据实际情况考虑需要多少个表空间。原则上，第一次建立多少个分区，逻辑上需要多少个分区来分隔数据。从技术角度上，拥有多个分区所带来的额外维护工作微不足道，重点在于将数据表划分为逻辑组。在实际操作中我们选择保留2年的历史数据，按照每1个月数据划分一个分区，为每个分区建一个表空间，索引用局部索引，索引单独一个表空间。

3.3 系统优化后的压力测试性能

纳税申报系统是一个B/S的系统，服务器是Solaris 10下Weblogic8.1。服务器监控的是Solaris 10。压力测试的过程是同时加载200用户进行并发操作。每15秒增加5个用户，持续加压20分钟，每30秒减少10个用户。

通过使用LoadRuner工具和PL/SQL Developer工具编写的数据库层的压力测试代码，对系统进行了压力测试,来验证优化后的数据库及系统的压力测试性能问题。图2是压力测试性能结果，从图中可以清晰的看出在当前压力测试下每秒处理1350条SQL语句，数据的物理读是30.6/s，数据命中率100%。可以完全说明优化后的数据表中的数据基本都是从缓存中获取，并不是原先改造前大部分从磁盘中获取，这样大大增加数据存取的效率。

4 表分区的维护方法及应用特征

Oracle中提供了移动分区、添加分区、拆分分区、删除分区、结合分区、合并分区、交换表分区等分区维护方法，每种表分区维护方法都具有各自不同的应用特征。我们使用了Oracle的SQL*Loader Import和Export工具来装载或卸载分区表中的数据。这些工具都是支持分区和子分区的;我们对于历史数据的处理将更加的灵活和方便。可以随时从数据表中备份出暂时已经不需要的历史数据分区数据，然后从数据表中删除对应分区，这样将极大的减少数据表的规模，提升数据库和应用系统的性能。

5 结论

应用分区技术对税务申报业务平台的数据重组后，该系统投入运行半年时间以来，系统性能一直稳定，响应速度更快，维护管理也更加方便。证明了采用有效的分区策略对数据库进行数据升级具有重要意义。当然，并不是所有的数据库应用都需要使用分区技术，当数据表记录大于千万条，表中包含大量历史数据和当前数据或数据增长迅速的情况下，使用分区技术才能显著提高系统的可管理性、性能及可用性。

参考文献

[1]Thomas Kyte.Effective Oracle by Design[M].America:racle Press,2009.1:100-120.

[2]Thomas Kyte.Expert Oracle Database Architecture[M].America:racle Press,2009:130-133.

[3]Richard.Oracle材料e Database10G性能调整与优化[M].清华大学出版社,2009:275-300.

[4]赵韶平,罗海燕,李霁红.Power Designer系统分析与建模[M].清华大学出版社,2004:50-80.

[5]盖国强,冯春培.oracle数据库性能优化[M].人民邮电出版社,2005:200-300.

[6]张静,张雨,许春艳.基于Oracle数据库的查询优化技术[J].科技创新导报,2009,06.

[7]苏大威,张乐.基于Oracle数据库开发系统的物理设计优化策略[J].计算机工程,2002(2):115-116.

Oracle表分区篇3

广播监测系统是一个三层的体系架构：由数据处理中心、监测台和监测前端组成。监测前端接收监测台下发的监测指令，对当地广播质量和效果进行监测，并将监测结果传回监测台。监测台一方面负责管理和维护它所管辖的监测前端，确保它们正常运转；另一方面对监测前端传回的广播质量和效果监测数据进行处理，上报给数据处理中心。数据处理中心是广播监测系统的核心，负责汇总和处理各监测台上报的广播质量和效果数据，形成各类报表和报告上报有关部门。

广播监测系统的软件架构也是分布式的，数据处理中心和监测台都有自己的广播监测软件，软件包括应用软件和数据库两部分。监测台的广播监测系统数据库存储自台的广播质量和效果监测数据，而数据处理中心广播监测系统数据库存储各监测台上报所有的广播质量和效果监测数据。数据处理中心和各监测台的数据库之间存在双向同步。所有数据库软件均采用Oracle数据库管理系统。Oracle作为当今世界上使用最广泛的数据库，也是最优秀的数据库管理系统，它以能保证分布式信息的安全性、完整性和一致性、较强的并发控制能力和恢复能力，以及管理超大规模数据库的能力而著称于世。目前数据处理中心数据库软件的版本是Oracle10g，各监测台的版本是Oracle9i, 见图1。

每天监测台利用本台的监测系统和下属的各个监测前端对当地的广播效果进行不间断地收测，收测结果存储在本台广播监测系统数据库的表Gbxg＿Tab里。通过同步软件，这些收测数据会实时地同步到数据处理中心的广播监测系统数据库的同名表Gbxg＿Tab里。由于监测台和监测前端较多，同时每个监测台和监测前端要收测的频率也很多，而且是不间断的收测，因此表Gbxg＿Tab的数据增长很快。据统计，该表记录数每天会增加五万多条，一年下来记录数将达到两千万条左右，堪称广播监测系统数据库第一大表。随着广播监测系统规模的进一步扩大，监测台和监测前端的数量进一步增加，该表的数据会以更快的速度增加。在这种情况下，采用分区表来存储这些海量数据是非常适合的，下面首先对Oracle数据库里分区表的概念、优点、分类和日常管理维护等进行简要介绍。

2 分区表简介

2.1 分区表的基本概念

在大型的数据库应用中，需要处理的数据量可以达到几十到几百GB，甚至TB级。为了提高对对这些巨型数据库的读写和查询速度，Oracle提供一种分区技术。用户可以在创建表时应用分区技术，将数据以分区形式保存。

分区表是指将巨型的表或索引分割成相对较小、可独立管理的部分。分区后的表和未分区表在执行查询语句或DML语句时没有任何区别。相反，当对表进行分区后，使用DDL时还可以对每个单独的分区进行操作。因此，对巨型表或索引分区后，能够简化对它们的管理和维护操作，而这对于应用是完全透明的。

对表进行分区时，每个分区都有相同的逻辑属性，如每个分区都有相同的字段名、数据类型和约束等。但是各个分区的物理属性可以不同，各分区可以位于不同的表空间中。

2.2 分区表的优点

(1) 增强可用性：如果表的某个分区损害，表在其他分区的数据仍然可用。

(2) 维护方便：如果表的每个分区损害，只需修复该分区即可。

(3) 均衡IO：可以把不同的分区映射到不同的磁盘以平衡IO，改善整个系统性能。

(4) 改善查询性能：对分区表的查询可以只搜索自己关心的分区，以提高检索速度。

2.3 分区表的分类

在Oracle10g数据库中，分区的方法有多种：范围分区、列表分区、散列分区、组合范围散列分区和组合范围列表分区等，以下分别进行介绍。

2.3.1 范围分区

范围分区根据要分区表中一列或多列的范围将行映射到分区上。而且要分区的行应当平均地发布在每个分区中。为了使用范围分区，必须指定三个标准：分区方法；分区的一列或多列；每个分区的界限。

2.3.2 列表分区

使用列表分区，通过知道分区列中的离散值，列表分区可以明确地控制分区列中的每个值映射到分区的方式。

2.3.3 散列分区

如果数据的分布不能轻易地匹配范围分区模式或不知道表中的行数，但又希望得到分区表固有的好处，这种情况下采用分区列表是一种较好的选择。

2.3.4 组合范围列表分区

组合范围列表分区是对范围和列表分区技术的组合。通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用列表分区技术进一步细分。

2.3.5 组合范围散列分区

有时需要根据范围分区后，还需要将每个分区内的数据再散列地分布在几个表空间中。这种分区方法称为组合范围散列分区。组合范围散列分区是范围分区的一种改进形式，同时具有范围分区和散列分区的优势。

2.4 分区索引

在分区表上建立索引时，可以对索引进行分区，也可以不对索引进行分区。对索引进行分区的目的与对表进行分区的目的是相同的，都是为了更加易于管理和维护巨型对象。在Oracle10g数据库中，一共可以为分区表建立三种类型的索引：局部分区索引，全局分区索引和全局非分区索引。

2.5 分区表的维护

分区表建立好以后，在应用的过程中还需要经常对分区表进行维护和管理。日常维护和管理的内容包括：增加分区、合并分区、分割分区、删除分区和截取分区等。

3 分区表应用举例

下面具体介绍如何利用分区表技术存储海量的广播效果监测数据。对于表Gbxg＿Tab，我们决定采用范围分区的方法，分区列为表Gbxg＿Tab中的字段Mark＿Datetime，该字段存储广播效果每次打分的时间。这样做的原因是广播效果打分的次数每天大致相同，按照字段Mark＿Datetime范围分区时，可以确保不同分区的记录数相差不多，数据分布比较均匀。然后考虑分区界限：如果采用按年分区，每个分区需要存储一年的数据，这会导致分区数据太多而无法体现其优势；如果按月分区，每个分区只存储一个月的数据，数据量少了很多，但一年就有十二个分区，分区数太多，不好管理和维护。因此采取的是一个折中方案：按季度分区，每个分区存储一个季度的数据，这样每个分区的记录数不会太多，同时一年只有四个分区，分区数也不会太多。

以下是创建该分区表的具体命令（Sql>表示Sql命令行提示符）：

在上述命令中，Create Table New＿Gbxg＿Tab表示新建一张表，表名为New＿Gbxg＿Tab。

Partition By Range (Mark＿Datetime) 表示采用范围分区的方法, 分区列为Mark＿Datetime。

接下来的五行分别指定五个分区，以第一行为例：Partition P2010a指定该分区名为P2010a, 代表2010年的第一季度；Values Less Than (To＿Date (‘2010-04-01’, ’Yyyy-Mm-Dd’) 指定该分区的打分时间上限是2010年4月1日，即该分区存储的是2010年第一季度的广播效果打分数据；Tablespace App＿Data＿01指定该分区存储在表空间App＿Data＿01上。其它四行依此类推。前四个分区分别存储在四个不同的表空间上，这样做的好处是避免一个表空间的损坏不会影响到所有分区的数据。最后一个分区是为2011年提前创建的，打分时间上限Maxvalue表示没有时间限制。As Select*From Gbxg＿Tab指定该表的数据取自广播效果打分表Gbxg＿Tab（非分区表）。

分区表建完后，为了避免对应用的修改，需要将新表New＿Gbxg＿Tab改名为Gbxg＿Tab。改名之前首先删除老表Gbxg＿Tab:

Sql>Drop Table Gbxg＿Tab;

然后将新表New＿Gbxg＿Tab改名为Gbxg＿Tab

Sql>Alter Table New＿Gbxg＿Tab Rename To Gbxg＿Tab;

为了提高检索速度，可以为表Gbxg＿Tab创建索引，这里选择建立局部分区索引，因为它相对简单且易维护。

Sql>Create Index Gbxg＿Idx On Gbxg＿Tab (Mark＿Datetime) Local;

其中索引名称为Gbxg＿Idx，索引字段为Mark＿Datetime, Local指定该索引为局部分区索引。

表和索引建完，我们再设想一下2010年结束进入2011年时需要做哪些工作？首先需要为表Gbxg＿Tab创建五个新的分区，四个代表2011年的四个季度，最好一个代表2012年。这就需要用到上面分区表维护里面提到的分割分区命令：

上述四条语句共创建了五个分区。其中第一条语句将分区P2011进行分割为两个分区：P2011a和P2011，分割的打分时间点为2011年4月1日；P2011a存储在表空间App＿Data＿01上，P2011存储在表空间App＿Data＿02上。其它三条命令依此类推。

除了分割分区，这时需要做的工作可能还包括对历史数据的归档：即对2011年的数据进行归档。这时需要首先将表Gbxg＿Tab里2010年的数据进行备份，备份完后再将这些数据从表Gbxg＿Tab里删除。

备份最好采用Oracle自身提供的逻辑备份工具Exp进行，Exp支持对分区表里的指定分区进行导出：

, Dos>Exp Username/Password@Gbwdb Tables= (Gbxg＿Tab:P2010a, Gbxg＿Tab:P2010b, Gbxg＿Tab:P2010c, Gbxg＿Tab:P2010d) File=Gbxg＿2010.Dmp Log=Gbxg＿2010.Log

在上述命令中，Username/Password@Gbwdb指定连接广播网数据库的用户名、密码和数据库名；Tables= (Gbxg＿Tab:P2010a, Gbxg＿Tab:P2010b, Gbxg＿Tab:P2010c, Gbxg＿Tab:P2010d) 指定要导出分区表G bx g＿Ta b里四个分区P2010a、P2010b、P2010c、P2010d的数据；File=Gbxg＿2010.Dmp指定导出数据存储在二进制文件Gbxg＿2010.Dmp里；Log=Gbxg＿2010.Log指定导出日志存储在文本文件Gbxg＿2010.Log里。

备份完后，就可以删除2010年四个分区里的数据了。可以直接删除分区，也可以截取分区。

分区表建好后，还有一个问题需要注意：如果用户修改了表Gbxg＿Tab中分区列Mark＿Datetime里的数据，使得被修改记录记录从一个分区移动到其它分区（例如打分时间原来是2010年1月10日，修改为2010年8月10日），系统会报错ORA-14402:更新分区关键字列将导致分区的更改。

这时需要对表Gbxg＿Tab进行如下修改：

Sql>Alter Table Gbxg＿Tab Enable Row Movement;

该命令的作用是将表Gbxg＿Tab设置为允许行移动，这样系统就不会报错了。

4 结束语

随着广播监测系统规模的不断扩大和监测业务的不断发展，广播监测系统数据库里的巨型表和索引会越来越多，利用Oracle的分区技术，将这些巨型的表或索引分割成相对较小可独立管理的部分，无疑会使简化数据库的管理和维护工作，同时也使数据库的性能得到改善。

参考文献

[1]董志鹏等著.《Oracle11g从入门到精通》.北京:电子工业出版社, 2008年.

Oracle表分区篇4

目前Oracle数据库已有大量的优化技术理论和实践论证, 但针对大行业应用的特点未提供整体优化方案。笔者完整地参与了贵州省地税多个支持决策系统的设计与开发、省级数据中心的部署以及相关Oracle优化工作, 并在项目过程中对分区表技术投入了大量的时间和精力进行研究、思考与总结。笔者深刻地认识到分区表技术在实际项目中的实用性, 其对大型行业应用系统的高质量运作和行业工作的有效展开起着积极的指导作用。

1 Oracle分区表技术

分区表是将大表的数据分成称为分区的许多小的子集, 通过对分区列的判断, 将记录放在不同的分区中。每一个分区都是一个独立的段, 可以放在不同的表空间, 不同的物理磁盘里。查询时可以通过各个表来访问表中的不同分区, 也可指定分区访问。通过分区技术, 我们可以将大数据量的表和索引分成便于管理的小块, 从而避免了把每张表当作一个大的、单独的对象进行管理, 为操作数据提供了可伸缩性。同时, 将每个分区分配给更小的存储单元, 也减少了进行管理操作所需要的时间, 且增强了并行处理能力, 提高了性能。另一方面, 如果某一分区出现故障, 屏蔽故障数据的分区也增强了系统的可用性。

2 Oracle分区方案的设计

随着贵州地税业务的发展, 贵州地税提出了省级数据集中的业务要求, 在省级数据集中过程中, 由于税收业务产生的数据量大, 各个市 (州、地) 业务人员的操作习惯及数据录入规则的不一致等原因, 产生了数据的不一致、重复数据等现象。因此后台数据库中存储检查出的错误数据的相关表的大小增长很快, 数据量大, 导致数据质量分析软件平台在查询与修改错误数据时, 查询时间长, 用户需等待很长时间。在这样的情况下, 必须使用Oracle的分区表技术对数据量大影响查询效率的表进行性能优化。

因数据库中相关表的数据量过大, 在前台软件平台对其展示时花费了大量时间。为了找出影响性能的原因, 笔者使用工具对贵州省地税数据清洗平台使用期间的sql语句使用情况进行监控, 找出了7条执行时间长、使用最频繁的sql语句。并用pl/sql工具中的解释计划对这7条sql语句进行分析。

以这些SQL语句中运行的持续时间最长的一条SQL1为例, 该SQL语句运行持续时间为3小时41分钟, 但该语句的I/O请求并不高, 可以排除是I/O方面的问题。由解释计划可知该SQL语句并未使用已建好的索引, 也未使用分区消除, 且在多表的连接上耗费很高。因此需结合实际使用场景大致可以设计出三种分区的思路。

第一种, 选取单位编码字段作为分区键。该方案为贵州地税数据清洗项目最初的设计方案。其特点是每一个单位的错误数据数量都比较接近, 所以每个单位编码下分布的数据量都比较平均, 能把数据平均的分布在每个分区中, 基本解决大数据量的表访问慢的情况。但是在实际使用中发现, 因为基础表的数据量过大 (达到了8500万条数据) , 以单位编码分区后分布到每一个分区的数据量仍然接近一千万, 而且这种情况在软件系统的使用中会逐步加重, 在软件系统中访问相关页面依然需要3~5分钟, 不能满足用户的要求。

第二种, 选取检查期数字段作为分区键。该方案的特点是, 根据检查结果表中的数据分布情况来看, 用户除了访问检查期数为第一期 (CHECKTIME=1) 的数据时, 数据访问较慢以外, 访问其他各期数的数据时查询速度均在可以接受的范围内。该方案满足业务要求的原因是, 检查期数为第一期的错误数据虽然很多, 但是大部分为系统后台通过计算机自动清洗的数据, 用户不需要查询这部分数据, 只需要查询少量需人工修改的错误数据, 并且在第一期中未修改完的错误数据在接下来的几个检查期内依旧会被系统检查出来, 提供给用户修改。因此, 业务人员能在较高的查询效率下开展工作。

第三种, 考虑到Oracle数据库支持两级复合分区, 因此可以尝试对基础表检查结果表 (ta_checkresults) 先使用检查期数 (CHECKTIME) 作为分区键分区, 再在此基础上使用单位编码 (DWID) 作为分区键二次分区, 形成一个范围—列表复合分区表。

对于这三种方案, 笔者设计SQL语句做了相关的测试, 以解释计划中的耗费值多少为评判标准, 选择最优秀的性能优化方案,

通过测试的结果可以知道, 在查询所有数据时, 方案一、方案二两种方案的耗费值有较小的优势;在只查询某一单位时, 方案二和方案三有明显的优势;当查询语句的WHERE子句中包含单位编码和检查期数时, 方案三有明显的优势。综合比较分析各种情况, 方案一耗时最多, 方案二次之, 方案三耗费最小, 因此选用方案三作为分区优化的最佳方案。

按照方案三重新对数据表进行分区, 并重建索引之后, 笔者再次使用pl/sql工具中的解释计划对SQL1进行了分析, 此时SQL1对表的查询已经使用到分区消除, 并且本地索引IDX_CHKRESULT_ZSDW也已经起作用。由解释计划的结果可以看到, 经过性能优化之后, 各项耗费与优化之前相比减少了大约6倍, 查询的性能获得了巨大的提升。

3 结束语

本文论述了Oracle分区表技术的基本理论, 并结合笔者参与贵州省地税项目实践, 总结性能分析的思路与分区表技术的设计方案, 意在整理已有的优化成果, 同时为同类型大型行业应用系统的性能分析和优化工作提供参考与借鉴。

参考文献

[1]文平.Oracle数据库性能优化的艺术[M].机械工程出版社, 2012.

[2]林树泽, 李渊.Oracle数据库进阶——高可用性、性能优化和备份恢复[M].清华大学出版社, 2011.

[3]罗敏.品悟性能优化[M].清华大学出版社, 2011.

数据库表分区的方法篇5

一、避免表连接查询中的物理竞争

现有2个数据库表 (orders和stock) , 其中orders有10个在不同磁盘上的数据库表分区, 这些分区分别有一个工作进程需与数据库表stock连接。在这种情况下, 每一个工作进程都需要访问数据库表orders, 然后扫描数据库表stock进行连接。当一个工作进程运行时, 其访问数据库表orders的数据是容易的, 但必须等待其他进程释放表stock资源时才能访问数据库表stock, 这就出现了I/O竞争。为了减少或者消除I/O竞争, 我们可分区数据库表stocks, 且让每一分区分布在不同的数据库设备上。

二、分区数据库表的命令

用于分区数据库表的常用命令包括:分区 (partition) 和未分区 (unpartition) 数据库表命令、将数据均衡分布到不同数据库设备上的删除和重新创建聚族索引 (clustered indexes) 的命令、将数据恢复到指定分区的并行块复制 (bcp) 命令、显示数据分布在不同分区或设备上有关信息的命令、更新分区统计信息的命令。

(一) 分区和未分区命令

改变数据库表分区的alter table命令格式为:

alter table table_name partition n

其中, table_name是被分区的数据库表的名称, n为数据库表分区的数目。

数据库表被分区之前拥有的数据保留在第一个分区, alter table命令分区一个数据库表并没有移动其原有的数据。为了创建用于并行查询的数据库表, 可通过创建聚族索引或者bcp方法重新分布分区数据库表的数据。使用未分区从句取消数据库表分区的alter table命令格式为:

alter table table_name unpartition

(二) 将数据均衡地分布到各分区的命令

将数据均衡地分布到各分区的方法包括创建聚族索引和bcp方法。使用create clustered index命令创建索引的命令格式为:

create clustered index index_name on table_name key_colume) on segment_name

其中, index_name是索引名称, table_name是数据库表名称, segment_name为片断 (segment) 名称。

通过对含有数据的分区数据库表创建索引可以将数据均衡地分布到数据库表各分区。

使用drop index命令删除索引的命令格式为:

drop index index_name

(三) 将数据恢复到指定分区的并行bcp命令

使用并行bcp命令能将数据恢复到指定的分区数据库表分区, 使用bcp时应注意以下问题。第一, 在执行bcp前, 数据库表必须在指定的数据库片断上且被分区 (此数据库片断跨越多个数据库设备从而使数据库表的不同分区在不同的数据库设备上) 。第二, 为了避免数据库表索引引起的意外, 应删除数据库表的各种索引。第三, 使用alter table…disable trigger命令保证bcp以快速的方式恢复数据。第四, 设置数据库操作“turn log on chkpt”为“ture”。第五, 可以利用操作系统命令将数据文件分解为多个独立的文件进行恢复, 也可以使用带有“-F” (开始行) 和“-L” (结束行) 标志的命令行恢复。

bcp命令的格式为:

bcp database._name..table_name:n in file_name

(四) 其他命令

为了显示数据库表每一分区中的数据页数和数据分布信息的概况, 我们可使用sp_helpartition命令, 其命令格式为:

sp_helpartition table_name

数据库服务器将数据库分区表中的各分区的数据页分布统计到一个统计表中, 该统计表的信息会因为以下情况而不准确:第一, 数据库表分区被取消后, 紧接着被重新分区;第二, 数据库表中大量的数据行被删除;第三, 数据库表中大量的数据行被更新且被更新的数据行不在原位置;第四, bcp命令将数据文件的数据拷贝到数据库表中;第五, 插入操作被频繁地回滚。

为了更新数据库表的分区统计信息, 可使用update partition statistics命令。其命令格式为:

update partition statistics table_name[partition_number]

其中, partition_number为分区号。

三、分区数据库表的步骤

分区数据库表的步骤取决于初始时数据库表的位置, 比如数据库表不存在、数据库表存在但不在指定位置、在指定位置数据库表的设备添加等情况。

(一) 数据库表不存在情况下的数据库表分区步骤

1. 在指定的数据库设备片断上创建数据库表。

2. 使用alter table命令创建数据库表的分区, 使每一分区位于不同数据库的设备使用bcp命令, 将数据均衡恢复到各分区。

3. 使用sp_helpartition命令检查数据分布情况, 必要时可创建数据库表聚族索引使得数据均衡分布。

4. 创建非聚族索引。

5. 使用dump命令备份数据库。

(二) 在指定位置数据库表的设备添加而进行的数据库表分区步骤