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 r2 values less than (to_date('2019-01-01','yyyy-mm-dd')),
partition r3 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);

虚拟列分区：
create table a8(id number primary key,name varchar2(10) not null,salary number not null,comm number not null,newsalary generated always as(salary+comm))
partition by range(newsalary)
(
partition v1 values less than(5000),
partition v2 values less than(8000),
partition v3 values less than(maxvalue)
);

insert into a8(id,name,salary,comm) values(1,'aaa',2000,200);
insert into a8(id,name,salary,comm) values(2,'bbb',7000,200);
insert into a8(id,name,salary,comm) values(3,'ccc',9000,200);
insert into a8(id,name,salary,comm) values(4,'ddd',10000,200);
commit;

select * from a8;
select * from a8 partition(v3);

更新分区表要开启行移动：
create table a1(id int,name varchar(2),salesamount number)
partition by range(salesamount)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000)
);

select * from a1;

insert into a1 values(1,'aa',800);
insert into a1 values(2,'bb',1200);
insert into a1 values(3,'cc',2200);
commit;

select * from a1 partition(p1);
alter table a1 enable row movement;
update a1 set salesamount=300 where id=3;

复合分区： 范围哈希分区
create table a1(id int,name varchar(2),salesamount number)
partition by range(salesamount)
subpartition by hash(name)
subpartitions 4
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);

select * from dba_tab_subpartitions where table_name='A1';

select * from dba_tab_partitions where table_name='A1';