ora_partition笔记备份-优快云博客

本文链接：https://blog.youkuaiyun.com/ArchonSbkd/article/details/125874395

前言

1、查看分区信息:

plsql：
select * from user_tab_partitions a where a.table_name='SALES';
select * from tablexx partition(pxx);

2、分区操作:

添加分区:
alter table xx add partition px(这个必须是已有分区之后) value less than (xxx);
删除分区:
alter table xx drop partition px;
截断分区:
alter table xx truncate partition px;
合并分区:
alter table xx merge partitions S1,S2 into partition S2;
拆分分区:
alter table xx split partition p2 at (1500)
into (partition p21,partition p22);

一、范围分区RANGE分区

创建分区表:
例如:
create table sales(
product_id varchar2(5)
sales_count number(10,2)
)
partition by range(sales_count)
(
   partition p1 values less than(1000),
   partition p2 values less than(2000),
   partition p2 values less than(3000)
);
查看分区信息:
plsql：
select * from user_tab_partitions a where a.table_name='SALES';

测试:
inert into sales values('1',600);--分区1
inert into sales values('2',1000);--分区2(1000不在less than1000里)
inert into sales values('3',2300);--分区3
inert into sales values('3',600);--插入失败ora-14400,没找到合适分区
查看
select * from sales partition(p1);

修改分区表
alter table sales add partition p4 values less than(maxvalue);--再insert3000就不会报错

二、散列分区:HASH

创建分区表:
create table my_emp(
empno number,
ename varchar2(10)
)
partition by hash(empno)
(
partition p1,partition p2
)

测试:
insert into my_emp values(1,'A');
insert into my_emp values(2,'B');
insert into my_emp values(3,'C');
查看
select * from my_emp partition(p1);

三、列表分区:LIST

创建分区表:
例如:
create table personCity(
id number,
name varchar2(10),
city varchar2(10)
)
partition by list(city)
(
partition pdb values('开封','商丘'),
partition pxb values('洛阳'),
partition pnb values('许昌'),
partition pbb values('新乡')
);

测试:
insert into personCity values(1,'A','开封');
insert into personCity values(2,'B','商丘');
insert into personCity values(3,'C','洛阳');
查看
select * from personCity partition(p1);

四、复合分区

①范围分区+散列分区/②范围分区+列表分区
partition by range(columnxxx1)
subpartition by hash(columnxxx2)
subpartitions 4
①
例如:
create table student(
sno number,sname varchar2(10)
)
partition by range(sno)
subpartition by hash(sname)
subpartitions 4
(
partition p1 values less than(1000),
partition p1 values less than(2000),
partition p1 values less than(maxvalue)
);
--subpartition 4 代表每一个范围分区都有4个散列分区
select * from user_tab_partitions a where a.table_name='STUDENT';

五、引用分区：主从表

主表:
create table student2(
   stu_id number primary key,
   stu_name varchar(2),
   grade varchar2(10)
)
partition by range(stu_id)
(
   partition par_stu1 values less than(1000),
   partition par_stu1 values less than(2000),
   partition par_stu1 values less than(maxvalue)
);
从表:
create table score2(
   id number parmary key,
   stu_id number not null,
   couse_name varchar2(20),
   score number,
   constraint fk_couse foreign key(stu_id) references student2(stu_id)
)
partition by reference(fk_couse);
--这样从表就会也有分区

insert into student2 values(1,'AA','一年级');
insert into student2 values(2,'BB','二年级');
insert into score2 values(1,1,'语文',70);
insert into score2 values(2,22,'数学',70);

六、间隔分区:

按照时间:
create table sale_detail(
   sale_detail_id number,
   product_id number,
   quantity number,
   sale_date date
)
partition by range(sale_date)
interval(numtoyminterval(1,'month'))--达到一月就再分区
(
   partition p_201006 values less than (to_date('20100601','yyyymmdd'))
);

insert into sale_detail values(1,100,20,to_date('20100121','yyyymmdd'));
insert into sale_detail values(2,100,30,to_date('20100621','yyyymmdd'));
insert into sale_detail values(3,100,30,to_date('20100721','yyyymmdd'));
insert into sale_detail values(4,100,50,to_date('20101121','yyyymmdd'));
select * from sale_detail partition(p_201006);
select * from ser_tab_partitions a where a.table_name in ('SALE_DETAIL');

七、虚拟列分区

create table sale(
   sale_id number parmary key,
   product_id number,
   price number,
   quantity number,
   sale_date date,
   total_price as (price * quantity) virtual                --虚拟列
)
partition by range(total_price)
(
   partition p_1000 values less than(1000),
   partition p_2000 values less than(2000),
   partition p_max values less than(maxvalue)
)

insert into sale(sale_id,product_id,price,quantity,sale_date) values
(1,100,15,20,sysdate);