11g新特性 ——更加灵活的分区策略
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
前不久，曾经接手一个性能调优案例：这是一个报表系统，其基础数据主要存储于三张表中。表的大小已经很大了，最大一张接近100G。在生成报表时需要长时间才能返回结果，一些online查询甚至经常timeout。表中存储的是2万多个公司的数据，报表的生成也是以公司为单位的，因此，这一调优方案的思路比较明确：将表按公司分区。但是，这中间却存在一些麻烦：每个公司的数据并不是均衡的。其中近200家公司属于VIP用户，他们的数据量最大，每个公司差不多是十几万到几十万的数据量，其总量占了全部数据的30%左右；而其它非VIP用户的数据基本上每个都在1万以内。而我们的主要目标就是要优先保证VIP用户获取到最佳的性能（由于其数据量，当前最大的性能问题恰恰就出在这些VIP用户上）。因此，我们提出了2中分区方案：
- 基于Company Id的Hash分区；
- 基于Company Id的List分区；
但是，这两种方案各有优缺点：
对于Hash分区，分区的大小更加均衡，因而性能也更加均衡。但是，可能出现一些无法控制的极端现象：Hash分区仅仅是对Company Id使用Hash函数进行分组，它能做到每个分区分配基本相当数量的Company Id，但是每个Company Id对应的数据量并不考虑在内，因此可能出现某些分区集中的都是VIP数据或者都是非VIP数据，造成分区过大或过小；另外一个缺点就是我们很难直接干预某个公司的性能。例如，可能有某个非VIP用户成为了VIP用户，其数据量激增，它又正好处于一个大的分区上，这时，我们很难将其从这个分区剥离出来，除非它所在分区正好出在一个即将分裂的分区上。
对于List分区，VIP用户的性能能够得到保证。我们可以将每个VIP用户单独存储在一个分区上，但是，不可能将非VIP用户单独存储开（不仅增加维护难度，且增加整个表的大小），只能将非VIP用户存储在几个分区上。但是这样还是造成DDL语句非常复杂，并且非VIP的分区很大（每个都在10G左右，而VIP分区最大才200M）。
由于List分区更加接近我们的优化目的，最终还是采用了List分区。
其实，期间我们曾经考虑过使用复合分区。在10g中（我们的生产库是10g），仅支持2种复合分区：Range-List和Range-Hash。我们的解决方案是：为表增加一个数字类型的ID字段，VIP用户对应的数字大于100,000，非VIP用户的ID小于100,000。每个VIP用户被单独放置在一个Range分区中，所有非VIP用户被放置在一个Range分区中，然后再对非VIP分区通过Hash划分子分区。这样，即能保证VIP用户的性能，也能均衡非VIP用户的性能。但是，由于这种方案需要增加一个非业务的字段，以及其它一些原因，最终被否决了。
到11g中，Oracle的分区策略更加灵活了。首先，11g支持更多方式的组合分区，除10g支持的两种之外，还支持Range-Range、List-Range、List-List、List-Hash的组合分区策略。对于我们上述这个案例，就可以通过List-Hash的组合分区来解决。以下就是一个List-Hash分区的演示：
SQL代码
- SQL> create table par_test
- 2 partition by list (owner)
- 3 subpartition by hash (owner)
- 4 store in (example)
- 5 (partition p1 values ('SYS'),
- 6 partition p2 values ('PUBLIC'),
- 7 partition def values (default)
- 8 subpartitions 4
- 9 )
- 10 as select * from dba_objects
- 11 /
- Table created.
- SQL> analyze table par_test compute statistics;
- Table analyzed.
- SQL> select partition_name, subpartition_name, num_rows, blocks from dba_tab_subpartitions
- 2 where table_name = 'PAR_TEST';
- PARTITION_NAME SUBPARTITION_NAME NUM_ROWS BLOCKS
- ------------------------------ ------------------------------ ---------- ----------
- P2 SYS_SUBP154 26604 434
- DEF SYS_SUBP158 4529 70
- DEF SYS_SUBP157 2783 45
- DEF SYS_SUBP156 2422 39
- DEF SYS_SUBP155 2854 47
- P1 SYS_SUBP153 29770 437
- 6 rows selected.
顺便再提一下11g新增的其他分区策略。
针对Range Partition，11g有了一种更加灵活的方式：Interval Partition。例如，我们一些分区表是依赖于时间做的范围分区：每个月的数据存放到一个分区中。随着数据的增长，还需要有一个作业来增加新的分区以满足上述策略。而在11g中，通过Interval Parition，就无需这中人为的维护作业了，Oracle会为新的数据自动增加分区：
SQL代码
- SQL> create table par_test2 (a number, b date)
- 2 partition by range (b)
- 3 interval (numtoyminterval(1,'MONTH'))
- 4 store in (example)
- 5 (
- 6 partition values less than (to_date('2009-09-01','yyyy-mm-dd'))
- 7 )
- 8 ;
- Table created.
- SQL> insert into par_test2 values(1, sysdate);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select partition_name, high_value from dba_tab_partitions
- 2 where table_name = 'PAR_TEST2';
- PARTITION_NAME HIGH_VALUE
- ------------------------------ --------------------------------------------------------------------------------
- SYS_P164 TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA '
- SQL> insert into par_test2 values(1, to_date('2009-10-01','yyyy-mm-dd'));
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select partition_name, high_value from dba_tab_partitions
- 2 where table_name = 'PAR_TEST2';
- PARTITION_NAME HIGH_VALUE
- ------------------------------ --------------------------------------------------------------------------------
- SYS_P164 TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA '
- SYS_P165 TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA '
- SQL> insert into par_test2 values(1, sysdate - interval '1' MONTH);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from par_test2;
- A B
- ---------- ---------
- 1 31-AUG-09
- 1 31-JUL-09
- 1 01-OCT-09
- SQL> select partition_name, high_value from dba_tab_partitions
- 2 where table_name = 'PAR_TEST2';
- PARTITION_NAME HIGH_VALUE
- ------------------------------ --------------------------------------------------------------------------------
- SYS_P164 TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA '
- SYS_P165 TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA '
从上述例子可以注意到，由于Range Partition的表达式的比较操作符是Less Than，因此，Interval Partition只会对超出（分区最大Partition Key值+Interval值）的数据创建新分区。同理，在指定Interval分区时，就不能再指定less than (MAXVALUE)了，否则Interval分区就没有意义了。
11g中还引入了一种新的分区策略：关联分区。在10g和之前版本，我们在做分区表时可能会遇到这样的问题：一个主表和多个子表都需要做分区，而分区所基于的关键值是只存在与主表中的一个字段，这时，我们就需要将这个字段冗余到子表当中去才能实现主表、子表采用相同的分区策略。11g中，这个问题可以通过关联分区解决了：
SQL代码
- SQL> create table par_main (pid number primary key, crt_date date)
- 2 partition by range (crt_date)
- 3 (
- 4 partition values less than (to_date('2009-09-01','yyyy-mm-dd')),
- 5 partition values less than (maxvalue)
- 6 );
- Table created.
- SQL> create table par_child (
- 2 cid number primary key,
- 3 pid number not null,
- 4 constraint par_main_fk foreign key (pid) references par_main(pid)
- 5 )
- 6 partition by reference (par_main_fk);
- Table created.
关联分区中要注意一点：关联分区中，父表不能为Interval分区。
11g，还新增了以虚字段（Virtual Column）为分区键的分区方式。以下例子中，store就是一个虚列，它的数值并没有实际存储在表中，而是由其它两个字段buy和sell计算得出，我们可以以它作为分区键建立分区：
SQL代码
- SQL> create table par_vc(
- 2 itemid number,
- 3 buy number,
- 4 sell number,
- 5 store number as (buy - sell)
- 6 )
- 7 partition by range (store)
- 8 (
- 9 partition values less than (1000),
- 10 partition values less than (2000),
- 11 partition values less than (maxvalue)
- 12 );
- Table created.
11g中引入的最后一种特殊的分区是system partition。对于普通分区，必须有一个或多个字段做为分区键来建立分区，而system分区就没有这种要求——仅仅是将表的数据分别存储在多个段中，而你在插入数据时，需要指定数据存储在哪个分区上：
SQL代码
- SQL> create table par_sys (a number, b varchar2(10))
- 2 partition by system
- 3 (
- 4 partition p1,
- 5 partition p2
- 6 );
- Table created.
- SQL> insert into par_sys values (1, 'a');
- insert into par_sys values (1, 'a')
- *
- 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 par_sys partition(p1) values (1, 'a');
- 1 row created.
从上面的例子可以注意到：在插入数据时，如果没有指定分区就会抛错。
--- Fuyuncat Mark ---