Oracle分区表的类型

哈希分区主要用于在数据大批量插入的时候，对于数值进行HASH运算，做到相对均衡，避免热块，有点类似于表级别的负载均衡。

create table testhash(id number,name varchar2(10))
partition by hash(id)
(partition h1,
partition h2,
partition h3,
partition h4
);

insert into testhash select object_id,substr(object_name,1,5) from dba_objects;

select count(*) from testhash partition(h4);

间隔分区实际上是范围分区的扩展，可以实现range分区的一个自动扩展。
create table a4(id number,rq date)
partition by range(rq)
interval(NUMTOYMINTERVAL(1,'month'))
(
partition d1 values less than(to_date('2020-01-01','yyyy-mm-dd'))
);

insert into a4 values(1,to_date('2020-02-01','yyyy-mm-dd'));
insert into a4 values(2,to_date('2018-02-01','yyyy-mm-dd'));
insert into a4 values(3,to_date('2020-03-31','yyyy-mm-dd'));
insert into a4 values(4,to_date('2020-04-01','yyyy-mm-dd'));
insert into a4 values(5,to_date('2020-05-31','yyyy-mm-dd'));
commit;

select * from dba_tab_partitions where table_name='A4';

1. 一年后的时间
   select sysdate + numtoyminterval(1, 'YEAR') from dual;

2. 一个月前的时间
   select sysdate + numtoyminterval(-1, 'MONTH') from dual;

1. 一天后的时间
   select sysdate + numtodsinterval(1, 'DAY') from dual;
   
2. 一小时前的时间
   select sysdate + numtodsinterval(-1, 'HOUR') from dual;   

numtoyminterval = number to year month interval 间隔级别有，年，月
numtodsinterval = number to day second interval 间隔级别有，日，时，分，秒

system分区：
数据进入哪个表空间，可以把表空间和分区进行绑定

create table a5(id number,name varchar2(10))
partition by system 
(
partition s1 tablespace t1,
partition s2 tablespace t2,
partition s3 tablespace t3
);

insert into a5 partition(s1) values(1,'aa');
insert into a5 partition(s2) values(2,'bb');
insert into a5 partition(s3) values(3,'cc');
insert into a5 partition(s1) values(4,'dd');
insert into a5 partition(s2) values(5,'ee');
commit;

引用分区：
create table a6(id number,rq date,constraint a6_pk primary key(id))
partition by range(rq)
(
partition r1 values less than (to_date('2018-01-01','yyyy-mm-dd')),
partition r1 values less than (to_date('2019-01-01','yyyy-mm-dd')),
partition r1 values less than (to_date('2020-01-01','yyyy-mm-dd'))
);

create table a7(id number not null,name varchar2(10),constraint a7_fk foreign key(id) references a6(id))
partition by reference(a7_fk);

inset into a6 values(1,to_date('2017-08-31','yyyy-mm-dd'));
inset into a6 values(2,to_date('2018-08-31','yyyy-mm-dd'));
inset into a6 values(3,to_date('2017-07-31','yyyy-mm-dd'));
inset into a6 values(4,to_date('2019-08-31','yyyy-mm-dd'));
commit;

insert into a7 values(1,'ben');
insert into a7 values(2,'tiger');
insert into a7 values(3,'scott');
insert into a7 values(4,'king');
commit;

select * from a7 partition(r1);
select * from a6 partition(r1);