oracle11g新特性-通过分区进行优化

通过分区进行优化 在 Oracle 数据库 11g 中，您的分区选择现在实际上是没有限制的。 下载 Oracle 数据库 11g “分而治之”— 没有能够比 Oracle 数据库的分区特性更好地阐释这个象征性原则的了。从版本 8 开始，您就可以将一个表或索引划分成多个段，然后将它们放在不同的表空间内。该表仍作为一个逻辑实体定址，各个分区则存储为单独的段，这就简化了数据操作。 在版本 11 中，引用分区、间隔分区、虚拟列分区以及扩展的组合分区等增强功能支持无限的分区设计可能性，并提高了可管理性。 如果您希望在分区的基础以及对您选择分区列或方案的决定有影响的因素上进一步提高，请参阅《Oracle Magazine》2006 年 9/10 月刊上我的文章。 扩展的组合分区 使用组合分区 — Oracle8i 数据库中引入的方案 — 您可以在分区中创建子分区，从而进一步细分表。但在该情况下，您只能通过散列子分区对已按范围分区的表进行再次分区。Oracle9i 中对组合分区进行了扩展，使之包括范围-列表子分区。 这些方案满足了大多数环境（但并非所有环境）的需要。例如，假设您有一个名为 SALES 的包含多列的表，其中包括两个特殊的列，它们是分区的候选列：state_code，它存储一个表示销售状态的两位代码，表面上用于计算销售税；以及 product_code，一个用于识别销售记录所销售的产品的三位数字。用户通过对两列进行同等的筛选对该表进行查询，存档要求也基于这两列。应用分区决策的原则时，您会发现这两列都是合适的分区键候选者。 在 Oracle 数据库 11g 中，您可以相当轻松地解决此问题。在该版本中，并不局限于范围-散列和范围-列表组合分区。您的选择实际上没有任何限制；您可以使用任何组合创建组合分区。 在这个示例中，您可以决定根据 product_code（因为该列具有更多离散的值）对表进行列表分区，然后根据 state_code 再次进行列表分区。下面的代码示例显示了如何实现该操作： create table sales ( sales_id number, product_code number, state_code varchar2(2) ) partition by list (product_code) subpartition by list (state_code) ( partition p101 values (101) ( subpartition p101_ct values ('CT'), subpartition p101_ny values ('NY'), subpartition p101_def values (default) ), partition p201 values (201) ( subpartition p201_ct values ('CT'), subpartition p201_ny values ('NY'), subpartition p201_def values (default) ) ) 选择并不仅限于此处显示的方法。您还可以创建列表-范围组合分区。在上面的示例中，假设产品代码不是离散的，而是在一个范围内。您将希望根据 state_code 进行列表分区，然后再根据 product_code 划分子分区。下面是实现该操作的代码示例。 create table sales1 ( sales_id number, product_code number, state_code varchar2(2) ) partition by list (state_code) subpartition by range (product_code) ( partition CT values ('CT') ( subpartition ct_100 values less than (101), subpartition ct_200 values less than (201) ), partition NY values ('NY') ( subpartition NY_100 values less than (101), subpartition NY_200 values less than (201) ) ) 您也可以创建范围-范围组合分区，如果您有两个日期域，该方法将非常方便。例如，考虑一个用于销售处理系统的表，该表包括一个交易日期和一个交货日期。您可能希望根据一个日期进行范围分区，然后再根据另一个日期进行子范围分区。该方案允许您根据日期进行备份、存档和清除。 总而言之，您在 Oracle 数据库 11g 中可以创建以下类型的组合分区： 范围-范围 范围-散列 范围-列表 列表-范围 列表-散列 列表-列表 引用分区 下面是设计分区方案过程中的一个典型问题：并非所有表都具有您需要根据其进行分区的列。假设您要创建一个销售系统，该系统包括两个简单的表（sales 和 customers）： create table customers ( cust_id number primary key, cust_name varchar2(200), rating varchar2(1) not null ) partition by list (rating) ( partition pA values ('A'), partition pB values ('B') ); sales 表的创建如下所示。它是 customers 表的一个子表。 create table sales ( sales_id number primary key, cust_id number not null, sales_amt number, constraint fk_sales_01 foreign key (cust_id) references customers ); 理想情况下，您希望用相同的方式对 sales 表和 customers 表分区：根据 rating 列进行列表分区。但有一个严重问题：sales 表没有名为 rating 的列！那么如何根据一个不存在的列进行分区呢？ 在 Oracle 数据库 11g 中，您可以使用一个称为引用分区的新特性。下面的示例显示了如何将该特性应用于 sales 表： create table sales ( sales_id number primary key, cust_id number not null, sales_amt number, constraint fk_sales_01 foreign key (cust_id) references customers ) partition by reference (fk_sales_01); 这段代码创建了与父表 customers 中相同的分区。注意，虽然没有名为 rating 的列，但仍根据该列对表进行了分区。partition by reference (fk_sales_01) 子句包括了分区定义中的外键名。该语句指示 Oracle 数据库 11g 确认通过父表（在该示例中为 customers）中使用的方案进行了分区。注意 cust_id 列的 NOT NULL 约束；这是引用分区所必需的。 如果您检查 sales 表中分区的分区边界： SQL> select partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'SALES'; PARTITION_NAME HIGH_VALUE --------------- ------------------------------- PA PB 高值为空，这意味着此处的边界派生自父表。分区的名称与父表中的名称相同。您可以通过查询 user_part_tables 视图来检查分区的类型。一个名为 ref_ptn_constraint_name 的特殊列显示了外键约束名称。 SQL> select table_name, partitioning_type, ref_ptn_constraint_name 2 from user_part_tables 3 where table_name in ('CUSTOMERS','SALES'); TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME ------------------------------ --------- -------------------------- CUSTOMERS LIST SALES REFERENCE FK_SALES_01 如果您希望按照父表分区的方式对子表进行分区，但没有相同的列，您又不想仅仅为了分区而引入这些列，此时引用分区将非常方便。而且，您不必针对每个子表显式声明一个很长的分区子句。 间隔分区 范围分区允许您根据分区键列的值的范围创建分区。下面是一个按范围分区的表的示例： create table sales6 ( sales_id number, sales_dt date ) partition by range (sales_dt) ( partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')), partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd')) ); 您在此处仅针对 2007 年 1 月和 2007 年 2 月定义了分区，如果表中插入一条 sales_dt 在 2007 年 3 月的记录，会发生什么情况？插入将失败，并显示以下错误： ORA-14400: inserted partition key does not map to any partition 显然，您需要针对 2007 年 3 月添加一个分区，然后才能插入一条记录。但通常说起来容易做起来难。您通常无法容忍事先创建大量分区，但其中很少一部分可能会产生此错误。 如果 Oracle 以某种方式自动察觉到对新分区的需要，然后创建它们，这样不是更好吗？Oracle 数据库 11g 可以，它可以使用一个称为间隔分区的特性。此时，您不必定义分区及它们的边界，只需定义一个定义了每个分区边界的间隔。下面是使用间隔分区的示例： create table sales6 ( sales_id number, sales_dt date ) partition by range (sales_dt) interval (numtoyminterval(1,'MONTH')) ( partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')) ); 注意子句：interval 后面跟着时间间隔。您在此处指示 Oracle 为每个月份创建一个时间间隔。您已经为 2007 年 1 月的数据创建了名为 p0701 的初始分区。现在，假设您插入了一条包括 2007 年 6 月数据的记录： SQL> insert into sales6 values (1,'01-jun-07'); 1 row created. Oracle 不会返回错误，而是成功执行该语句。那么这条记录将转向何处？p0701 分区不能包括该记录，我们没有为 2007 年 6 月定义分区。但此时，如果您检查该表的分区： SQL> select partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'SALES6'; PARTITION_NAME HIGH_VALUE --------------- ---------------------------------------------------------------- P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C ALENDAR=GREGORIA SYS_P41 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C ALENDAR=GREGORIA 注意名为 SYS_P1、高值为 2007 年 7 月 1 日的分区，它最多可以容纳到 6 月底的数据。该分区是由 Oracle 动态创建的，并具有一个系统生成的名称。 现在，假设您输入一个小于最高值的值，如 2007 年 5 月 1 日。理想情况下，它应该具有自己的分区，因为您的分区时间间隔是一个月。 SQL> insert into sales6 values (1,'01-may-07'); 1 row created. SQL> select partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'SALES6'; PARTITION_NAME HIGH_VALUE --------------- ---------------------------------------------------------------- P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C ALENDAR=GREGORIA SYS_P41 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C ALENDAR=GREGORIA SYS_P42 TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C ALENDAR=GREGORIA 注意新分区 SYS_P42，其上限为 6 月 1 日，因此该分区可以保留 2006 年 5 月的数据。该分区是通过拆分 SYS_P41 分区创建的（针对 6 月份）。因此，当您定义一个间隔分区方案时，Oracle 会自动创建和维护分区。 如果您希望将分区存储在特定表空间中，可以使用 store in 子句执行该操作： interval (numtoyminterval(1,'MONTH')) store in (TS1,TS2,TS3) 该子句以循环方式将分区存储在表空间 TS1、TS2 和 TS3 中。 应用程序开发人员如何定位特定分区？一种方法是知道名称，这种方法可能不可行，即使您知道名称，这种方法也非常容易出错。为了便于访问特定分区，Oracle 数据库 11g 为分区 SQL 提供了一个新语法： SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy')); SALES_ID SALES_DT ---------- --------- 1 01-MAY-07 注意新子句 for (值)，它允许您直接引用分区，而不必通过它们的确切名称进行显式调用。如果您希望截断或删除一个分区，可以调用这个扩展的分段语法。 以此方式创建表之后，DBA_PART_TABLES 视图中的 PARTITIONING_TYPE 列会显示时间间隔。 系统分区 尽管 Oracle 对此特性的实际用途期望很少，但我还是希望至少描述一下此特性，因为它真的很酷。 下面是一个很少见但并非不可能发生的用例：假设您有一个无法用任何逻辑方法分区的表。结果将是一个巨大的、单一的表，这会导致需要扩展的索引维护和其他操作等问题。 因此，开发人员提出一个解决方案：他们承诺，如果该表不能以某种方式分区，他们可以通过一种智能方式写入分区。这样，应用程序就可以控制特定记录进入哪个分区。DBA 只需定义分区。示例如下： create table sales3 ( sales_id number, product_code number, state_code number ) partition by system ( partition p1 tablespace users, partition p2 tablespace users ); 注意，没有任何分区键或边界。因此，以物理方式将表分成两个段，但仍然是一个逻辑表。以这种方式定义之后，数据库会为该表创建两个段，而不是只有一个单一的表。您可以通过以下语句对其进行检查： SQL> select partition_name 2 from user_segments 3 where segment_name = 'SALES3'; PARTITION_NAME ------------------------------ P1 P2 创建本地索引时，也是以相同的方式对其进行分区。 SQL> create index in_sales3_state on sales3 (state_code) local; Index created. SQL> select partition_name 2 from user_segments 3 where segment_name = 'IN_SALES3_STATE'; PARTITION_NAME ------------------------------ P1 P2 可以通过检查 user_part_tables 来检查分区的类型： SQL> select partitioning_type 2 from user_part_tables 3 where table_name = 'SALES3'; PARTITION --------- SYSTEM 这显示为 SYSTEM，当然，指示系统分区。需要注意的一点是，这些表类型的 high_value 列值为空。 SQL> select partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'SALES3'; PARTITION_NAME HIGH_VALUE -------------- --------------------- P1 P2 下面是一个有趣的问题：如果没有分区键或方案（如范围、列表或散列），Oracle 如何知道传入的记录应进入哪个分区？ 答案是：Oracle 不知道。下面的示例显示了如果您向表中插入一条记录时将发生的情况： SQL> insert into sales3 values (1,101,1); insert into sales3 values (1,101,1) * ERROR at line 1: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method 分区边界是未知的，因此应用程序必须在插入数据的同时使用分区敏感语法来提供该信息。您需要将该语句重新编写为： SQL> insert into sales3 partition (p1) values (1,101,1); 1 row created. 删除时，您不必提供分区敏感语法，但是记住，没有分区边界的概念。因此，当您发出如下语句时： SQL> delete sales3 where state_code = 1; Oracle 必须扫描所有分区，以查看该行所在的位置。为了避免出现这种情况，您应将其重新编写为： SQL> delete sales3 partition (p1) where state_code = 1; 更新也是一样的。这限制了搜索该记录的分区范围。 如果一个表不能以任何逻辑方式进行分区，那么系统分区将提供惊人的优势。它们使您能够利用分区的优势，同时允许开发人员自由决定记录将进入哪个分区。 单个分区的表空间传输 在 Oracle 数据库的早期版本中，您能够传输表空间，并且稍后能将其插入不同数据库或同一个数据库中。该过程涉及复制数据文件，因此它是跨数据库传输数据的最快方法。然而直到现在，您仍然无法传输单个分区的表空间，然后再将其插回数据库中。在 Oracle 数据库 11g 中，您可以这么做。 假设您有一个名为 SALES5 的表，它包括了几个名为 CT、NY 等的分区。 SQL> select partition_name, tablespace_name 2 from user_tab_partitions 3 where table_name = 'SALES5'; PARTITION_NAME TABLESPACE_NAME -------------- --------------- CT TS1 NY TS2 现在，您可以使用如下命令传输 CT 分区： $ expdp tables=scott.sales5:ct transportable=always directory=data_pump_dir dumpfile=p_ct.dmp Export:Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 16:05:40 Copyright (c) 2003, 2005, Oracle.All rights reserved. Username:/ as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta With the Partitioning, Oracle Label Security, OLAP, Data Mining and Oracle Database Vault options Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA tables=scott.sales5:ct transportable= always directory=data_pump_dir dumpfile=p_ct.dmp Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded **************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /home/oracle/oracle/admin/PROBE2/dpdump/p_ct.dmp ****************************************************************************** Datafiles required for transportable tablespace TS1: /home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:05:55 现在，可以将 p_ct.dmp 和 ts1_01.dmp 这两个文件传递到其他系统，然后尝试将其插入到数据库中。出于学习目的，我们尝试插入到相同的数据库中。首先，您需要删除该表，然后删除 ts1 表空间。 SQL> drop table scott.sales5; Table dropped. SQL> drop tablespace ts1 including contents; Tablespace dropped. 现在，将表空间插入到数据库中。但这里有个小问题：sales5 表不再存在，您最初只导出了一个分区 (ct)，并未导出整个表。那么如何只导入不存在的表的一个分区呢？ 在 Oracle 数据库 11g 中，Data Pump 导入中的一个名为 partition_options 的新命令行选项可实现此操作。如果您指定了值 departition，Data Pump 将根据导出的分区创建一个新表。该方法以这种方式“拆分”分区，因此它相应地被命名为取消分区。我们来看一下它的工作原理。 $ impdp partition_options=departition dumpfile=p_ct.dmp transport_datafiles='/home/oracle/oradata/PROBE2/PROBE2 /ts1_01.dbf' Import: Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 21:58:08 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.4.0 - Beta With the Partitioning, Oracle Label Security, OLAP, Data Mining and Oracle Database Vault options Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_04": /******** AS SYSDBA partition_options= departition dumpfile=p_ct.dmp transport_datafiles=/home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_04" successfully completed at 21:58:23 该 SQL 创建了一个名为 sales5_ct 的表，这个表只是由先前可传输的表空间导出的 SALES5 表的 ct 分区。如您所见，表名是原始表名和分区名的组合。可以通过检查 DBA_SEGMENTS 视图来确认段的存在。 SQL> select segment_name 2 from dba_segments 3 where tablespace_name = 'TS1'; SEGMENT_NAME ----------------- SALES5_CT 您可以使用单个分区的可传输表空间特性将一个表的单个分区插入到其他数据库中。插入之后，您可能希望执行交换分区操作，以便将其作为一个分区置于某个表上。 根据虚拟列分区 我们来看另一个常见问题。在名为 sales 的表中，您具有以下列： SQL> desc sales Name Null? Type ----------------------------------------- -------- ------ SALES_ID NOT NULL NUMBER CUST_ID NOT NULL NUMBER SALES_AMT NUMBER 假设您希望按照某个允许您进行清除的方案对该表进行分区，并且基于销售额进行存档。以下是销售的四个类别： 如果 sale_amt 为 且 cust_id 为 则 sale_category 为 0-10000 任何内容 LOW 10001-100000 0-100 LOW 10001-100000 101-200 MEDIUM 10001-100000 >200 HIGH 100001-1000000 0-100 MEDIUM 100001-1000000 101-200 HIGH 100001-1000000 >200 ULTRA >1000000 任何内容 ULTRA 您希望根据 sale_category 列对该表进行分区，但有一个问题：没有名为 sale_category 的列。这是您从 sale_amt 列派生的列。那么您如何对该表进行分区呢？ 在 Oracle 的早期版本中，您可能已经在表中插入了名为 sale_category 的新列，并使用一个触发器用表中所示的逻辑填充该列。但是由于触发器，这个新列的存在可能会导致其他性能影响。 在 Oracle 数据库 11g 中，一个称为虚拟列的新特性使您能够创建一个并不存储在表中的列，但在运行时将计算该列。您还可以根据该列进行分区。使用此特性，对该表进行分区就变得轻而易举。 create table sales ( sales_id number, cust_id number, sales_amt number, sale_category varchar2(6) generated always as ( case when sales_amt <= 10000 then 'LOW' when sales_amt > 10000 and sales_amt <= 100000 then case when cust_id < 101 then 'LOW' when cust_id between 101 and 200 then 'MEDIUM' else 'MEDIUM' end when sales_amt > 100000 and sales_amt <= 1000000 then case when cust_id < 101 then 'MEDIUM' when cust_id between 101 and 200 then 'HIGH' else 'ULTRA' end else 'ULTRA' end ) virtual ) partition by list (sale_category) ( partition p_low values ('LOW'), partition p_medium values ('MEDIUM'), partition p_high values ('HIGH'), partition p_ultra values ('ULTRA') ) 现在，如果您插入以下行： SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100); 1 row created. SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500); 1 row created. SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500); 1 row created. SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000); 1 row created. SQL> commit; Commit complete. 注意，您未输入 sale_category 的值。现在，如果您检查 p_low 中的记录，将看到正确的记录： SQL> select * from sales partition (p_low); SALES_ID CUST_ID SALES_AMT SALE_C ---------- ---------- ---------- ------ 1 1 100 LOW 该记录放在相应的分区中。 根据虚拟列分区使您能够创建对业务重要的分区，即使列本身是不存在的。这里，您已经对虚拟列使用了一个非常简单的计算方法，但它也可以如您希望的那样复杂。在这些情况下，根据虚拟列进行分区将变得更有价值。 Partition Advisor 在设计分区方案的过程中，您考虑的最主要的问题可能是选择分区方案和分区列的决定。这个任务最好留给进行广泛工作负载分析的经验丰富的专业人员，即使是他们可能也无法做出正确的决定。您可以在 Oracle 数据库 11g 中通过一个名为 Partition Advisor 的新顾问工具获得帮助，该工具可以分析数据、访问模式并建议分析方案。您可以在这一部分中了解有关该工具的更多信息。 结论 分区一直是 Oracle 数据库 11g 中最有用的工具之一，它将变得越来越有用： 引用分区允许您对同一数据库中的相关表进行均值分区，即使所有子表中都不存在相应的列。 间隔分区包括了非常有用的即发即弃功能 — 您定义一个时间间隔，Oracle 会永远进行维护。 将组合分区扩展到范围-范围、列表-范围、列表-散列和列表-列表可以提供更好的分区选择以及可管理性。 Data Pump 现在允许您传输和插入单个分区，该特性在存档和保留方面肯定非常有用。 最后，您可以设计最可能的分区策略，通过根据虚拟列进行分区来模拟业务流。 “分而治之”的策略从未提供过如此多的选择。可以将它们设想为另一套可以割下火鸡身上最好部分的锋利刀子！ 返回到“Oracle 数据库 11g：面向 DBA 和开发人员的重要特性”主页 Arup Nanda ([e-mail]) 是 Starwood Hotels and Resorts 的数据库系统经理，从事 Oracle 的 DBA 职业十多年，并且在 2003 年由《Oracle Magazine》 评选为“年度 DBA”。Arup 经常在 Oracle 相关活动中发表演讲，并在 Oracle 相关杂志上撰写文章，他是纽约 Oracle 用户群执行委员会的成员，并且是一位 Oracle ACE。他与其他人合作编写了《Oracle Privacy Security Auditing》(Rampant TechPress) 一书。