Oracle11g新特性:引用分区(reference partitioning)

引用分区(reference partitioning)是Oracle Database 11g Release 1及以上版本的一个新特性。它处理的是父/子对等分区的问题。也就是说，要以某种方式对子表分区，使得各个子表分区分别与一个你表分区存在一对一的关系。在某些情况下这很重要，例如假设有一个数据仓库，你希望保证一定数量的数据在线(例如最近5年的ORDER信息)，而且要确保相关联的子表数据(ORDER_LINE_ITEMS数据)也在线。在这个经典的例子中，ORDERS表通常有一个ORDER_DATE列，所以可以很容易地按月分区，这也有利于保证最近5年的数据在线。随着时间推移，只需加载下一个朋的分区，并删除最老的分区。不过，考虑ORDER_LINE_ITEMS表时会看到存在一个问题。它没有ORDER_DATE列，而且ORDER_LINE_ITEMS表中根本没法有可以据以分区的列，因此无法帮助清除老信息或加载新信息。

过去，在引用分区出现之前，开发人员必须对数据逆规范化(denormalize)，具体做法是：从父表ORDERS将ORDER_DATE属性复制到子表ORDER_LINE_ITEMS。这会引入冗余数据，相应地带来数据冗余存在的一系列常见问题，比如存储开销增加、数据加载资源增加、级联更新问题(如果修改父表，还必须确保更新父表数据的所有副本)，等等。另外，如果在数据库中启用了外键约束(而且确实应当启用外键约束)，会发现无法截除或删除父表中原来的分区。例如，下面来创建传统的ORDERS和ORDER_LINE_ITEMS表。先看ORDERS表：

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>orders</code>

<code> </code><code>3 </code><code>order</code><code># number </code><code>primary</code> <code>key</code><code>,</code>

<code> </code><code>4 order_date </code><code>date</code> <code>NOT</code> <code>NULL</code><code>,</code>

<code> </code><code>5 data varchar2(30)</code>

<code> </code><code>7 enable row movement</code>

<code> </code><code>8 PARTITION </code><code>BY</code> <code>RANGE (order_date)</code>

<code> </code><code>10 PARTITION part_2016 </code><code>VALUES</code> <code>LESS THAN (to_date(</code><code>'01-01-2017'</code><code>,</code><code>'dd-mm-yyyy'</code><code>)) ,</code>

<code> </code><code>11 PARTITION part_2017 </code><code>VALUES</code> <code>LESS THAN (to_date(</code><code>'01-01-2018'</code><code>,</code><code>'dd-mm-yyyy'</code><code>)) </code>

<code>Table</code> <code>created.</code>

<code>zx@ORCL></code><code>insert</code> <code>into</code> <code>orders </code><code>values</code>

<code>1 row created.</code>

<code>zx@ORCL></code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

现在来创建ORDER_LINE_ITEMS表，并插入一些数据指向ORDERS表：

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>order_line_items</code>

<code> </code><code>3 </code><code>order</code><code># number,</code>

<code> </code><code>4 line# number,</code>

<code> </code><code>5 order_date </code><code>date</code><code>, </code><code>-- manually copied from ORDERS!</code>

<code> </code><code>6 data varchar2(30),</code>

