深入Oracle11g分区功能.docx

1、深入Oracle11g分区功能深入Oracle 11g分区功能深入Oracle 11g分区功能 摘要：Oracle 11g分区功能可以减轻DBA的工作负担，简化Oracle数据库的日常管理维护工作。本文向您详细介绍Oracle 11g分区功能。 标签：Oracle 11g分区 数据库分区是每种数据库都需具备的关键功能之一。几乎所有的Oracle数据库都使用分区功能来提高查询的性能，Oracle 11g分区功能可以简化数据库的日常管理维护工作，大大减轻了DBA(数据库设计和管理工程师)的工作负担。Oracle 11g中提供的新功能增添了更多的数据库分区功能选择，使分区功能在使用和维护上变得更加灵

2、活；同时，Oracle 11g也提供更多的分区功能选择。Oracle 8.0版最早推出了表格的分区功能，使Oracle成为了第一个支持物理分区的RDBMS供应商。SQL Server(2000)和DB2都只支持逻辑分区(使用UNION ALL视图)，而SQL Server 2005并不直接支持物理分区(需通过分区功能)。Oracle的分区功能选择很受用户群的欢迎，因为分区功能能够改善应用程序的性能、可管理性和可用 性，其中最重要的是DSS应用程序。由于该功能受到广泛的喜爱，因此每次发布新版本都会有功能上的不断提高。下面的表格列举了随版本更新而不断提高的分区功能(高级)：Oracle数据库版本分

3、区功能8.0.5 引入范围分区功8i 引入散落列分区和组合范围散列分区功能。9i 引入列表分区和组合范围列表分区功能。10G 引入了对索引组织表进行范围分区、列表分区和三列分区的功能。该版本还引入了其他组合分区功能选择。11G 引入扩展的分区功能：-Interval分区-外键分区-虚拟列分区-引入了分区建议器分区功能类型现在让我们简单的讨论一下以上每个分区功能的特性：范围分区：数据根据分区键值范围指定进行分布。比如，如果我们选择一个日期列作为分区键，分区“JAN-2007”就会包括所有包含从01-JAN-2007到31-JAN-2007之间的分区键值(假设分区的范围是从这个月的第一天到这个月的

4、最后一天)。散列分区：将散列算法用于分区键来确定指定行所在的分区。这个分区方法能够保持I/O平衡，但是不可用于范围查询或不等式查询。列表分区：数据根据分区键值列表指定进行分布。这个分区方法对于离散的列表非常有用，如地区、国家等。组合分区：结合2个数据分区方法可以成为一个组合分区方法。先用第一个数据分布方法对表格进行分区，然后再用第二个数据分区方法对每个分区进行二次分区。组合分区方法有以下组合：组合范围散列分区、组合范围列表分区、组合范围范围分区、组合列表范围分区、组合列表列表分区、组合列表散列分区。10G支持对索引组织表(索引和数据一起的表格)进行范围分区、列表分区或散列分区，但是不支持对其进

5、行组合分区。Interval分区：11G版本引入的interval分区范围分区的一种增强功能，可实现equi-sized范围分区的自动化。创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引1. SQLCREATETABLESALES_PART 2. (TIME_IDNUMBER, 3. REGION_IDNUMBER, 4. ORDER_IDNUMBER, 5. 6. ORDER_DATEDATE, 7. 8. SALES_QTYNUMBER(10,2), 9. SALES_AMOUNTNUMBER(12,2) 10. ) 11. 1

