一、什么时候可以需要分区
1、表大小达到2g左右；
2、表数据达到1千万行，并且操作越来越慢；
3、随着数据增长效率受到很大影响；
二、partitioning by range。
SQL> create table yxl_range(a number,b number)
2 partition by range(b)
3 (partition yxl_range_b_1 values less than (1),
4 partition yxl_range_b_2 values less than (10),
5 partition yxl_range_b_3 values less than (maxvalue)
6 );
Table created
SQL> drop table yxl_range;
Table dropped
三、partitioning by list
SQL> create table yxl_list(a number,b varchar2(2))
2 partition by list(b)(
3 partition yxl_list_b_1 values ('aa'),
4 partition yxl_list_b_2 values ('bb'),
5 partition yxl_list_b_3 values (default)
6 )
7 ;
Table created
SQL> drop table yxl_list;
Table dropped
四、partitioning by hash
--建立3个表空间
SQL> create tablespace yxl_hash_1
2 datafile '/tmp/yxl_hash_1.dbf' size 10m;
Tablespace created
SQL> create tablespace yxl_hash_2
2 datafile '/tmp/yxl_hash_2.dbf' size 10m
3 ;
Tablespace created
SQL> create tablespace yxl_hash_3
2 datafile '/tmp/yxl_hash_3.dbf' size 10m
3 ;
Tablespace created
SQL> create table yxl_hash(a number,b number)
2 partition by hash (b)(
3 partition yxl_hash_b_1 tablespace yxl_hash_1,
4 partition yxl_hash_b_2 tablespace yxl_hash_2,
5 partition yxl_hash_b_3 tablespace yxl_hash_3
6 );
Table created
SQL> drop table yxl_hash;
Table dropped
SQL> drop tablespace yxl_hash_1 including contents and datafiles;
Tablespace dropped
SQL> drop tablespace yxl_hash_2 including contents and datafiles;
Tablespace dropped
SQL> drop tablespace yxl_hash_3 including contents and datafiles;
Tablespace dropped
五、partitioning a table in mutiple ways.（复合分区）
SQL> create table yxl_multi
2 (a number, b number,c varchar2(2))
3 partition by range(b)
4 subpartition by list(c)(
5 partition yxl_multi_b_1 values less than (1)
6 (subpartition yxl_multi_c_1 values ('aa'),
7 subpartition yxl_multi_c_2 values ('bb')),
8 partition yxl_multi_b_2 values less than (maxvalue)
9 (subpartition yxl_multi_cc_1 values ('aa'),
10 subpartition yxl_multi_cc_2 values ('bb'))
11 );
Table created
SQL> drop table yxl_multi;
Table dropped
六、creating partitions on demand.(按照要求自动创建分区 10g)。
SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
SQL> create table f_sales(sales_amt number ,d_date date)
2 partition by range (d_date)
3 interval(numtoyminterval(1, 'YEAR'))
4 (partition p1 values less than (to_date('2008-01-01','yyyy-mm-dd')));
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('2008-01-01','yyyy-mm-dd')))
ORA-00922: 选项缺失或无效
SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> create table yxl_inc(a number,b date)
2 partition by range(b)
3 interval(numtoyminterval(1,'YEAR'))
4 (partition yxl_inc_1 values less than (to_date('2008-1-1','yyyy-mm-dd')));
Table created
complete
七、partitioning on a vitual column（虚拟列上分区 11g)
SQL> create table emp1 (
2 emp_id number
3 ,salary number
4 ,comm_pct number
5 ,commission generated always as (salary*comm_pct)
6 )
7 partition by range(commission)
8 (partition p1 values less than (1000)
9 ,partition p2 values less than (2000)
10 ,partition p3 values less than (maxvalue));
Table created
八、Application-Controlled Partitioning （系统控制分区 11g)
SQL> create table apps
2 (app_id number
3 ,app_amnt number)
4 partition by system
5 (partition p1
6 ,partition p2
7 ,partition p3);
Table created
SQL> insert into apps values(1,100);
insert into apps values(1,100)
ORA-14701: 对于按“系统”方法进行分区的表, 必须对 DML 使用分区扩展名或绑定变量
SQL> insert into apps partition(p1) values(1,100);
1 row inserted
九、分区的同时指定表空间。
create table f_regs
(reg_count number
,d_date_id number
)
partition by range (d_date_id)(
partition p_2009 values less than (20100101) tablespace p_2009_tbsp,
partition p_2010 values less than (20110101) tablespace p_2010_tbsp,
partition p_max values less than (maxvalue) tablespace p_max_tbsp);
--如果你已经创建了分区表，则可以采取下面语句把某个分区移动到某个表空间。
alter table f_regs move partition p_2009 tablespace tablespace p_2009_tbsp;
--如果你移动了某个分区，则必须rebuild那个分区的索引。为什么呢？因为移动表空间后，行的rowid发生了变化，所以必须rebuild索引。
--在rebuild index的同时，可以同时移动到某人表空间。
alter index d_date_id_fk1 rebuild partition p_2009 tablespace tablespace p_2009_tbsp;
十、Automatically Moving Updated Rows
SQL> create table yxl01 (a number ,b number)
2 partition by range (b)(
3 partition p1 values less than (10),
4 partition p2 values less than (20),
5 partition p3 values less than (maxvalue));
Table created
SQL> insert into yxl01 values(1,9);
1 row inserted
SQL> insert into yxl01 values(2,19);
1 row inserted
SQL> commit;
Commit complete
SQL> update yxl01 set b=11 where a = 1;
update yxl01 set b=11 where a = 1
ORA-14402: 更新分区关键字列将导致分区的更改
SQL> alter table yxl01 enable row movement;
Table altered
SQL> update yxl01 set b=11 where a = 1;
1 row updated
SQL> commit;
Commit complete
SQL> alter table yxl01 disable row movement;
Table altered
十一、对已经存在的表进行分区操作。
步骤：1、用create table语句新建一个分区表。
2、drop或者rename旧表。
3、把新建的分区表rename成旧表。
SQL> create table yxl01_new
2 partition by range (b)(
3 partition p1 values less than (10),
4 partition p2 values less than (20),
5 partition p3 values less than (maxvalue))
6 nologging
7 as select * from yxl01
8 /
Table created
SQL> drop table yxl01;
Table dropped
SQL> rename yxl01_new to yxl01;
Table renamed
十二、给分区表添加分区。
1、range分区表添加分区。分区表定时
alter table f_regs add partition p2011 values less than (20120101) tablespace p_tbsp;
注：分区表定义时最大的分区表没有采用 less than (maxvalue)；如果采用了，需要切分最大的分区表，而不是添加分区操作。
2、给list分区表添加分区。
alter table f_sales add partition reg_east values('GA');
注：分区表定义时没有采用default选项，如果采用了，则需要切分分区表，而不是添加分区表操作。
3、给hash分区表添加分区。
alter table browns add partition new_part tablespace p_tbsp update indexes;
注：没有没有采用update indexes选项，则需要rebuild分区表的索引。
十三、Exchanging a Partition with an Existing Table
SQL> create table f_sales
2 (sales_amt number, d_date_id number)
3 partition by range (d_date_id)
4 (partition p_2007 values less than (20080101),
5 partition p_2008 values less than (20090101),
6 partition p_2009 values less than (20100101)
7 );
Table created
SQL> create bitmap index d_date_id_fk1
2 on f_sales(d_date_id) local;
Index created
SQL> alter table f_sales add partition p_2010 values less than (20110101);
Table altered
SQL> create table workpart(sales_amt number, d_date_id number);
Table created
SQL> insert into workpart values(100,20100101);
1 row inserted
SQL> insert into workpart values(120,20100102);
1 row inserted
SQL> commit;
Commit complete
SQL> create bitmap index d_date_id_fk2 on workpart(d_date_id);
Index created
SQL> alter table f_sales exchange partition p_2010
2 with table workpart including indexes without validation;
Table altered
SQL> select * from f_sales;
SALES_AMT D_DATE_ID
---------- ----------
100 20100101
120 20100102
十四、重命名分区
SQL> alter table f_sales rename partition p_2010 to p_2010_re;
Table altered
十五、切分分区
SQL> alter table f_sales
2 split partition p_2010_re at (20100601) into
3 (partition p_2010_r,partition p_2010_b) update indexes
4 ;
Table altered
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
D_DATE_ID_FK1 P_2007 USABLE
D_DATE_ID_FK1 P_2008 USABLE
D_DATE_ID_FK1 P_2009 USABLE
D_DATE_ID_FK1 P_2010_B USABLE
D_DATE_ID_FK1 P_2010_R USABLE
注：如果没有update indexes选项，则需要rebuild index操作。
十六、合并分区。
SQL> alter table f_sales
2 merge partitions p_2010_r,p_2010_b into partition p_2010;
Table altered
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
D_DATE_ID_FK1 P_2007 USABLE
D_DATE_ID_FK1 P_2008 USABLE
D_DATE_ID_FK1 P_2009 USABLE
D_DATE_ID_FK1 P_2010 UNUSABLE
SQL> alter index D_DATE_ID_FK1 rebuild partition p_2010;
Index altered
注：当然上面使用时可以加上 update indexes选项。
十六、删除分区
alter table f_sales drop partition p_2008;
alter table f_sales drop subpartition p2_south;
十七、快速删除分区数据。
SQL> alter table f_sales truncate partition p_2009_re;
Table truncated
十八、分区统计信息
SQL> exec dbms_stats.gather_table_stats('HBJB_KF_BDGS','F_SALES','P_2008');
PL/SQL procedure successfully completed
十九、建立索引。
1.Local Index
create index f_regs_idx1 on f_regs(reg_count) local;
或者：
create index f_regs_idx1 on f_regs(reg_count) local
(partition p2009 tablespace idx1
,partition p2010 tablespace idx2);
2.Global Index
reate index f_count_idx1 on f_regs(reg_count)
global partition by hash(reg_count) partitions 3;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16396910/viewspace-1029625/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
SQL*PLUS命令的使用大全
下一篇：
oracle“SQL Trace”简介
请登录后发表评论
登录
全部评论
