1、范围分区

  1. ---  删除表
  2.  drop table emp_range; 
  4. -- 范围分区 range
  5. create table emp_range
  6. (empno number(4)
  7. ,ename varchar2(10)
  8. ,job varchar2(9)
  9. ,mgr number(4)
  10. ,create_date date
  11. )
  12. partition by range (create_date)
  13. (partition p_20170101 values less than (to_date('2017-01-02','yyyy/mm/dd'))
  14. ,partition p_20170102 values less than (to_date('2017-01-03','yyyy/mm/dd'))
  15. ,partition p_20170103 values less than (to_date('2017-01-04','yyyy/mm/dd'))
  16. );
  19. -- 表要大写   注意！！！！！！！！！！！
  20. -- 显示当前用户可访问的所有分区表信息：all_part_tables
  21. select * from all_part_tables a where a.table_name = 'EMP_RANGE';
  23. -- 显示当前用户所有分区表的信息：user_part_tables
  24. select * from user_part_tables a;
  26. --显示当前用户可访问的所有分区表的详细分区信息：all_tab_partitions 
  27. select * from all_tab_partitions a where a.table_name = 'EMP_RANGE';
  29. --显示当前用户所有分区表的详细分区信息：user_tab_partitions 
  30. select * from user_tab_partitions a where a.table_name = 'EMP_RANGE';
  33. --
  34. -- 自己往表里面插入数据  然后查询  删除数据
  36. select * from emp_range partition (p_20170101); -- 查询
  37. alter table emp_range truncate partition  p_20170101; -- 删除分区的数据
  41. -- 往表里面插入数据
  42. select * from emp_range;
  43. select * from emp_range partition (p_20170103); -- 指定一个范围表分区查询数据
  45. insert into emp_range values(1001,'xiaoming','salas',1000,to_date('20160101','yyyymmdd'));
  47. insert into emp_range values(1001,'xiaoming','salas',1000,to_date('20170101','yyyymmdd'));
  49. insert into emp_range values(1001,'xiaoming','salas',1000,to_date('20170102','yyyymmdd'));
  51. insert into emp_range values(1001,'xiaoming','salas',1000,to_date('20170103','yyyymmdd'));
  53. commit;
  56. -- 异常：插入的分区关键字未映射到任何分区
  57. insert into emp_range values(1001,'xiaoming','salas',1000,to_date('20170108','yyyymmdd'));
  59. -- 添加一个分区 
  60. alter table emp_range add partition p_20170104 
  62.      values less than(to_date('2017-01-05','yyyy-mm-dd'));
  64. -- 删除一个分区
  65. alter table emp_range drop partition p_20170104 ;

2、列表分区

-- 列表分区      

-- 列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的


---  删除表
 drop table emp_list; 

-- 

create table emp_list
(
  empno    number(4)
  ,ename    varchar2(10)
  ,job      varchar2(9)
  ,mgr      number(4)      
  ,deptno   number(2)
)
   partition by list (deptno)
   (
     partition p_10 values  (10)
    ,partition p_20 values  (20)
    ,partition p_30 values  (30)
    ,partition p_40 values  (40)
   );



-- 自己往表里面插入数据  然后查询  删除数据
select * from emp_list partition (p_10); -- 查询

alter table emp_list truncate partition  p_10; -- 删除分区的数据





-- 往表里面插入数据
select * from emp_list;

select * from emp_list partition (p_10); -- 指定一个范围表分区查询数据



insert into emp_list values(1001,'xiaoming','salas',1000,10);


insert into emp_list values(1001,'xiaoming','salas',1000,20);

insert into emp_list values(1001,'xiaoming','salas',1000,30);

insert into emp_list values(1001,'xiaoming','salas',1000,40);

commit;



-- 异常：插入的分区关键字未映射到任何分区
insert into emp_list values(1001,'xiaoming','salas',1000,70);



-- 添加一个分区 
alter table emp_list add partition p_50 values (50);

-- 验证
select * from USER_TAB_PARTITIONS a where a.table_name = 'EMP_LIST';

-- 删除一个分区
alter table emp_list drop partition p_50 ; 




-- 工作中得列表分区

-- 一般公司用列表分区  to_char(create_date,'yyyymmdd')   年月的字符串

---  删除表
 drop table emp_list; 

-- 