6、2. PARTITIONBYRANGE(ORDER_DATE) 13. 14. 15. INTERVAL(NUMTOYMINTERVAL(1,month) 16. 17. 18. (PARTITIONp_firstVALUESLESSTHAN(01-JAN-2006);19. Numtoyminterval功能把数字转换成 INTERVAL YEAR TO MONTH文字(YEAR or MONTH)。进行Interval分区的表格有传统的范围部分和自动生成的interval部分。进行范围分区的表格可以通过使用ALTER TABLE命令的 SET INTERVAL选项扩展成为Interval分

7、区的表格。外键分区：分区方案的引入是以相关表格通过相同的分区策略获得好处作为前提设想的。Detail表格通过PK-FK关系从master表格继承相同 的分区方案。我们不需要把分区键存储在detail表格中，通过关键词“PARTITION BY REFERENCE”，detail表格获得master表格的分区方案。虚拟列分区：在之前的Oracle版本里，只有分区键存在与表格中才可以实现对表格的分区功能。而Oracle 11G的新功能“虚拟列”打破了这一限制，允许通过使用表格中的一列或多列的表述确定分区键。而虚拟列只作为元数据存储。例如：在表格ACCOUNTS中添加一个虚拟列：1. SQLCREA

8、TETABLEACCOUNTS 2. 3. (acc_nonumber(10)notnull, 4. 5. 6. acc_namevarchar2(50)notnull, 7. 8. acc_locvarchar2(5), 9. 10. acc_branchnumber(2)generatedalwaysas 11. 12. (to_number(substr(to_char(acc_no),1,2);使用虚拟列作为分区键：1. SQLCREATETABLEaccounts 2. 3. (acc_nonumber(10)notnull, 4. 5. 6. acc_namevarchar2(50

9、)notnull, 7. 8. acc_locvarchar2(5), 9. 10. acc_branchnumber(2)generatedalwaysas 11. 12. (to_number(substr(to_char(acc_no),1,2) 13. 14. partitionbylist(acc_branch);15. 分区建议器Oracle 11g还提供了一个分区建议器，可支持分区建议的生成，类似于10G中支持实物化视图、实物化视图日志和索引的功能。事实上，分区建议器在Oracle 11g中是SQL Access Advisor的一部分。这个建议器帮助生成建议，并可以显示出推荐分

10、区实施后可获得的效果。它还会生成创建有效分区的脚本，可手动通过SQL*Plus 提交给Oracle或Enterprise Manager。Oracle 分区表(Partition)Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。 Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。 分区提供以下优点： 由于将数据分散到各个分区中，

11、减少了数据损坏的可能性； 可以对单独的分区进行备份和恢复； 可以将分区映射到不同的物理磁盘上，来分散IO； 提高可管理性、可用性和性能。 Oracle提供了以下几种分区类型： 范围分区（range）； 哈希分区（hash）； 列表分区（list）； 范围哈希复合分区（range-hash）； 范围列表复合分区（range-list）。 索引也可以进行分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化 时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行

12、维护操作时，通常会 导致全局索引的INVALDED，必须在执行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进行分区维护的同时重建全局索引。 全局索引可以包含多个分区的值 局部索引比全局索引容易管理,而全局索引比较快 注意:不能为散列分区 或者 子分区创建全局索引 如果表中预期的数据量较大，通常都需要考虑使用分区表，确定使用分区表后，还要确定什么类型的分区（range partition、hash partition、list partition等）、分区区间大小等。分区的创建最好与程序有某种默契，偶曾经创建分区表，按自然月份定义分区的，

13、但程序却在查询时默认的开始时间与 结束时间是：当前日期-30至当前日期，比如当天是9.18号，那查询条件被产生为8.18-9.18，结果分区后并不没有大幅提高性能，后来对程序的查 询日期做了调整，按自然月查询，系统的负载小了很多。 从Oracle8.0开始支持表分区（MSSQL2005开始支持表分区）。 Oracle9i 分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分，从而可以更精细地对这些数据库对象进行管理和访问。 Oracle 提供了种类繁多的分区方案以满足所有的业务需要。另外，由于在 SQL 语句中是完全透明的，所以分区可以用于几乎所有的应用程

14、序。 分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块。索引也可以这么分区。每个分区可以被单独管理，可以不依赖于其他分区而单独发挥作用，因此提供了一个更有利于可用性和性能的结构。 分区可以提高可管理性、性能与可用性，从而给各种各样的应用程序带来极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分 区还能够在很大程度上简化日常管理任务。分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。分区是建立上亿万字节数据系统或需要极高 可用性系统的关键工具。 在多CPU配置环境下，如果打算使用并行执行，则分区提供了另一种并行的方法。通过给表或索引的不同分区分

15、配不同的并行执行服务器，就可以并行执行对分区表和分区索引的操作。 表或索引的分区和子分区都共享相同的逻辑属性。例如表的所有分区或子分区共享相同的列和约束定义，一个索引的分区或子分区共享相同的索引选项。然而它们可以具有不同的物理属性如表空间。 尽管不需要将表或索引的每个分区或子分区放在不同的表空间，但这样做更好。将分区存储到不同的表空间能够 l 减少数据在多个分区中冲突的可能性 l 可以单独备份和恢复每个分区 l 控制分区与磁盘驱动器之间的映射对平衡I/O 负载是重要的 l 改善可管理性可用性和性能 分区操作对现存的应用和运行在分区表上的标准DML 语句来说是透明的。但是可以通过在DML 中使用

16、分区扩展表或索引的名字来对应用编程，使其利用分区的优点。 可以使用SQL*Loader、Import 和Export 工具来装载或卸载分区表中的数据。这些工具都是支持分区和子分区的。 分区的方法Oracle9i 提供了如下5种分区方法： l 范围分区Range l 散列分区Hash l 列表分区List l 组合范围-散列分区Range-Hash l 组合范围-列表分区Range-List 可对索引和表分区。全局索引只能按范围分区，但可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护。 一般组建局部索引，以便反映其基础表的结构。它与基础表是等同分区的，即它与基础 表

17、在同样的列上分区，创建同样数量的分区或子分区，设置与基础表相对应的同样的分区边界。对局部索引而言，当维护活动影响分区时，会自动维护索引分区。这保证了索引与基础表之间的等同分区。 关于范围分区Range： 要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。 关于散列分区Hash： 如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量

18、的分区中均等地划分数据 的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O 驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。 关于列表分区List： 当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个 范围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或 散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的

19、数据集进行分组和组织到一起。 与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。 关于组合范围-散列分区： 范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。 关于组合范围-列表分区： 范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述

20、。 创建或更改分区表时可以指定行移动子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLE ROW MOVEMENT。本产品(项目)使用ENABLE ROW MOVEMENT子句。 分区技术能够提高数据库的可管理性： 使用分区技术，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。 分区技术提高可管理性的一个典型用法是支持数据仓库中的滚动

21、视窗加载进程。假设数据库管理员每周向表中加载新数据。该表可以是范围分区，以便 每个分区包含一周的数据。加载进程只是简单地添加新的分区。添加一个新分区的操作比修改整个表效率高很多，因为数据库管理员不需要修改任何其他分区。从分 区后的表中去除数据也是一样。你只要用一个很简便快捷的数据字典操作删掉一个分区，而不必发出使用大量资源和调动所有要删除的数据的 DELETE 命令。 分区技术能够提高数据库的性能: 由于减少了所检查或操作的数据数量，同时允许并行执行，Oracle9i 的分区功能提供了性能上的优势。这些性能包括： l 分区修整：分区修整是用分区技术提高性能的最简单最有价值的手段。分区修整常常能

22、够将查询性能提高几个数量级。例如，假定应用程序中有包含定单历史记录的 定单表，该表用周进行了分区。查询一周的定单只需访问该定单表的一个分区。如果该定单表包含两年的历史记录，这个查询只需要访问一个而不是一百零四个分 区。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 公司将把分区修整技术与索引技术、连结技术和并行访问方法一起联合使用。 l 分区智能联接：分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起，而且每个表都用联接关键字来分区时，就可以使用分区智 能联接。分区智能联接将大型联接分解成较小的发生在各个

23、分区间的联接，从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。 l 更新和删除的并行执行：分区功能能够无限地并行执行 UPDATE、DELETE 与 MERGE 语句。当访问分区或未分区的数据库对象时Oracle 将并行处理 SELECT 与 INSERT 语句。当不使用位图索引时，也可以对分区或未分区的数据库对象并行处理 UPDATE、DELETE 和 MERGE 语句。为了对有位图索引的对象并行处理那些操作，目标表必须先分区。这些 SQL 语句的并行执行可以大大提高性能，特别是提高 UPDATE 与 DELETE 或 MERGE 操作涉及大量数据时的性能。 分区

24、技术提高可用性： 分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分，例如，如果分区表的分区不能用，但该表的所有其他分区仍然 保持在线并可用。那么这个应用程序可以继续针对该分区表执行查询和事务处理，只要不是访问那个不可用的分区，数据库操作仍然能够成功运行。 数据库管理员可以指定各分区存放在不同的表空间里，从而让管理员独立于其它表分区针对每个分区进行备份与恢复操作。 还有，分区功能可以减少计划停机时间。性能由于分区功能得到了改善，使数据库管理员在相对较小的批处理窗口完成大型数据库对象的维护工作。 Oracle最大允许存在多少个分区呢? 我们可以从Oracle的Con

25、cepts手册上找到这个信息，对于Oracle9iR2: Tables can be partitioned into up to 64,000 separate partitions. 对于Oracle10gR2，Oracle增强了分区特性： Tables can be partitioned into up to 1024K-1 separate partitions. 关于何时应该进行分区,Oracle有如下建议: Tables greater than 2GB should always be considered for partitioning. Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current months data is updatable and the other 11 months are read only.