oracle 数据库表的分区

表分区
    表分区的具体作用：
      分区功能可以将表、索引或者索引组织表进一步细分为段，这些数据对象的段叫作分区，
      每个分区都有自己的名称，一个分区后的对象(表)具体有多个段，这些段既可以集体管理，
      也可以单独管理。
    什么时候需要用分区表：
      (1)表的数据量特别大
      (2)表中包含历史数据，新的数据被增加到新的分区中。
    表分区的优缺点：
      优点：
        1.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
        2.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
        3.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
        4.均衡i/o：可以把不同的分区映射到磁盘以平衡i/o，改善整个系统的性能。
        
      缺点：
        1.已经存在的表没有方法可以直接转化为分区表。
        2.需要维护。
    表分区的几种类型：
      1.范围分区：range
      2.列表分区：list
      3.组合分区：范围分区+列表分区
    
    范围分区：
      create table table_name(column1 type1,column2 type2,...)
      partition by range (需要用作分区的字段名)
      (partition 分区名1 values less than (分区字段具体的一个上限值),
      partition 分区名2 values less than (分区字段具体的一个上限值),
      partition 分区名3 values less than (maxvalue)
      );
      
      step1:创建分区表
        普通创建：
        create table emp_range(empno number,ename varchar2(10),job varchar2(10),mgr number,
        hiredate date,sal number,comm number,deptno number)
        partition by range(hiredate)
        (partition range_1981 values less than (to_date('19810101','YYYYMMDD')),
        partition range_1982 values less than (to_date('19820101','YYYYMMDD')),
        partition range_max values less than (maxvalue)
        );
        --以as方法：
        create table emp_range3
        partition by range(hiredate)
        (partition range_1981 values less than (to_date('19810101','YYYYMMDD')),
        partition range_1982 values less than (to_date('19820101','YYYYMMDD')),
        partition range_max values less than (maxvalue)
        )
        as select * from emp;
 --创建一张emp_range表数据同emp一样
 -- 并按照工资范围 划分为三个档 
 --第一个档 工资不高于2000  第二个档 不高于4000 第三个档 不高于8000
 drop table emp_range;
 create table emp_range 
 partition by range (sal)
 (
 partition s_2000 values less than (2000),
 partition s_4000 values less than (4000),
 partition s_8000 values less than (8000)
 )
 as select * from emp;
 
 
 select * from emp_range;
 select * from emp_range partition (s_2000);
 --step2：数据插入分区表中
 insert into emp_range(sal) values(2000);
 --step3：查询分区
 select * from emp_range partition (s_4000);
 insert into emp_range(sal) values(10000);  --没定义 不能插入
 
 
 

select  * from  emp_range  partition (sal4000)  
      
        select * from emp_range partition (range_1981)
        union
        select * from emp_range partition (range_max);
当分区字段作为查询条件的时候，如果查询范围没有跨越分区，就会在对应分区查询,否则就会全表扫描
        
partition range single   单个分区扫描
select * from emp_range where sal between 1000 and 1500; 
PARTITION RANGE ITERATOR 分区迭代扫描
select * from emp_range where sal between 1000 and 3000; 
PARTITION RANGE ALL      是分区全扫描
select * from emp_range where sal between 1000 and 5500; 
 
--创建emp_date和emp表数据一致  并按照入职年份进行分区划分 1981年和1982年和其他年份的分区
create table emp_date 
partition by range(hiredate)
(partition p_1981 values less than (date'1982-1-1'),
 partition p_1982 values less than (date'1983-1-1'),
 partition p_max  values less than (maxvalue)
)
as select * from emp;

       
   --  列表分区：
      create table table_name(column1 type1,column2 type2,...)
      partition by list (需要用作分区的字段名)
      (partition 分区名1 values (具体的值),
      partition 分区名2 values (具体的值),
      partition 分区名3 values (default)
      );
      step1:创建分区
        create table emp_list2
        partition by list(deptno)
        (partition list_10 values (10,20),
        partition list_o values (default)   --没定义的其他值都归为默认值的分区
        )
        as select * from emp; 
        step3：查询分区
        select * from emp_list2 partition (list_10);
        select * from emp_list2 partition (list_o);
        
       insert into emp_list2(deptno) values (40);      
        
  select distinct job from emp      
--创建一个emp_job 按照职位类型创建5个分区    
     create table emp_job 
     partition by list (job)
     (partition p_clerk values ('CLERK'),
      partition p_SALESMAN values ('SALESMAN'),
      partition p_PRESIDENT values ('PRESIDENT'),
      partition p_MANAGER values ('MANAGER'),
      partition p_ANALYST values ('ANALYST')      
     )
     as select * from emp;
     
     select * from emp_job partition (p_clerk);
    
    组合分区：
      组合分区(范围分区+列表分区)
      语法格式：
      create table table_name(column1 type1,column2 type2,...)
      partition by range (主分区字段)
      subpartition by list (子分区字段)
      (
      ----
      partition 主分区名1 values less than (主分区1上限)
      ----
      (
       subpartition 子分区名1 values (子分区值1),
       subpartition 子分区名2 values (子分区值2)
       ) ,
      ----------------
      ---
      partition 主分区名2 values less than (主分区2上限)
      ---
      (
       subpartition 子分区名1 values (子分区值1),
       subpartition 子分区名2 values (子分区值2)
      )
      );
      
      主分区为range子分区为list：
