1按需创建分区,可以自动扩展分区
create table f_sales(sales_amt number,d_date date)partition by range (d_date) interval(numtoyminterval(1,'YEAR')) (partition p1 values less than(to_date('01-jan-2010','dd-mon-yyyy')));
2通过partition by reference子句来指定子表与父表同样的方式进行分区
create table orders( order_id number,order_date date,constraint order_pk primary key(order_id))partition by range(order_date)(partition p10 values less than(to_date('01-jan-2010','dd-mon-yyyy')));
create tale order_items(line_id number,order_id number not null,constraint order_items_pk primary key(line_id,order_id),constraint order_items_fk1 foreign key(order_id) references orders) partition by reference(order_items_fk1);
3基于虚拟列的分区
create table emp(emp_id numbser,salary number,comm_pct number,commission generated always as (salary*comm_pct))partition by range(commission)(
partition p1 values less than (1000),partition p2 values less than(2000),partition p3 valeus less than (maxvalue));
4自定义插入到哪个分区,系统分区
create table test (id int ,name varchar2(100)) partition by system(partition p1,partition p2,partition p3);
insert into test partition(p1) values(1,'test'):
5更改分区键,让记录能更换分区,而不是报错
alter table test enable row movement;
6交换分区,把一个表变成一个分区,或将一个分区变成一个表(实验)
create table f_sales(sales_amt number, d_date_id number)
partition by range(d_date_id)
(partition p_2007 values less than(20080101),
partition p_2008 values less than (20090101),
partition p_2009 values less than (20100101));
create bitmap index d_date_id_fk1 on f_sales(d_date_id) local;
alter table f_sales add partition p_2010 values less than(20110101);
create table workpart(sales_amt number,d_date_id number);
insert into workpart values(100,20100101);
insert into workpart values(200,20100102);
create bitmap index d_date_id_fk2 on workpart(d_date_id);
alter table f_sales exchange partition p_2010 with table workpart including indexes without validation;
select * from workpart;
select * from f_sales partition(p_2010);
select index_name,partition_name,status from user_ind_partitions where index_name like 'D_DATE%';
7拆分分区
alter table f_sales split partition p_2010 at (20100601) into (partition p2010_a,partition p2010) update indexes;
select * from user_part_tables a where a.table_name='F_SALES';
select * from user_tab_partitions a where a.table_name='F_SALES';
insert into f_sales values(300,20100401);
insert into f_sales values(400,20101001);
select * from f_sales partition(p2010_a);
select * from f_sales partition(p2010);
上面的拆分的范围分区
下面的是拆分的列表分区
alter table f_sales split partition reg_mid values('IA','KS','MI','MN') into (partition reg_mid_a,partition reg_mid_b);
8合并分区
alter table f_sales merge partitions p2010_a,p2010 into partition pp2010;
select * from user_tab_partitions a where a.table_name='F_SALES';
在合并的时候分区的名称是可以指定的
select index_name,partition_name,status from user_ind_partitions where index_name like 'D_DATE%';
1 D_DATE_ID_FK1 PP2010 UNUSABLE
2 D_DATE_ID_FK1 P_2007 USABLE
3 D_DATE_ID_FK1 P_2008 USABLE
4 D_DATE_ID_FK1 P_2009 USABLE
合并后的分区,本地索引是失效了的,使用下面的语句重建本地索引
alter table f_sales modify partition pp2010 rebuild unusable local indexes;
9删除分区
alter table f_sales drop partition pp2010;
10删除一个分区中的记录
alter table f_sales truncate partition p_2008;
delete from f_sales partition(p_2008);
11创建本地索引,本地索引分区索引的分区与表的分区不在一起
create index i_f_sales on f_sales(d_date_id) local(partition p2009 tablespace idx1,partition p2010 tablespace idx2);
本地分区索引分有前缀的本地分区索引和无前缀的局部索引,有前缀的局部分区索引就是索引最左侧的列是分区列,无前缀索引就是最左侧的列不是分区列
可以查看
select index_name,table_name,alignment,locality from user_part_indexs;查看索引是否是前缀索引
12创建全局索引
我们在一个不是分区键的字段查询分区表,想要在这个字段上创建一个索引来提高性能,创建全局索引,全局索引有哈希分区或范围全局索引,
create index f_sales on f_sales(sales_id) global partition by range(sales_id) (partition pg1 values less than (100),partition pg2 values less than (200),partition pg3 values less than (maxvalue));
create index f_sales_idx on f_sales(count) global partition by hash(count) partitions 3;