oracle11g分区表

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;