开启辅助访问

Bo's Oracle Station

Bo's Oracle Station › 博客首页 ›Oracle技术› Performance Tuning › 查看内容

【博客文章2021】Oracle分区技术和复合分区技术的例子和注意事项

2021-10-28 11:25| 发布者: admin| 查看: 4564| 评论: 0|原作者: Bo Tang

摘要: Oracle分区技术和复合分区技术的例子和注意事项Author: Bo TangSYSTEM分区：CREATE TABLE hr.systab (c1 integer, c2 integer)PARTITION BY SYSTEM(PARTITION p1 TABLESPACE users,PARTITION p2 TABLESPACE example,PARTITION p3 TABLESPACE users,PARTITION p4 TABLESPACE example); select *fro

【博客文章2021】Oracle分区技术和复合分区技术的例子和注意事项

Author: Bo Tang

SYSTEM分区：
  1. CREATE TABLE hr.systab (c1 integer, c2 integer)
  2. PARTITION BY SYSTEM
  3. (
  4.   PARTITION p1 TABLESPACE users,
  5.   PARTITION p2 TABLESPACE example,
  6.   PARTITION p3 TABLESPACE users,
  7.   PARTITION p4 TABLESPACE example
  8. );


  11.     select    *  from dba_part_tables  pt
  12.     where pt.owner='HR' and pt.table_name='SYSTAB';

  14.     select  * from dba_tab_partitions  tp
  15.     where tp.table_owner='HR' and tp.table_name='SYSTAB';
  16.    
  17.     INSERT INTO hr.systab VALUES (4,5);会报错
复制代码


  1.     INSERT INTO hr.systab PARTITION (p1) VALUES (4,5);
  2.     commit;
  3.    
  4.     select  * from hr.systab  partition ( p1);
  5.    
复制代码

----------------虚拟列表：


  3. create table hr.t5006_a  ( a  number ,
  4.                                        b   as  (a*2 )  );

  6. select * from dba_tab_columns  tc
  7. where tc.OWNER='HR' and tc.TABLE_NAME='T5006_A';

  9. insert into hr.T5006_A (a)  values  (1    );
  10. commit;

  12. begin
  13.     for i in 2..300
  14.     loop
  15.        insert into hr.T5006_A (a)  values  (i    );
  16.      end loop;
  17.      commit;
  18.   end;

  20. begin
  21.     dbms_stats.gather_table_stats(ownname => 'HR',
  22.     tabname => 'T5006_A',
  23.     method_opt => 'for columns b size 254 for all columns size auto');
  24.   end;
  25.   
  26.   select  * from dba_tab_col_statistics tcs
  27.    where tcs.owner='HR'  and tcs.table_name='T5006_A';
  28.    
  29.    select  * from hr.t5006_a;
  30.    
  31.     update hr.t5006_a set b=4 where a=1;会报错


复制代码



  1. create table hr.t5006_b  ( a  varchar2(10) ,
  2.                                        b   as  (a||'X' )  )
  3.            partition by list (b)
  4.          (
  5.           partition p1  values ('AX','BX'),
  6.           partition p2 values ('CX')
  7.          );                           
  8.       

  10. insert into hr.t5006_b  (a ) values ('A');

  12. commit;

  14. select  * from hr.t5006_b  partition (p1);
复制代码
语法上可以不写virtual：
  1. create table hr.t5006_c  ( a  number ,
  2.                                        b   as  (a*2 )  virtual );
复制代码
--------------------------- 引用分区，要特别注意外键非空：
  1. create table hr.t5006_parent (  a number  primary key  , b varchar2(20) )
  2. partition by range ( a )
  3. (
  4. partition p1 values less than(100),
  5. partition p2 values less than (maxvalue)
  6. );

  8. create table hr.t5006_son ( a number      constraint fk_t5006  references hr.t5006_parent  ,
  9.                                            c date )
  10. partition by  reference  (fk_t5006); 会报错:
复制代码




  1. create table hr.t5006_son ( a number  not null    constraint fk_t5006  references hr.t5006_parent  ,
  2.                                            c date )
  3. partition by  reference  (fk_t5006);        