<code> </code><code>7 </code><code>constraint</code> <code>c1_pk </code><code>primary</code> <code>key</code><code>(</code><code>order</code><code>#,line#),</code>

<code> </code><code>8 </code><code>constraint</code> <code>c1_fk_p </code><code>foreign</code> <code>key</code><code>(</code><code>order</code><code>#) </code><code>references</code> <code>orders</code>

<code> </code><code>10 enable row movement</code>

<code> </code><code>11 PARTITION </code><code>BY</code> <code>RANGE (order_date)</code>

<code> </code><code>13 PARTITION part_2016 </code><code>VALUES</code> <code>LESS THAN (to_date(</code><code>'01-01-2017'</code><code>,</code><code>'dd-mm-yyyy'</code><code>)) ,</code>

<code> </code><code>14 PARTITION part_2017 </code><code>VALUES</code> <code>LESS THAN (to_date(</code><code>'01-01-2018'</code><code>,</code><code>'dd-mm-yyyy'</code><code>)) </code>

<code>zx@ORCL></code><code>insert</code> <code>into</code> <code>order_line_items </code><code>values</code>

现在如果要删除包含2016年数据的ORDER_LINE_ITEMS分区，也可以删除对应2016年的ORDERS分区而不会违反引用完整性约束。尽管我们都很清楚这一点，但数据库并不知道：

<code>zx@ORCL></code><code>alter</code> <code>table</code> <code>order_line_items </code><code>drop</code> <code>partition part_2016;</code>

<code>Table</code> <code>altered.</code>

<code>zx@ORCL></code><code>alter</code> <code>table</code> <code>orders </code><code>drop</code> <code>partition part_2016;</code>

<code>alter</code> <code>table</code> <code>orders </code><code>drop</code> <code>partition part_2016</code>

<code>ERROR </code><code>at</code> <code>line 1:</code>

<code>ORA-02266: </code><code>unique</code><code>/</code><code>primary</code> <code>keys </code><code>in</code> <code>table</code> <code>referenced </code><code>by</code> <code>enabled </code><code>foreign</code> <code>keys</code>

所以，对数据逆规范化的做活很笨拙，会耗费资源，而且可能破坏数据的完整性。不仅如此，它还会妨碍管理分区表时经常需要做的一项工作：清除老信息。

下面来看引用分区。采用引用分区，子表会继承父表的分区机制，而不必对分区键逆规范化，而且更重要的是，它会让数据库了解这个子表与父表之间存在对等分区特点。也就是说，截除或删除生意人子表分区时，也能删除或截除父表分区。

要重新实现前面的例子，语法很简单，如下所示，这里将重用现胡的你表ORDERS，只需要截除这个表：

<code>zx@ORCL></code><code>drop</code> <code>table</code> <code>order_line_items </code><code>cascade</code> <code>constraints;</code>

<code>Table</code> <code>dropped.</code>

<code>zx@ORCL></code><code>truncate</code> <code>table</code> <code>orders;</code>

<code>Table</code> <code>truncated.</code>

创建一个新的子表：

<code> </code><code>3 </code><code>order</code><code># number </code><code>NOT</code> <code>NULL</code><code>,</code>

<code> </code><code>4 line# number </code><code>NOT</code> <code>NULL</code><code>,</code>

<code> </code><code>5 data varchar2(30),</code>

<code> </code><code>6 </code><code>constraint</code> <code>c1_pk </code><code>primary</code> <code>key</code><code>(</code><code>order</code><code>#,line#),</code>

<code> </code><code>7 </code><code>constraint</code> <code>c1_fk_p </code><code>foreign</code> <code>key</code><code>(</code><code>order</code><code>#) </code><code>references</code> <code>orders</code>

<code> </code><code>9 enable row movement</code>

<code> </code><code>10 partition </code><code>by</code> <code>reference(c1_fk_p)</code>

神奇之处就在CREATE TABLE语句的第10行。在这里，我们将区间分区语句替换为PARTITION BY REFERENCE。

这允许我们指定要使用的外键约束，从而发现分区机制。在这里可以看到外键指向ORDERS表——数据库读取ORDERS表的结构，并发现它有两个分区。因此，子表会有两个分区。实际上，如果现在查询数据字典可以得到：

<code>zx@ORCL></code><code>set</code> <code>linesize 200</code>

<code>zx@ORCL>col </code><code>table</code> <code>for</code> <code>a20</code>

<code>zx@ORCL>col partition_name </code><code>for</code> <code>a20</code>

<code>zx@ORCL></code><code>select</code> <code>table_name, partition_name</code>

<code> </code><code>2 </code><code>from</code> <code>user_tab_partitions</code>

<code> </code><code>3 </code><code>where</code> <code>table_name </code><code>in</code> <code>( </code><code>'ORDERS'</code><code>, </code><code>'ORDER_LINE_ITEMS'</code> <code>)</code>

<code> </code><code>4 </code><code>order</code> <code>by</code> <code>table_name, partition_name</code>

<code>TABLE_NAME PARTITION_NAME</code>

<code>ORDERS PART_2016</code>

<code>ORDERS PART_2017</code>

<code>ORDER_LINE_ITEMS PART_2016</code>

<code>ORDER_LINE_ITEMS PART_2017</code>

可以看到两个表的结构完全相同。另外，由于数据库知道这两个表是相关联的，可以删除父表分区，并让它自动清除相关的子表分区(因为子表从父表继承而来，所以父表分区结构的任何调整都会向下级联传递到子表分区)：

<code>zx@ORCL></code><code>alter</code> <code>table</code> <code>orders </code><code>drop</code> <code>partition part_2016 </code><code>update</code> <code>global</code> <code>indexes;</code>

因此，之前不允许完成的DROP现在则是完全允许的，它会自动级联传递到子表。另外如果使用ADD增加一个分区：

<code>zx@ORCL></code><code>alter</code> <code>table</code> <code>orders </code><code>add</code> <code>partition</code>

<code> </code><code>2 part_2018 </code><code>values</code> <code>less than</code>

<code>ORDERS PART_2018</code>

<code>ORDER_LINE_ITEMS PART_2018</code>

可以看到，这个操作也会向下级联传递。父表与子表之间存在一种一对一的关系。

参考《ORACLE DATABASE 9I10G11G编程艺术》

本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1893911，如需转载请自行联系原作者

Oracle11g新特性:引用分区(reference partitioning)

继续阅读

Testlink安装部署之XAMPP

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述