更多分区选择，尽在Oracle 11g(ZT)

最新推荐文章于 2025-12-13 11:00:09 发布

原创最新推荐文章于 2025-12-13 11:00:09 发布 · 814 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #database #date #calendar #reference #table

dbms 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了Oracle Database 11g中的新分区特性，包括外键分区、interval分区、扩展的组合分区和虚拟列分区。这些特性有助于提高数据库性能，简化分区管理，并满足归档需求。

　分区是现代数据库设计中一个功能非常强大的功能，并且无需改动原有数据库管理逻辑设计。Oracle Database 11g中，分区功能的选择得到了更进一步的扩展，引入了外键分区和interval分区，扩展的组合分区和虚拟列分区。本文教你学习何时以及如何使用Oracle Database 11g里的新分区方案……

　　【IT专家网独家】Oracle Database 11g的分区方案增加了更多的功能，包括确定新的组合分区方法的功能、选择分区区间、指定外键来延用主表格的分区键以及虚拟列分区。

　　外键分区

　　假设有一个叫Acme Hotels的企业，需要你为它建立一个酒店房间预定系统。一个名为RES的核心表格存储房间预定信息。下面是RES表格中的列：

　　RES_ID NUMBER

　　RES_DATE DATE

　　HOTEL_ID NUMBER(3)

　　GUEST_ID NUMBER

　　Res_id，res_date，hotel_id和guest_id列分别指预定房间的唯一ID号码、预定房间的日期、预定房间所在酒店的唯一ID号码以及预定房间的客人的唯一标识符。因为大多数用户都通过res_date列进行查询，而且该列也用于确定分区的记录，所以通过该列对表格进行每季度的范围分区，如Listing 1所示。

　　对房间预定记录可以有很多种处理方法，而每一条记录都以一个唯一的trans_id来识别。因为TRANS是RES的一个子表格，所以在TRANS res_id列中有一个指向RES表格的外键。因为TRANS表格有与RES表格相同的归档需求，我们会想要对它进行完全相同的分区方法——通过res_date列进行每季度的范围分区。

　　Code Listing 1：创建RES表格　　

create table res (
　　res_id number primary key not null,
　　res_date date,
　　hotel_id number(3),
　　guest_id number
　　)
　　partition by range (res_date) (
　　partition p1 values less than (to_date('01/01/2007','mm/dd/yyyy')),
　　partition p2 values less than (to_date('04/01/2007','mm/dd/yyyy')),
　　partition p3 values less than (to_date('07/01/2007','mm/dd/yyyy')),
　　partition p4 values less than (to_date('10/01/2007','mm/dd/yyyy')),
　　partition pm values less than (maxvalue)
　　);

　　下一步，需要创建一个表格来存储由预订房间而产生的处理。这个名为TRANS的表格如下：　　

TRANS_ID NUMBER
　　RES_ID NUMBER
　　TRANS_DATE DATE
　　AMT NUMBER

　　但是有一个问题：TRANS表格本身没有res_date列，所以你如何才能通过一个不存在的列进行分区呢?

　　进入Oracle Database 11g的新功能。

　　Oracle Database 11g有一个非常有用的新功能：外键分区。不同于在Listing 1的RES中那样在TRANS表格中添加一个res_date列并指定分区从句，你可以在Oracle Database 11g中指定一个简单的PARTITION BY REFERENCE从句，如Listing 2中所示。你需要传递外键约束名来指示外键如何创建。例如，在这个例子中，你通过TRANS创建外键分区，外键为FK_TRANS_01，指向RES主表格。即使分区列不存在与TRANS表格，TRANS表格也会延用RES表格的分区方案。外键分区主要是指示Oracle Database使子表格(在本例子中为TRANS)与主表格(RES)具备完全相同的分区属性。

　　Code Listing 2：使用外键分区创建TRANS表格　　

create table trans ( 
  trans_id     number not null, 
  res_id        number not null, 
  trans_date  date not null, 
  amt           number, 
  constraint fk_trans_01 
    foreign key (res_id) 
    references res 
) 
partition by reference 
   (fk_trans_01);