复制代码

------------------------引用分区的自动维护：
  1. create table hr.t5006_parent (  a number  primary key  , b varchar2(20) )
  2. partition by range ( a )
  3. (
  4. partition p1 values less than(100),
  5. partition p2 values less than (maxvalue)
  6. );

  8. create table hr.t5006_son ( a number  not null    constraint fk_t5006  references hr.t5006_parent  ,
  9.                                            c date )
  10. partition by  reference  (fk_t5006);                                          
  11. ----------------------------------------
  12. select table_name, partition_name, high_value
  13. from dba_tab_partitions
  14. where table_name in ('T5006_PARENT','T5006_SON')
  15. order by partition_position, table_name;
  16. ----------------------------------------


  19. insert into hr.t5006_parent values ( 99,'A99' ) ;
  20. commit;

  22. insert into hr.t5006_son  values (99,sysdate );
  23. commit;

  25. select  * from hr.t5006_son partition (p2) ;

  27. -----------------------------------------

  29. alter table hr.t5006_parent  split partition p1 at ( 50)  into
  30. ( partition p11 , partition p12);
  31. ------------------------------------------
  32. select table_name, partition_name, high_value
  33. from dba_tab_partitions
  34. where table_name in ('T5006_PARENT','T5006_SON')
  35. order by partition_position, table_name;
  36. -----------------------------------------------------------------------
  37. alter table hr.t5006_parent merge partitions p11,  p12
  38. into partition p1;
  39. -------------------------------------------------
  40. select table_name, partition_name, high_value
  41. from dba_tab_partitions
  42. where table_name in ('T5006_PARENT','T5006_SON')
  43. order by partition_position, table_name;

复制代码

---------------------分区键并不是主外键（书上的例子）:




  3. create table hr.t5006_p ( a number primary key , b date )
  4. partition by range ( b )
  5. (
  6. partition p1 values less than(  to_date ('2000-01-01','YYYY-MM-DD' )),
  7. partition p2 values less than (  to_date ('2010-01-01','YYYY-MM-DD') )
  8. );



  12. create table hr.t5006_s ( a number not null constraint fk_t5006_s references hr.t5006_p ,
  13. b date )
  14. partition by reference (fk_t5006_s);


  17. insert into hr.t5006_p values ( 1,  to_date ('2009-01-01','YYYY-MM-DD') );

  19. commit;

  21. insert into hr.t5006_s values ( 1,   to_date ('1999-01-01','YYYY-MM-DD') );

  23. commit;

  25. select table_name, partition_name, high_value
  26. from dba_tab_partitions
  27. where table_name in ('T5006_P','T5006_S')
  28. order by partition_position, table_name;


  31. select  * from hr.t5006_p partition (p2) ;

  33. select  * from hr.t5006_s partition (p2);

  35. select  * from hr.t5006_s;
  36. select  * from hr.t5006_s partition (p2);
复制代码