create table emp_list
(
  empno    number(4)
  ,ename    varchar2(10)
  ,job      varchar2(9)
  ,mgr      number(4)      
  ,deptno   number(2)
  ,create_date varchar2(10)
)
   partition by list (create_date)
   (
     partition p_20170201 values  (20170201)
    ,partition p_20170202 values  (20170202)
    ,partition p_20170203 values  (20170203)
    ,partition p_20170204 values  (20170204)
   );

3、组合分区

-- 组合分区 分区里面有 分区

create table emp_range_list
(
  empno      number(4) ,
  ename      varchar2(10),
  job        varchar2(9),
  mgr        number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(7,2)
)
partition by range(hiredate) subpartition by list (deptno)
(
   partition p_19800101 values less than(to_date('1981-01-01','yyyy-mm-dd'))
          (
            subpartition p_19800101_10 values (10),
            subpartition p_19800101_20 values (20),
            subpartition p_19800101_30 values (30),
            subpartition p_19800101_40 values (40)
          ),
   partition p_19810101 values less than (to_date('1982-01-01','yyyy-mm-dd'))
          (
              subpartition p_19810101_10 values (10),
              subpartition p_19810101_20 values (20),
              subpartition p_19810101_30 values (30),
              subpartition p_19810101_40 values (40)
          ),
   partition p_19820101 values less than (to_date('1983-01-01','yyyy-mm-dd'))
          (
              subpartition p_19820101_10 values (10),
              subpartition p_19820101_20 values (20),
              subpartition p_19820101_30 values (30),
              subpartition p_19820101_40 values (40)
          ),
     partition p_19830101 values less than (to_date('1984-01-01','yyyy-mm-dd'))
          (
              subpartition p_19830101_10 values (10),
              subpartition p_19830101_20 values (20),
              subpartition p_19830101_30 values (30),
              subpartition p_19830101_40 values (40)
          )
);


--

-- 写数据

insert into emp_range_list
 select * from emp e where e.hiredate < to_date('19840101','yyyymmdd');
 commit;



-- 查询主分区数据
select * from emp_range_list partition (p_19800101); 

-- 删除主分区的数据
alter table emp_range_list truncate partition  p_19800101; 



-- 查询子分区的数据
select * from emp_range_list subpartition(p_19810101_10);

-- 删除子分区的数据
alter table emp_range_list truncate subpartition  p_19810101_10; 




-- 添加一个分区 
alter table emp_range_list add partition p_19840101 
      values less than (to_date('1985-01-01','yyyy-mm-dd'))
          (
              subpartition p_19840101_10 values (10),
              subpartition p_19840101_20 values (20),
              subpartition p_19840101_30 values (30),
              subpartition p_19840101_40 values (40)
          );

-- 删除一个分区
alter table emp_range_list drop partition p_19840101 ;



-- 验证
select * from USER_TAB_PARTITIONS a where a.table_name = 'EMP_RANGE_LIST';

4、哈希分区

—Hash分区 优点：尽可能的让每个分区的数据分布均匀
对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助
hash分区会将表中的数据平均分配到你指定的几个分区中，
列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中

drop table hash_test;

-- HASH分区代码演示
create table hash_test
    (
        stu_id number ,
        age    number ,
        sname  varchar2(30)
   )
   partition by hash(stu_id)
   (
       partition part_01,
       partition part_02,
       partition part_03,
       partition part_04
 );

-- 插入数据测试
select * from hash_test;

drop sequence seq_001;


create sequence seq_001
minvalue 1                 --最小值为1
start with 1               --从1开始
nomaxvalue                 --不设上限   可以指定最大值 maxvalue 10
increment by 1             --增长数  可以是 -1
nocycle                    --不循环
cache 30                   --预先在内存中生成30个序列号
;



-- 循环
declare
begin
  for i in 1..10000 loop  
     insert into hash_test values( SEQ_001.Nextval,18,'小明' );
     commit;
  end loop;
end;


-- 234 244  261  261
select count(1) from hash_test partition (part_01);
select count(1) from hash_test partition (part_02);
select count(1) from hash_test partition (part_03);
select count(1) from hash_test partition (part_04);


select count(1) from hash_test partition (part_05);
select count(1) from hash_test partition (part_06);
select count(1) from hash_test partition (part_07);
select count(1) from hash_test partition (part_08);
select count(1) from hash_test partition (part_09);
select count(1) from hash_test partition (part_10);