通过查询USER_PART_TABLES数据字典视图你可以看到外键分区是如何来创建的，如Listing 3所示。partitioning_type列显示分区方案类型;在Listing 3中，类型为REFERENCE，而当类型为REFERENCE时，ref_ptn_constraint_name列显示外键约束名为FK_TRANS_01。

　　Code Listing 3：检查外键分区中的外键　　

SQL> select table_name, partitioning_type, ref_ptn_constraint_name
  2    from user_part_tables
  3    where table_name in ('RES', 'TRANS');

 TABLE_NAME         PARTITIONING_TYP      REF_PTN_CONSTRAINT_NAME
------------        ------------------    -----------------------   
 RES                RANGE
TRANS               REFERENCE                    FK_TRANS_01

　　要查找分区的边界，可以查询USER_TAB_PARTITIONS数据字典视图，如Listing 4所示。注意在HIGH_VALUE 列中所显示的TRANS子表格的分区边界值都为null。这表示该表格的边界跟RES主表格的分区边界是相同的，因此不再另外界定。

　　Code Listing 4：检查表格的分区　　

SQL> select table_name, partition_name, high_value
  2    from user_tab_partitions
  3    where table_name in ('RES', 'TRANS');

TABLE_NAME   PARTITION_NAME       HIGH_VALUE
----------   --------------       ---------------------------------------- 
RES          P1                   TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          P2                   TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          P3                   TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          P4                   TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-M
                                  M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
RES          PM                         MAXVALUE
TRANS        P1
TRANS        P2
TRANS        P3
TRANS        P4
TRANS        PM

　　利用外键分区，即使分区键列不存在于子表格中，你也可以对任何子表格进行与主表格相同的分区，从而提高性能并满足归档需求。

　　假设你取消RES表格的分区功能，所有的子表格分区也会相应自动取消。当你在一个主表格中添加分区，也会自动在子表格中添加分区。相同地，如果你把一个主表格的PM分区进行分割，分离出一个新的分区，TRANS表格的PM分区也会从相同的点一分为二。以下介绍你如何可以在value401上分离RES表格的PM分区：　　

alter table res
　　split partition pm
　　at (401)
　　into (partition p4, partition pm);

　　现在如果你查询TRANS表格的分区，你会看到　　

select partition_name
　　from user_tab_partitions
　　where table_name = 'TRANS';
　　PARTITION_NAME
　　---------------
　　P1
　　P2
　　P3
　　P4
　　SYS_P45

　　请注意当PM分区被分割，创建出新的分区SYS_P45。(SYS_P45的名字拥有SYS_P的序列好前缀。)你可能会想要更改它的名字与RES的PM分区中的命名一致。要重命名，可以使用以下SQL：　　

alter table trans rename partition
　　SYS_P45 to PM;

　　注意如果子表格有任何本地分区索引，它们的分区也会相应地被一分为二。外键分区可以为主表格指定一个适当的分区方案而不需要对子表格再进行分区处理。这样不仅可以大大地简化分区的管理，还可以在不希望把分区键列包含在子表格中的情况下实现主表格和子表格具备相同的分区属性——或者可以实现在不允许在表格中添加新列的预先包装的应用软件中把分区键包含在子表格中。

Interval分区

　　如果一个INSERT语句的新增记录中含有分区键列的一个尚未经分区的值，会有什么结果呢?INSERT语句会无效。但是，如果你已经通过范围分区方案(如Listing 1中所示)的MAXVALUE从句指定了一个默认分区，或为列表分区的表格指定DEFAULT分区，那么INSERT就不会无效，但是新纪录会进入默认分区，使分区的目的变得无意义。所以你必须保证你的分区对所有可能添加到表格中的记录有效;你必须谨慎地确定所有可能的值，然后在添加数据前创建分区。例如，假设RES表格是通过res_id列进行分区，分区值范围1-100，101-200和201-300，这样你已经确定分区达res_id=300。你可以最大res_id列值进行监控，在该值接近300的时候，你可以为301-400的值创建一个新的p4分区。

　　说得容易，做起来难。如果你忘记了添加新分区，res_id值为301的新记录就会要么插入无效，要么进入默认分区。如果能够自动创建新分区那不是很方便吗?