以上这种情况，son表的分区范围(date)与 parent表是不一样的，son表的分区分布：
主键（1）在p2，子键(1)就在子表自己的p2上。
--------------------------------------复合分区
有办法写temple的语法：

  1. CREATE TABLE hr.sales (   prod_id NUMBER(6) NOT NULL,
  2.                                              cust_id NUMBER NOT NULL,
  3.                                              time_id DATE NOT NULL,
  4.                                              channel_id char(1) NOT NULL,
  5.                                              promo_id NUMBER (6) NOT NULL,
  6.                                              quantity_sold NUMBER(3) NOT NULL,
  7.                                              amount_sold NUMBER(10,2) NOT NULL )
  8. PARTITION BY RANGE (time_id)
  9. SUBPARTITION BY RANGE (cust_id)
  10. SUBPARTITION TEMPLATE
  11.    (  SUBPARTITION sp1 VALUES LESS THAN (50000),   
  12.       SUBPARTITION sp2 VALUES LESS THAN (100000),
  13.       SUBPARTITION sp3 VALUES LESS THAN (150000),
  14.       SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
  15. (
  16.   PARTITION  p1 VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
  17.   PARTITION  p2 VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
  18.   PARTITION  p3 VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
  19.   PARTITION  p4 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
  20. );


  23. select  * from dba_tab_partitions  tp
  24.   where tp.table_owner='HR' and tp.table_name='SALES';

  26. select  * from dba_tab_subpartitions tp
  27.   where tp.table_owner='HR' and tp.table_name='SALES';
复制代码

没办法写template的语法：
  1. create table shipments
  2. ( order_id number not null
  3. , order_date date not null
  4. , ship_date date not null
  5. , customer_id number not null
  6. , sales_amount number not null
  7. )
  8. partition by range (order_date)
  9. subpartition by range (ship_date)
  10. ( partition p_2006_jul values
  11.   less than (to_date('01-AUG-2006','dd-MON-yyyy'))
  12.   ( subpartition p_2006_jul_early values
  13.     less than (to_date('15-AUG-2006','dd-MON-yyyy'))
  14.   , subpartition p_2006_jul_agreed values
  15.     less than (to_date('01-SEP-2006','dd-MON-yyyy'))
  16.   , subpartition p_2006_jul_late values less than (maxvalue)
  17.   )
  18. , partition p_2006_aug values
  19.   less than (to_date('01-SEP-2006','dd-MON-yyyy'))
  20.   ( subpartition p_2006_aug_early values
  21.     less than (to_date('15-SEP-2006','dd-MON-yyyy'))
  22.   , subpartition p_2006_aug_agreed values
  23.     less than (to_date('01-OCT-2006','dd-MON-yyyy'))
  24.   , subpartition p_2006_aug_late values less than (maxvalue)
  25.   )
  26. , partition p_2006_sep values
  27.   less than (to_date('01-OCT-2006','dd-MON-yyyy'))
  28.   ( subpartition p_2006_sep_early values
  29.     less than (to_date('15-OCT-2006','dd-MON-yyyy'))
  30.   , subpartition p_2006_sep_agreed values
  31.     less than (to_date('01-NOV-2006','dd-MON-yyyy'))
  32.   , subpartition p_2006_sep_late values less than (maxvalue)
  33.   )
  34. , partition p_2006_oct values
  35.   less than (to_date('01-NOV-2006','dd-MON-yyyy'))
  36.   ( subpartition p_2006_oct_early values
  37.     less than (to_date('15-NOV-2006','dd-MON-yyyy'))
  38.   , subpartition p_2006_oct_agreed values
  39.     less than (to_date('01-DEC-2006','dd-MON-yyyy'))
  40.   , subpartition p_2006_oct_late values less than (maxvalue)
  41.   )
  42. , partition p_2006_nov values
  43.   less than (to_date('01-DEC-2006','dd-MON-yyyy'))
  44.   ( subpartition p_2006_nov_early values
  45.   less than (to_date('15-DEC-2006','dd-MON-yyyy'))
  46.   , subpartition p_2006_nov_agreed values
  47.   less than (to_date('01-JAN-2007','dd-MON-yyyy'))
  48.   , subpartition p_2006_nov_late values less than (maxvalue)
  49.   )
  50. , partition p_2006_dec values
  51.   less than (to_date('01-JAN-2007','dd-MON-yyyy'))
  52.   ( subpartition p_2006_dec_early values
  53.     less than (to_date('15-JAN-2007','dd-MON-yyyy'))
  54.   , subpartition p_2006_dec_agreed values
  55.     less than (to_date('01-FEB-2007','dd-MON-yyyy'))
  56.   , subpartition p_2006_dec_late values less than (maxvalue)
  57.   )
  58. )

复制代码

路过
雷人
握手
鲜花
鸡蛋
收藏 邀请
上一篇：【博客文章2022】 In-Memory Column Store系列2

QQ|手机版|Bo's Oracle Station   

GMT+8, 2022-4-11 14:55 , Processed in 0.056139 second(s), 21 queries .

返回顶部