--对hiredate 进行范围分区 1981 和之后的时间
--再进行 部门编号的列表分区
create table emp_hiredate_deptno
partition by range (hiredate)
subpartition by list(deptno) 
(
partition p_1981 values less than (date'1982-1-1')
(
subpartition p_1981_10  values (10),
subpartition p_1981_20  values (20),
subpartition p_1981_30  values (30)
),
partition p_1982 values less than (maxvalue)
(
subpartition p_1982_10  values (10),
subpartition p_1982_20  values (20),
subpartition p_1982_30  values (30)
)
) 
as select * from emp;
 
查询主分区与子分区   
select * from emp_hiredate_deptno;    
select * from emp_hiredate_deptno partition (p_1981);      
select * from emp_hiredate_deptno subpartition (p_1981_10);      
 

      主分区为list子分区为range：
      create table emp_range_list2
      partition by list(deptno)
      subpartition by range(hiredate)
      (partition p1 values (10,20)
      (subpartition p1a values less than (to_date('19820101','YYYYMMDD')),
      subpartition p1b values less than (maxvalue)),
      partition p2 values (30)
      (subpartition p2a values less than (to_date('19820101','YYYYMMDD')),
      subpartition p2b values less than (maxvalue))
      )
      as select * from emp;
      --查询分区
select * from emp_range_list2 subpartition (p1a);      
select * from emp_range_list2 partition (p1);     
      
      
      主分区为list子分区为list：
      create table emp_range_list3
      partition by list(deptno)
      subpartition by list(deptno)
      (partition p1 values (10,20)
      (subpartition p1a values (10),
      subpartition p1b values (20)),
      partition p2 values (30)
      (subpartition p2c values (30))
      )
      as select * from emp;
      
--创建一张表  emp_job  表数据同emp  并按照部门和职位进行分区 

drop table emp_job;
select distinct  deptno,job  from  emp;
create table emp_job
partition by list (deptno)
subpartition by list(job)
(
partition p1 values (10)
(subpartition p1a values ('PRESIDENT'),
 subpartition p1b values ('CLERK'),
 subpartition p1c values ('MANAGER')
),
partition p2 values (20)
(subpartition p2a values ('CLERK'),
 subpartition p2b values ('MANAGER'),
 subpartition p2c values ('ANALYST')
),
partition p3 values (30)
(subpartition p3a values ('SALESMAN'),
 subpartition p3b values ('CLERK'),
 subpartition p3c values ('MANAGER')
)
)
as select * from emp;

select * from emp_job subpartition(p1a);




    删除分区 -- 删除分区会删除表数据
      alter table 表名 drop partition 分区名;
      alter table emp_job drop partition p1;
      select * from emp_job partition(p1);
      alter table emp_job drop subpartition p2a;
      select * from emp_range_list3 subpartition(p1b);
      
    添加分区
      只能添加已存分区规则外的规则的分区
     
      alter table 表名 add partition 分区名 values less than(上限值);
      --给emp_range 添加 第四个分区 范围是工资不超过10000
       select * from emp_range;
        alter table emp_range add partition p4 values less than(10000);
        
      alter table emp_date add partition range_1984 values less than(to_date('19850101','YYYYMMDD'));
      alter table emp_job add partition p1 values (10);      
    截断分区
      alter table 表名 truncate partition 分区名;
      alter table 表名 truncate subpartition 子分区名;
      alter table emp_job truncate partition p1;
       select * from emp_job subpartition (p3a);
      alter table emp_job truncate subpartition p3a;
   
    合并分区
      alter table 表名 merge partitions 分区名1,分区名2 into partition 新分区名;
      
      alter table emp_job merge partitions p2,p3 into partition p_new;
      --合并范围分区
      emp_range
      alter table emp_range merge partitions s_2000,s_4000 into partition s_4;
    拆分分区
      alter table 表名 split partition 分区名 at (分割值) into (partition 新分区名1,partition 新分区名2);
      alter table emp_range split partition s_4
      at (2000) into (partition s_2000,partition s_4000);
  


  alter table emp_job split partition p_new at (20)    --不可以进行分离列表分区
                       into (partition p2,partition p3);
  
  alter table emp_date merge partitions p_1981,p_1982 into partition p_1983;
  alter table emp_date split partition p_1983 at (date'1982-1-1')    
                       into (partition p_1981,partition p_1982);
  
    重命名分区
      alter table 表名 rename partition 原分区名 to 新分区名;
      alter table emp_range4 rename partition range_1982 to range_1982_2;
--查看分区信息
select * from user_tab_partitions a where a.table_name = 'EMP_JOB';


--补充  
增加分区 ：范围分区只能在最高值的后边追加，不可以在中间范围增加 maxvalue
合并分区： 不可以跨区合并，要合并必须是相邻且有序的
select * from  emp_range ;
--拆分工资在4000-8000区间的分区为两个分区  以6000为分界线
alter table emp_range split partition s_8000 at (6000) into (partition s_6000,partition s_8000);
--合并 2000-4000 分区和4000到6000范围的分区 分区名命名为 posal
alter table emp_range merge partitions s_2000,s_4000 into partition posal;
--重命名posal为 p4_6
alter table emp_range rename partition posal to p4_6;
--删除 p4分区
alter table emp_range drop partition p4;
--添加8000-15000的范围分区 s_15000 
alter table emp_range add partition s_15000 values less than(15000);  
--清空p4_6分区的数据
alter table emp_range truncate partition p4_6;
select * from emp_range partition (p4_6);