本教程演示如何使用 Oracle Database 11g 中新增的各种分区技术。
大约 60 分钟
本教程包括下列主题：
|
概述 |
|
前提条件 |
|
使用引用分区 |
|
使用间隔分区 |
|
重命名表分区 |
|
交换表分区 |
|
使用扩展的组合分区 |
|
使用基于虚拟列的分区 |
|
总结 |
将光标置于此图标上可以加载和查看本教程的所有屏幕截图。
（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）
注：此外，您还可以在下列步骤中将光标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图将其隐藏。
在 Oracle Database 11g 中，可以使用多种新技术对表数据进行分区，以提高数据的检索性能、优化数据结构。这些技术包括：
| 引用 | 通过从父表继承分区键（而非复制键列），允许在逻辑上均分具有父子关系的表 |
| 间隔 | 自动创建范围分区 |
| 扩展的组合 | 允许沿两维进行逻辑范围分区 |
| 虚拟列 | 允许在表的虚拟列上定义分区键列 |
开始本教程之前，您应该：
| 1. | 安装 Oracle Database 11g。 |
|
| 2. | 将 partition.zip 文件下载并解压缩到您的工作目录中。 |
|
| 3. | 打开一个终端窗口，执行以下命令： sqlplus / as sysdba @setup
|
|
引用分区通过从父表继承分区键（而非复制键列），从而可以在逻辑上均分具有父子关系的表。分区键通过现有的父子关系解析，由现行的主键或外键约束实施。逻辑相关性还可以自动级联分区维护操作，从而使应用程序开发更轻松且更不易出错。
执行以下步骤，进一步了解引用分区的用法：
| 1. |
打开一个终端窗口，以 SH 用户身份登录 SQL*Plus。执行 create_orders.sql 脚本，创建按范围分区的 ORDERS 表。 @create_orders
|
| 2. |
执行 create_order_items.sql 脚本，创建按引用分区的 ORDER_ITEMS 表。 @create_order_items
|
| 3. |
执行 query_dict_1.sql 脚本，查看按引用分区的 ORDER_ITEMS 表的相关信息。 @query_dict_1
|
| 4. |
执行 insert_orders.sql 脚本，将数据插入 ORDERS 表。 @insert_orders
|
| 5. |
执行 show_data_placement.sql 脚本，观察如何将数据置于分区中的同一位置。 @show_data_placement
|
| 6. |
要显示智能化分区联接，将 _parallel_broadcast_enabled 参数设置为 FALSE。执行以下命令： ALTER SESSION SET "_parallel_broadcast_enabled"=FALSE; 注： 以并行方式联接两个大型分区表时，并行智能化分区联接是非常高效的。本教程所使用数据集并不太大，但为了说明并行智能化分区联接计划的执行情况，将 _parallel_broadcast_enabled 参数设置为 false，并且在查询中使用散列联接提示。在您的环境中，您无需设置该参数或使用提示来查看并行智能化分区联接。
|
| 7. |
执行 show_plan.sql 脚本，查看有关智能化分区联接的信息。 @show_plan
|
| 8. |
执行 drop_partition.sql 脚本，从 ORDERS 表中删除 p_before_jan_2006 分区。 @drop_partition
|
| 9. |
执行 query_dict_2.sql 脚本，查看有关该表的信息。 @query_dict_2
|
| 10. |
执行 add_partition.sql 脚本，添加 p2007_01 分区。注：向父表中添加了两个分区，并且使用“dependent tables”子句为作为子表分区的第二个分区使用了不同的表空间分配。 @add_partition
|
| 11. |
执行 cleanup_1.sql 脚本，删除本例中创建的分区和表空间。 @cleanup_1
|
新的间隔分区策略是范围分区的扩展。间隔分区使范围分区的创建完全实现了自动化。也就是说，需要分区时就将创建新分区。通过定义间隔条件，当插入的数据超出所有范围分区时，数据库就知道应该创建新分区了。管理新分区的创建是一项繁琐的高重复性任务。对于可预测的小范围分区的添加（例如，添加每日的新分区）尤其如此。间隔分区可以通过按需创建分区来自动化该操作。至少必须先创建一个范围分区，然后才能使用间隔分区。
执行以下步骤，了解间隔分区的用法：
| 1. |
执行 create_newsales.sql 脚本，创建按间隔分区的 NEWSALES 表。如前所述，间隔分区是范围分区的扩展。对于间隔分区，分区键只能是表中某列的名称，并且必须是 NUMBER 或 DATE 数据类型。在下面的示例中，分区键将使用 DATE 数据类型。 @create_newsales
上面屏幕截图中的新 INTERVAL 子句定义了间隔。引进了一些日期函数来用时间表示间隔大小。 上边示例中定义的间隔为一天。创建表的同时也创建了范围分区。 使用 Interval 子句时还可以使用 STORE IN 可选子句，这个可选子句用于指定一个或多个表空间，数据库将使用循环算法把后续创建的间隔分区的数据存储在这些表空间中。表空间由间隔号确定。STORE IN 可选子句的语法如下： ... INTERVAL (NUMTODSINTERVAL(1,'DAY')) STORE IN (tbs1, tbs2, tbs3, tbs4) ... |
| 2. |
执行 query_dict_3.sql 脚本，查询 USER_TAB_PARTITIONS 字典视图，以了解有关新建的 NEWSALES 表的信息。该视图显示了分区级的分区信息、分区存储参数以及 DBMS_STATS 包或 ANALYZE 语句生成的分区统计信息。 @query_dict_3
|
| 3. |
执行 insert_newsales.sql 脚本，向 NEWSALES 表插入新数据。新插入的 10 行代表 1 月 1 日及之后的日期，因此，自动创建六个新分区。 @insert_newsales
|
| 4. |
执行 query_dict_4.sql 脚本，查看插入 10 行后系统自动创建的六个新分区的相关信息。 分区的上边界代表了过渡点，它下面的所有分区都位于范围部分中，它上面的所有后续分区（大于等于 January 1, 2005 的分区）都落入间隔部分中。新分区的名称是系统生成的，都以 sys_ 前缀开始。该示例中新建的间隔分区如下： SYS_P41：系统创建的第一个间隔分区，它包含新插入的 01-jan-2005 的 newsales。 SYS_P42：系统创建的第二个间隔分区，它包含新插入的 02-jan-2005 的 newsales。 SYS_P43：系统创建的第三个间隔分区，它包含新插入的 05-jan-2005 的 newsales。 SYS_P44：系统创建的第四个间隔分区，它包含新插入的 06-jan-2005 的 newsales。 SYS_P45：系统创建的第五个间隔分区，它包含新插入的 09-jan-2005 的 newsales。 SYS_P46：系统创建的第六个间隔分区，它包含新插入的 10-jan-2005 的 newsales。 @query_dict_4
|
| 5. |
执行 merge_partition.sql 脚本合并以下两个分区：SYS_P41 和 SYS_P42。合并这两个间隔分区时，系统自动将 SYS_P41 和 SYS_P42 的完整间隔转换为一个范围分区。过渡点移至该范围部分的末端。注意，新的 FOR 子句可以使您在不知道其名称的情况下识别特定分区。 @merge_partition
合并间隔分区会将过渡点移至两个合并的分区中的较高的上边界。这意味着按间隔分区的表的范围部分将扩展至两个合并的分区中的上边界。所有边界低于新合并分区边界的物化间隔分区都将自动转换为范围分区，其上边界均由它们间隔的上边界定义。 |
| 6. |
执行 query_dict_5.sql 脚本，查看有关合并分区的信息。新分区名称为 P_BEFORE_3_JAN_2005。 @query_dict_5
|
| 7. |
执行 create_hist_newsales.sql 脚本，创建一个按范围分区的表。 @create_hist_newsales
|
| 8. |
执行 query_dict_6.sql 脚本，查看有关新建分区的信息。 @query_dict_6
|
| 9. |
执行 insert_histnewsales_row_1.sql 脚本，在 HISTORICAL_NEWSALES 表中插入一行。插入操作将失败，因为该分区表目前还不是一个按间隔分区的表。 @insert_histnewsales_row_1
|
| 10. |
执行 alter_hist_newsales.sql 脚本，将分区表更改为按间隔分区的表。 @alter_hist_newsales
|
| 11. |
执行 insert_histnewsales_row_2.sql 脚本，再次尝试在该表中插入一行。 @insert_histnewsales_row_2
|
| 12. |
执行 query_dict_7.sql 脚本，查看有关分区的信息。系统新建的间隔分区命名为 SYS_P<##>。 @query_dict_7
|
您可以删除按间隔分区表中的间隔分区。该操作将只删除该间隔分区中的数据，仍然完整保留间隔分区的定义。如果数据要插入到刚删除的间隔分区，则数据库将再创建一个间隔分区。您还可以删除按间隔分区表中的范围分区。但是，您不能删除按间隔分区表的按范围分区部分中最高的范围分区。
通过在 SET INTERVAL 子句中使用空值，您可以禁止将来创建间隔分区，从而有效地恢复按范围分区的表。已创建的间隔分区根据它们当前的高值被转换为范围分区。您也可以使用 SET INTERVAL 子句将现有的按范围分区的表或按范围组合分区的表迁移到间隔分区或按间隔分区的表中。
您可以对表分区和索引分区及子分区重新命名，为其指定一个有意义的名称，代替在另一个维护操作中分配给该分区的默认系统名称。所有分区方法均支持标识分区的 FOR(value) 方法。您可以使用该方法将系统生成的分区名称重新命名为更有意义的名称。这在间隔分区或按间隔分区的表中非常有用。您可以针对按引用分区的主表和子表分别对分区和子分区重新命名。对主表的重命名操作不会层叠到子表。
您可以使用 ALTER TABLE ...RENAME PARTITION 语句重命名范围分区、散列分区或列表分区。在本部分中，您将重命名间隔分区部分创建的系统生成的分区。
| 1. | 要查看已有的分区，再次执行之前运行过的 query_dict_7.sql 脚本 @query_dict_7
|
| 2. | 可以执行以下命令对分区重命名： ALTER TABLE historical_newsales RENAME PARTITION sys_p<##> TO P_2006_p<##>; 其中 sys_p<##> 是第 1 步中查询到的系统生成的分区。
|
| 3. | 重新运行 query_dict_7.sql 脚本，可以看到该分区表名称已更改。 @query_dict_7
|
通过交换分区的数据段，您可以将分区（或子分区）转换为未分区的表，也可将未分区的表转换为分区表的一个分区（或子分区）。如果您有一个使用未分区表的应用程序，但您又想将其转换为分区表的分区，交换表分区功能将非常有用。例如，在数据仓储环境中，交换分区简化了向现有分区表快速加载新增数据的过程。通常，OLTP 及数据仓储环境都可以通过从分区表中交换旧数据分区而受益。数据从分区表中清除了，但没有真正删除，随后还可以分别进行归档。交换分区时，日志记录属性被保留。您可以有选择地指定是否也交换本地索引（INCLUDING INDEXES 子句），还可以指定行对适当的映射行是否有效（WITH VALIDATION 子句）。
您可以交换按间隔分区的表中的间隔分区。但是，您必须确保在交换分区前已经创建了要交换的间隔分区。可以通过锁定间隔分区来使数据库创建分区。下面的示例显示了 historical_newsales 表的分区交换，它是一个使用按月分区的间隔分区表。该示例显示了如何使用分区交换加载将 2007 年 1 月的数据添加到该表中。这里假设 historical_newsales 表中只有本地索引，并且 historical_newsales2 表中已经创建了对等的索引。执行以下步骤：
| 1. | 要创建另一个分区来放置交换的数据，执行 lock_hist_newsales.sql 脚本。 @lock_hist_newsales
|
| 2. | 重新运行 query_dict_7.sql 脚本，查看这个新分区。 @query_dict_7
|
| 3. | 现在，您将创建一个未分区的新表并插入一行数据。执行 create_hist_newsales2.sql 脚本。 @create_hist_newsales2
|
| 4. | 您希望在交换前查看 historical_newsales 表中的数据。执行以下命令： select * from historical_newsales;
|
| 5. | 现在，可以执行交换了。执行 exchange_hist_newsales.sql 脚本。 @exchange_hist_newsales
|
| 6. | 执行以下命令查看 historical_newsales 和 historical_newsales2 表中包含的内容。 select * from historical_newsales;
|
| 7. | 再次重新运行 query_dict_7.sql 脚本。注意，“02-JAN-07”的数据现在位于 historical_newsales 表中系统生成的 SYS_P118 分区中，因为它是 2008 年 1 月 1 日之前的数据。 @query_dict_7
|
您也可以交换按引用分区的表中的分区，但必须确保您引用的数据在父表的对应分区中是可用的。
您可以交换存在虚拟列的分区。为了成功进行包含虚拟列的分区表中的分区交换，必须在分区表的某个分区中创建一个与所有非虚拟列定义匹配的表。您不需要包括虚拟列定义，除非在虚拟列上定义了约束或索引。如果存在这种情况，为了匹配分区表的约束和索引定义，您必须包括虚拟列定义。这种情况也适用于基于虚拟列分区的表。
组合的范围-范围分区允许沿两个维进行逻辑范围分区；例如，按 ORDER_DATE 分区，再按 SHIPPING_DATE 划分范围子分区。组合的范围-范围分区提供了一个额外的建模策略，可以将业务需求映射到对象分区。
在本例中，与客户签订的服务级协议表明，订单将在下订单后的那个日历月交付。确定的订单类型包括：
| EARLY | 在下订单之后的下个月中旬之前交付的订单。这些订单很可能超出客户的预期。 |
| AGREED | 在下订单之后的那个日历月交付的订单（但不是 early 订单） |
| LATE | 从下订单之后的第二个日历月才开始交付的订单 |
执行以下步骤，创建按范围-范围组合分区的表以满足业务需求：
| 1. |
执行 create_shipments.sql 脚本，将 SHIPMENTS 表创建为按范围-范围组合分区的表（按 ORDER_DATE 和 SHIPDATE 分区）。 @create_shipments
|
| 2. |
使用 insert_shipments.sql 脚本在 SHIPMENTS 表中插入多行。 @insert_shipments
|
| 3. |
执行 count_shipments.sql 脚本，查看数据在 SHIPMENTS 表中的分布方式。 @count_shipments
|
您可以使用虚拟列分区对在表的虚拟列上定义的键列进行分区。 虚拟列通过求解表达式来定义。 虚拟列可以在创建或修改表时定义。通常，从逻辑上对对象进行分区的业务需求并不是以一对一的方式与现有列匹配。Oracle 分区功能已经得到增强，它允许在虚拟列上定义分区策略，因此可以实现更全面的业务需求匹配。
本例中的员工分类如下：
| Poor | 薪酬总额（薪水 + 奖金）低于 1000 |
| Not doing too bad | 薪酬在 1000 到 3000 之间 |
| On target | 薪酬为 3000 |
| Filthy rich | 薪酬 > 3000 |
执行以下步骤，了解基于虚拟列的分区的用法：
| 1. |
执行 create_employees.sql 脚本，创建包含虚拟列的 EMPLOYEES 表。 @create_employees
|
| 2. |
执行 insert_employees.sql 脚本，在 EMPLOYEES 表中插入多个行。 @insert_employees
|
| 3. |
执行 select_employees.sql 脚本，查询 EMPLOYEES 表。 @select_employees
. . .
|
| 4. |
执行 create_prod_ret.sql 脚本，创建包含虚拟列的 PRODUCT_RETURNS 表，虚拟列用于对该表进行分区。 @create_prod_ret
|
| 5. |
执行 insert_prod_ret.sql 脚本，在 PRODUCT_RETURNS 表中插入多行。 @insert_prod_ret
. . . |
| 6. |
执行 select_prod_return.sql 脚本，查询 PRODUCT_RETURNS 表。 @select_prod_return
|
| 7. |
执行 xplan_prod_ret.sql 脚本，为使用定义虚拟列的表达式的查询显示分区修剪。 @xplan_prod_ret
|
在本教程中，您学习了如何：
|
使用引用分区 | |
|
使用间隔分区 | |
|
重命名表分区 | |
|
交换表分区 | |
|
使用扩展的组合分区 | |
|
使用虚拟列分区 | |
将光标置于该图标上可以隐藏所有的屏幕截图。