---
drop table hash_test;

-- HASH分区代码演示
create table hash_test
    (
        stu_id number ,
        age    number ,
        sname  varchar2(30)
   )
   partition by hash(stu_id)
   (
       partition part_01,
       partition part_02,
       partition part_03,
       partition part_04,
       partition part_05,
       partition part_06,
       partition part_07,
       partition part_08
 );


create table hash_test
    (
        stu_id number ,
        age    number ,
        sname  varchar2(30)
   )
   partition by hash(stu_id)
   (
       partition part_01,
       partition part_02,
       partition part_03,
       partition part_04,
       partition part_05,
       partition part_06,
       partition part_07,
       partition part_08,
       partition part_09,
       partition part_10
 );



-- 换个方式插入数据
insert into hash_test

select level   as stu_id
      ,18      as age
      ,'小五'  as sname
 from dual 
 connect by level <= 10000;
commit;


-- 插入重复数据数据

-- 发现所有的数据都分配在一个分区里面
insert into hash_test

select 1   as stu_id
      ,18      as age
      ,'小五'  as sname
 from dual 
 connect by level <= 10000;

commit;

5、自动扩展分区

-- 按天自动添加分区
   create table emp_interval_day 
  (id       number
  ,time_col date
  ) 
  partition by range(time_col)
   interval (numtodsinterval(1,'day')) -- 一天一个表
  (
    -- 2018年之前的都在一张表内
   partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
  );


select * from emp_interval_day;


insert into emp_interval_day values(1001,to_date('2017-01-01','yyyy-mm-dd'));
insert into emp_interval_day values(1001,to_date('2018-01-01','yyyy-mm-dd'));
insert into emp_interval_day values(1001,to_date('2018-01-02','yyyy-mm-dd'));
insert into emp_interval_day values(1001,to_date('2018-01-03','yyyy-mm-dd'));
insert into emp_interval_day values(1001,to_date('2018-01-28','yyyy-mm-dd'));
insert into emp_interval_day values(1001,to_date('2018-01-04','yyyy-mm-dd'));

commit;



-- 按月自动添加分区
   create table emp_interval_month 
  (id       number
  ,time_col date
  ) 
  partition by range(time_col)
  interval (numtoyminterval(1,'month'))
  (
   partition p_month_1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
  );


-- 删除分区数据
alter table emp_interval_month truncate partition  for (to_date('2018-02-01','yyyy-mm-dd'));


-- 按年自动添加分区
 create table emp_interval_year
  (id       number
  ,time_col date
  ) 
  partition by range(time_col)
  interval (numtoyminterval(1,'year'))
  (
   partition p_year_1 values less than (to_date('2018-01-01','yyyy-mm-dd'))
  );


-- 公司中的做法
 create table emp_interval_m
  (id       number
  ,time_col number
  ) 
  partition by range(time_col)
  interval (1)
  (
   partition p_month_1 values less than (20180101)
  );


 create table emp_interval_m
  (id       number
  ,time_col number
  ) 
  partition by range(time_col)
  interval (1)
  (
   partition p_month_1 values less than (201801)
  );



-- 删除分区数据
alter table emp_interval_m truncate partition  for (20180201);



-- 组合分区的自动扩展分区
create table emp_range_list
(
    empno  number(4) ,
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal  number(7,2),
    comm number(7,2), 
    deptno number(7,2)
)
partition by range(hiredate) interval (numtoyminterval(1,'month'))
subpartition by list (deptno)
(
   partition p1 values less than(to_date('1981-01-01','yyyy-mm-dd'))
          (
              subpartition p1a values (10) ,
              subpartition p1b values (20),
              subpartition p1c values (30),
              subpartition p1d values (40)
          )
);

— 表分区的优缺点
表分区的优点： 提高了增、删、改、查的性能
如果某个分区的数据出现问题，只需要修复这个分区的数据就可以了，不影响其他分区的数据查询
缺点： 已经存在的表没有方法可以直接转化为分区表
这个不叫缺点 仅仅是oracle公司的工程师暂时还没有实现的功能而已

— 查询的数据可能会跨分区
— 分区索引
本地分区索引：适合当个分区查询
全局索引：适合跨分区查询

— 哈希分区有什么优点
尽量让每个分区得数据分布均匀

— 自动扩展分区有什么优缺点
优点：维护方便
缺点：分区名是系统自动给的