　　Oracle Database 11g可以实现这一点：自动创建新分区。要实现这点，需要运用interval分区方法，如下：　　

create table res (
　　res_id number not null,
　　res_date date,
　　hotel_id number(3),
　　guest_id number
　　)
　　partition by range (res_id)
　　interval (100) store in (users)
　　(
　　partition p1 values less than (101)
　　);

　　这个脚本为res_id列值为1-100的记录创建了名为p1的分区。当插入res_id列值小于101的记录时，记录会进入p1分区，但是当插入res_id列值大于或等于101的记录是，Oracle Database 11g会为它创建一个以系统生成命名的新分区。例如，假设你插入一个res_id列值为901的记录，如下：

　　insert into res values (901,sysdate,1,1);

　　现在看执行如Listing 5中所示的查询来检查表格中指定的分区。注意分区SYS_P82是如何自动创建来储存超出了P1分区分界值的新增901值。这时，如果你插入另一个res_id值为301的记录，SYS_P82分区就会一分为二，如Lisitng 5下半部分的结果所示。

　　Code Listing 5：检查interval分区方案生成的分区　　

SQL> select partition_position, partition_name, high_value
  2    from user_tab_partitions
  3    where table_name = 'RES'
  4    order by 1;

PARTITION_POSITION    PARTITION_NAME     HIGH_VALUE
--------------------  --------------     ---------------   
1                     P1                  101
2                     SYS_P82            1001

The result of the same query after inserting res_id = 301:

PARTITION_POSITION    PARTITION_NAME     HIGH_VALUE
--------------------  --------------     ---------------  
1                     P1                  101
2                     SYS_P83             401
3                     SYS_P82            1001

　　你可能会希望通过名字来处理分区，比如当你想要截断某个分区的时候。由于分区名是在运行时间生成的，你不能提前知道，那如何才能处理表格中某个分区呢?例如，假设你想要截断含有res_id值为901的分区，但是你不知道这个分区的名字。要找出这个分区名的其中一个办法是查询USER_TAB_PARTITIONS数据字典视图，如Listing 4和5中所示，但是还有一个更简单的方法，就是使用Oracle Database 11g的延伸分区存取语法。你可以通过以下SQL来截断分区：　　

alter table res truncate partition
　　for (901);

　　你可以在针对任何一种分区表格使用的任何直接分区存取SQL语句中使用FOR(值)语法，不仅仅是针对interval分区表格。

　　在interval分区中，你在表格创建脚本中指定的第一个分区是在用户的默认tablespace中创建的，但是之后的分区则是在数据库的默认tablespace中创建的。即使你更改表格的属性来改变默认tablespace，新的分区依然会进入数据库的默认tablespace里。要强制它们进入不同的tablespace，你需要在创建表格是指定另外一个从句。指定RESDATA1和RESDATA2两个tablespace所谓所有新分区的储存场所，在INTERVAL从句之后加上STORE IN从句，如下：

　　interval (100) store in (resdata1,resdata2)

　　现在新的分区会循环进入以上两个tablespace。

你也可以使用时间戳记作为分区间隔。这个方法在创建包含日期范围和按每月分区的表格是非常方便。要实现这点，需要把INTERVAL从句写成如下：

　　interval (numtoyminterval(1, 'MONTH'))

　　扩展的组合分区方案

　　在我之前发表的文章中，我还介绍了另一个很重要的功能：组合分区。组合分区是指把分区进行二次分区。直到Oracle Database 10g Release 2为止，你只能把范围分区进一步分为散列分区或列表分区。虽然这已经足以满足绝大部分的分区需求，但是有一些情况下，进行二次范围分区可以有很大的帮助。例如，你在前文看到的酒店房间预定表格(RES)，假设酒店ID显示酒店的类型：值在1-100之间是指五星级酒店，值在101-200之间是指四星级酒店，如此类推。由于在同一个级别中常常会进行收入比较分析，用户往往只会选择某一个特定范围的酒店ID，比如1-100。因此，如果通过hotel_id列把RES表格进行范围分区很合理。然而，用户还会经常选择最新的数据，你可能会想要把旧的数据存储在更便宜的磁盘中来降低存储成本。因此可能会有人想要通过res_date列来对表格进行范围分区。两个分区方案都是很吸引人的选择——你应该选择哪个呢?

