HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

11g新特性 ——更加灵活的分区策略

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-08-31 07:43:52

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

    前不久,曾经接手一个性能调优案例:这是一个报表系统,其基础数据主要存储于三张表中。表的大小已经很大了,最大一张接近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代码
  1. SQL> create table par_test   
  2.   2  partition by list (owner)   
  3.   3  subpartition by hash (owner)   
  4.   4  store in (example)   
  5.   5  (partition p1 values ('SYS'),   
  6.   6  partition p2 values ('PUBLIC'),   
  7.   7  partition def values (default)   
  8.   8  subpartitions 4   
  9.   9  )   
  10.  10  as select * from dba_objects   
  11.  11  /   
  12.   
  13. Table created.   
  14.   
  15. SQL> analyze table par_test compute statistics;   
  16.   
  17. Table analyzed.   
  18.   
  19. SQL> select partition_name, subpartition_name, num_rows, blocks from dba_tab_subpartitions   
  20.   2  where table_name = 'PAR_TEST';   
  21.   
  22. PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS     BLOCKS   
  23. ------------------------------ ------------------------------ ---------- ----------   
  24. P2                             SYS_SUBP154                         26604        434   
  25. DEF                            SYS_SUBP158                          4529         70   
  26. DEF                            SYS_SUBP157                          2783         45   
  27. DEF                            SYS_SUBP156                          2422         39   
  28. DEF                            SYS_SUBP155                          2854         47   
  29. P1                             SYS_SUBP153                         29770        437   
  30.   
  31. rows selected.   

    顺便再提一下11g新增的其他分区策略。

    针对Range Partition,11g有了一种更加灵活的方式:Interval Partition。例如,我们一些分区表是依赖于时间做的范围分区:每个月的数据存放到一个分区中。随着数据的增长,还需要有一个作业来增加新的分区以满足上述策略。而在11g中,通过Interval Parition,就无需这中人为的维护作业了,Oracle会为新的数据自动增加分区:

SQL代码
  1. SQL> create table par_test2 (a number, b date)      
  2.   2  partition by range (b)      
  3.   3  interval (numtoyminterval(1,'MONTH'))      
  4.   4  store in (example)      
  5.   5  (      
  6.   6     partition values less than (to_date('2009-09-01','yyyy-mm-dd'))      
  7.   7  )      
  8.   8  ;      
  9.      
  10. Table created.      
  11.      
  12. SQL> insert into par_test2 values(1, sysdate);      
  13.      
  14. 1 row created.      
  15.      
  16. SQL> commit;      
  17.      
  18. Commit complete.      
  19.      
  20. SQL> select partition_name, high_value from dba_tab_partitions      
  21.   2  where table_name = 'PAR_TEST2';      
  22.      
  23. PARTITION_NAME                 HIGH_VALUE      
  24. ------------------------------ --------------------------------------------------------------------------------      
  25. SYS_P164                       TO_DATE(' 2009-09-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIA  '  
  26.     
  27. SQL> insert into par_test2 values(1, to_date('2009-10-01','yyyy-mm-dd'));     
  28.     
  29. 1 row created.     
  30.     
  31. SQL> commit;     
  32.     
  33. Commit complete.     
  34.     
  35. SQL> select partition_name, high_value from dba_tab_partitions     
  36.   2  where table_name = 'PAR_TEST2';     
  37.     
  38. PARTITION_NAME                 HIGH_VALUE     
  39. ------------------------------ --------------------------------------------------------------------------------     
  40. SYS_P164                       TO_DATE(' 2009-09-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIA  '    
  41. SYS_P165                       TO_DATE(' 2009-11-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIA  '  
  42.     
  43. SQL> insert into par_test2 values(1, sysdate - interval '1' MONTH);     
  44.     
  45. 1 row created.     
  46.     
  47. SQL> commit;     
  48.     
  49. Commit complete.     
  50.     
  51. SQL> select * from par_test2;     
  52.     
  53.          A B     
  54. ---------- ---------     
  55.          1 31-AUG-09     
  56.          1 31-JUL-09     
  57.          1 01-OCT-09     
  58.     
  59. SQL> select partition_name, high_value from dba_tab_partitions     
  60.   2  where table_name = 'PAR_TEST2';     
  61.     
  62. PARTITION_NAME                 HIGH_VALUE     
  63. ------------------------------ --------------------------------------------------------------------------------     
  64. SYS_P164                       TO_DATE(' 2009-09-01 00:00:00''SYYYY-MM-DD HH24:MI:SS''NLS_CALENDAR=GREGORIA '  
  65. 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代码
  1. SQL> create table par_main (pid number primary key, crt_date date)   
  2.   2  partition by range (crt_date)   
  3.   3  (   
  4.   4     partition values less than (to_date('2009-09-01','yyyy-mm-dd')),   
  5.   5     partition values less than (maxvalue)   
  6.   6  );   
  7.   
  8. Table created.   
  9.   
  10. SQL> create table par_child (   
  11.   2    cid number primary key,   
  12.   3    pid number not null,   
  13.   4    constraint par_main_fk foreign key (pid) references par_main(pid)   
  14.   5  )   
  15.   6  partition by reference (par_main_fk);   
  16.   
  17. Table created. 

    关联分区中要注意一点:关联分区中,父表不能为Interval分区。

    11g,还新增了以虚字段(Virtual Column)为分区键的分区方式。以下例子中,store就是一个虚列,它的数值并没有实际存储在表中,而是由其它两个字段buy和sell计算得出,我们可以以它作为分区键建立分区:

SQL代码
  1. SQL> create table par_vc(   
  2.   2     itemid number,   
  3.   3     buy number,   
  4.   4     sell number,   
  5.   5     store number as (buy - sell)   
  6.   6  )   
  7.   7  partition by range (store)   
  8.   8  (   
  9.   9    partition values less than (1000),   
  10.  10    partition values less than (2000),   
  11.  11    partition values less than (maxvalue)   
  12.  12  );   
  13.   
  14. Table created.  

    11g中引入的最后一种特殊的分区是system partition。对于普通分区,必须有一个或多个字段做为分区键来建立分区,而system分区就没有这种要求——仅仅是将表的数据分别存储在多个段中,而你在插入数据时,需要指定数据存储在哪个分区上:

SQL代码
  1. SQL> create table par_sys (a number, b varchar2(10))   
  2.   2  partition by system   
  3.   3  (   
  4.   4    partition p1,   
  5.   5    partition p2   
  6.   6  );   
  7.   
  8. Table created.   
  9.   
  10. SQL> insert into par_sys values (1, 'a');   
  11. insert into par_sys values (1, 'a')   
  12.             *   
  13. ERROR at line 1:   
  14. ORA-14701: partition-extended name or bind variable must be used for DMLs on  
  15. tables partitioned by the System method   
  16.   
  17.   
  18. SQL> insert into par_sys partition(p1) values (1, 'a');   
  19.   
  20. 1 row created.  

    从上面的例子可以注意到:在插入数据时,如果没有指定分区就会抛错。

    --- Fuyuncat Mark ---

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat