强化--oracle提供了以下几种分区类型:

oracle提供了以下几种分区类型:
    范围分区(range)
    哈希分区(hash)
    列表分区(list)
    范围-哈希复合分区(range-hash)
    范围-列表复合分区(range-list)
    
    查看分区数  :select*from user_tab_partitions where table_name='表名'
    查看分区内容:select * from 表名 partition(分区名) ;
    
    alter table 表名 add partition 分区名 values (分区字段)
    tablespace tbs_zba_czc       --表空间
    pctfree 10                      --预留的空间大小,10%
    initrans 1                      --的是一个 block 上初始预分配给并行交易控制的空间
    maxtrans 255                  --如果initrans 不够了,自动扩展,最大这个值
    
    分区表示例:
        create table temp_fee(
        month_id             varchar2(6),
        prov_id              varchar2(3),
        total_fee            number)
        nologging
        
        partition by range (month_id)  --主分区
        
        subpartition by list (prov_id) --子分区
        (
          partition part201606 values less than ('201711')
            tablespace tbs_zba_jm
            pctfree 10
            initrans 1
            maxtrans 255
          (
            subpartition sys_subp20429 values (default) tablespace tbs_zba_jm
          )
        );
    
一、范围分区(特别要注意的是"范围"中不包含=)

    1、单范围
        create table temp_fee(
            month_id             varchar2(6),
            prov_id              varchar2(3),
            device_number        varchar2(40))
            nologging
        partition by range (month_id)
        (
          partition part201606 values less than ('201711'),
          partition part201607 values less than ('201710'),
          partition part201608 values less than ('201709'),
          partition part201609 values less than ('201708')
        );
        
    2、多范围分区
        create table temp_fee(
            month_id             varchar2(6),
            prov_id              varchar2(3),
            device_number        varchar2(40))
            nologging
        partition by range (month_id,prov_id)
        (
          partition part201606 values less than ('201711','011'),
          partition part201607 values less than ('201710','012'),
          partition part201608 values less than ('201709','013'),
          partition part201609 values less than ('201708','014')
        );
        
    3、循环分区
    
        1)建表
            create table temp_fee(
                month_id             varchar2(6),
                prov_id              varchar2(3),
                device_number        varchar2(40))
                nologging
            partition by range (month_id)
            (
              partition part201606 values less than ('201711')
            );
            
        2)分区拓展
            declare
             v_table varchar2(64):='temp_fee';
             v_month_start varchar2(8) := '201801';
             v_month_end   varchar2(8) := '201803';
             i     varchar2(8);
             v_sql varchar2(5000);
             v_nmon varchar2(8);
            begin
                    i := v_month_start;
              while i <= v_month_end loop
                v_nmon := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm');
                v_sql := 'alter table '||v_table||' add partition part'||i||' values less than ('''||v_nmon||''')'; 
                execute immediate v_sql ;
                i := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm');
              end loop;
              commit;
            end;
    
二、列表分区

    1、建表
          create table temp_qw(
            prov_id              varchar2(3),
            d_number             varchar2(15),
            user_id              varchar2(15))
            nologging
          partition by list (prov_id)
          (
            partition part09 values ('009')       
          );
          
    2、分区拓展
        declare
         v_table varchar2(64):='temp_qw';
         v_sql varchar2(5000);
        begin
            for j in (select prov_id from prov order by prov_id) loop
              v_sql := 'alter table '||v_table||' add partition part'||j.prov_id||' values ('''||j.prov_id||''')'; 
              execute immediate v_sql ;
            end loop;
          commit;
        end;
    
    需要注意的问题:
        一但列表分区后,如果插入了未分区的字段会报错,为了防止出现这种情况一般我们在添加完分区后
        alter table temp_qw add partition part_default values (default); 
        这样做的后续问题就是,在想添加分区的时候就的把这个分区删除掉
      示例:
          create table temp_qwe(
                prov_id              varchar2(3),
                d_number             varchar2(15),
                user_id              varchar2(15))
                nologging
          partition by list (prov_id)
              (
                partition part11 values ('011'),
                partition part10 values ('010'),
                partition part09 values (default)       
              );
          删除分区:alter table temp_qwe drop partition part09
        需要注意的是,在删除分区的时候一定要将分区内数据备份,不然删除分区的时候会将数据删除
      
三、哈希分区
    再碰到未知怎么去分区的时候
        create table test(
                  transaction_id number primary key,
                  item_id number(8) not null)
        partition by hash(transaction_id)
        (
            partition part_01 tablespace tablespace01,
            partition part_02 tablespace tablespace02,
            partition part_03 tablespace tablespace03
        );

四、组合分区
    在生产中我们会常常用到这样的分区
    示例:
        create table temp_lfc_zcdwk_acct
               (month_id      varchar2(6),
               day_id         varchar2(6),
               prov_id        varchar2(6),
               flag           varchar2(2) 
               )
        partition by range (month_id, prov_id)
        subpartition by list (day_id)
        (
          partition part201801_049 values less than ('201801', '050')
            
          (
            subpartition part201801_049_subpart_01 values ('01')  ,
            subpartition part201801_049_subpart_02 values ('02')  ,
            subpartition part201801_049_subpart_03 values ('03')  ,
            subpartition part201801_049_subpart_04 values ('04')  ,
            subpartition part201801_049_subpart_05 values ('05')  ,
            subpartition part201801_049_subpart_06 values ('06')  ,
            subpartition part201801_049_subpart_07 values ('07')  ,
            subpartition part201801_049_subpart_08 values ('08')  ,
            subpartition part201801_049_subpart_09 values ('09')  ,
            subpartition part201801_049_subpart_10 values ('10')  ,
            subpartition part201801_049_subpart_11 values ('11')  ,
            subpartition part201801_049_subpart_12 values ('12')  ,
            subpartition part201801_049_subpart_13 values ('13')  ,
            subpartition part201801_049_subpart_14 values ('14')  ,
            subpartition part201801_049_subpart_15 values ('15')  ,
            subpartition part201801_049_subpart_16 values ('16')  ,
            subpartition part201801_049_subpart_17 values ('17')  ,
            subpartition part201801_049_subpart_18 values ('18')  ,
            subpartition part201801_049_subpart_19 values ('19')  ,
            subpartition part201801_049_subpart_20 values ('20')  ,
            subpartition part201801_049_subpart_21 values ('21')  ,
            subpartition part201801_049_subpart_22 values ('22')  ,
            subpartition part201801_049_subpart_23 values ('23')  ,
            subpartition part201801_049_subpart_24 values ('24')  ,
            subpartition part201801_049_subpart_25 values ('25')  ,
            subpartition part201801_049_subpart_26 values ('26')  ,
            subpartition part201801_049_subpart_27 values ('27')  ,
            subpartition part201801_049_subpart_28 values ('28')  ,
            subpartition part201801_049_subpart_29 values ('29')  ,
            subpartition part201801_049_subpart_30 values ('30')  ,
            subpartition part201801_049_subpart_31 values ('31')  
          ),    
            partition part201801_050 values less than ('201801', '051')
            
          (
            subpartition part201801_050_subpart_01 values ('01')  ,
            subpartition part201801_050_subpart_02 values ('02')  ,
            subpartition part201801_050_subpart_03 values ('03')  ,
            subpartition part201801_050_subpart_04 values ('04')  ,
            subpartition part201801_050_subpart_05 values ('05')  ,
            subpartition part201801_050_subpart_06 values ('06')  ,
            subpartition part201801_050_subpart_07 values ('07')  ,
            subpartition part201801_050_subpart_08 values ('08')  ,
            subpartition part201801_050_subpart_09 values ('09')  ,
            subpartition part201801_050_subpart_10 values ('10')  ,
            subpartition part201801_050_subpart_11 values ('11')  ,
            subpartition part201801_050_subpart_12 values ('12')  ,
            subpartition part201801_050_subpart_13 values ('13')  ,
            subpartition part201801_050_subpart_14 values ('14')  ,
            subpartition part201801_050_subpart_15 values ('15')  ,
            subpartition part201801_050_subpart_16 values ('16')  ,
            subpartition part201801_050_subpart_17 values ('17')  ,
            subpartition part201801_050_subpart_18 values ('18')  ,
            subpartition part201801_050_subpart_19 values ('19')  ,
            subpartition part201801_050_subpart_20 values ('20')  ,
            subpartition part201801_050_subpart_21 values ('21')  ,
            subpartition part201801_050_subpart_22 values ('22')  ,
            subpartition part201801_050_subpart_23 values ('23')  ,
            subpartition part201801_050_subpart_24 values ('24')  ,
            subpartition part201801_050_subpart_25 values ('25')  ,
            subpartition part201801_050_subpart_26 values ('26')  ,
            subpartition part201801_050_subpart_27 values ('27')  ,
            subpartition part201801_050_subpart_28 values ('28')  ,
            subpartition part201801_050_subpart_29 values ('29')  ,
            subpartition part201801_050_subpart_30 values ('30')  ,
            subpartition part201801_050_subpart_31 values ('31')  
            ));                 

双分区
    1、建表        
        create table temp_ee(
            month_id             varchar2(6),
            prov_id              varchar2(3),
            device_number        varchar2(40))
            nologging

        partition by range (month_id)
        subpartition by list (prov_id)
        (
          partition part201606 values less than ('201711')
          (
            subpartition sys_default values (default)
          )
        );

    2、分区拓展
        declare
         v_table varchar2(64):='temp_ee';
         v_month_start varchar2(8) := '201711';
         v_month_end   varchar2(8) := '201803';
         i     varchar2(8);
         v_sql varchar2(5000);
         v_nmon varchar2(8);
        begin
          i := v_month_start;
          while i <= v_month_end loop
            v_nmon := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm');
            v_sql := 'alter table '||v_table||' add partition part'||i||' values less than ('''||v_nmon||''')
            (
              ';
            for j in (select prov_id from prov order by prov_id) loop
              v_sql := v_sql || '  subpartition part'||i||'_subpart'||j.prov_id||' values ('''||j.prov_id||''') ,
              ';
            end loop;
            v_sql := v_sql || '  subpartition part'||i||'_subpartdefault values (default) ) ';
            execute immediate v_sql ;
            i := to_char(add_months(to_date(i,'yyyymm'),1),'yyyymm');
          end loop;
          commit;
        end;

 
 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页