　　Code Listing 6：组合范围-范围分区　　

create table res (
　　res_id number not null,
　　res_date date,
　　hotel_id number(3),
　　guest_id number
　　)
　　partition by range (res_date)
　　interval (numtoyminterval(1,'MONTH')) store in (example)
　　subpartition by range (hotel_id)
　　subpartition template
　　(
　　subpartition s1 values less than (101),
　　subpartition s2 values less than (201),
　　subpartition s3 values less than (301),
　　subpartition s4 values less than (401),
　　subpartition sm values less than (maxvalue)
　　)
　　(
　　partition p1 values less than (to_date('01-FEB-2007','DD-MON-YYYY'))
　　);

　　为什么不两个都使用呢?在Oracle Database 11g中，除了已经有的组合范围-散列分区和组合范围-列表分区之外，你还可以实现以下组合分区方法：组合范围-范围、组合列表-范围、组合列表-散列、组合列表-列表等分区方案。在这个例子中，你可以通过res_date列创建范围分区，然后再通过hotel_id列创建子分区，如Listing 6所示。注意注意我特意选择了一个interval方案来做分区。我本来可以选择很清楚的分区名和高的值来说明，但是我想要说明你可以如何使用interval分区来简化维护工作。我本来也可以选择相反的分区顺序——我可以先进行hotel_id分区然后再进行res_date子分区。要查找创建的自分区，你可以从USER_TAB_SUBPARTITIONS数据字典视图中选择如下：

SQL> select partition_name,
2    subpartition_name
3    from user_tab_subpartitions
4    where table_name = 'RES';

PARTITION_NAME     SUBPARTITION_NAME
------------------ -----------------
P1                 P1_SM
P1                 P1_S4
P1                 P1_S3
P1                 P1_S2
P1                 P1_S1
SYS_P106           SYS_SUBP105
SYS_P106           SYS_SUBP104
SYS_P106           SYS_SUBP103
SYS_P106           SYS_SUBP102
SYS_P106           SYS_SUBP101

虚拟列分区

　　Oracle Database 11g还提供了一个新的分区功能，成为虚拟列分区。一个虚拟列事实上并不存储在表格中，但是每次运行时对其进行存取都会对其进行计算并呈现给用户。你同样可以通过该虚拟列对数据进行分区。以RES表格为例，就如先前所说的，hotel_id列显示酒店的星级：1-100表示五星级、101-200表示四星级，如此类推。假设你想要让hotel_id列的值成为RES表格的一部份。最简单的办法就是创建一个列——STAR_RATING——然后通过该列对表格进行列表分区。但是你要如何向该列输入数据呢?一个办法是重写应用程序，但这不是一个好办法。另外一个办法就是使用一个触发器使该列自动更新。在Oracle Database 11g中，你可以不必向该列输入真实的数值，而是使用虚拟列。Listing 7显示表格创建脚本。

　　Code Listing 7：虚拟列分区　　

create table res (
　　res_id number not null,
　　res_date date,
　　hotel_id number(3),
　　guest_id number,
　　star_rating number(1)
　　generated always as (
　　substr(hotel_id,1,1)
　　) virtual
　　)
　　partition by list (star_rating)
　　(
　　partition star5 values (5),
　　partition star4 values (4),
　　partition star3 values (3),
　　partition others values (default)
　　)

　　这样，用户无需考虑如何去对酒店星级级别数据进行解码，你可以创建一个包含了准确信息的列并根据该列创建了一个有效的分区方案。你完全不用再写触发器代码就可以实现这一切。

　　结论

　　分区是现代数据库设计中一个功能非常强大的功能，并且无需改动原有数据库管理逻辑设计。绝大多数情况下，它可以帮助实现数据存储、备份、归档和修复中看起来相矛盾的目标——完全无需改动应用程序。Oracle Database 11g中，分区功能的选择得到了更进一步的扩展，引入了外键分区和interval分区，扩展的组合分区和虚拟列分区。