oracle分区表.docx

一、分区的优点： 1 、增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用； 2 、减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少； 3 、维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多； 4 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能； 5 、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快； 6 、分区对用户透明，最终用户感觉不到分区的存在。 二、分区的管理： 分区的很多操作都会导致索引的失效，需要重建索引。不过如果带上update indexes 可以避免。 1 、分区表的建立：-range分区CREATE TABLE p_range (sale_date DATE NOT NULL ) PARTITION BY RANGE (sale_date) (PARTITION p1 VALUES LESS THAN (TO_DATE(1999-04-01,YYYY-MM-DD) TABLESPACE system, PARTITION p2 VALUES LESS THAN (TO_DATE(1999-07-01,YYYY-MM-DD)TABLESPACE system, PARTITION pmax VALUES LESS THAN (maxvalue)TABLESPACE system); -list分区CREATE TABLE p_list(sale_date varchar2(10) NOT NULL ) PARTITION BY list (sale_date) (PARTITION p1 VALUES (20121111) TABLESPACE system, PARTITION p2 VALUES (20121112)TABLESPACE system,partition pdefault values (default)TABLESPACE system); -hash分区CREATE TABLE p_hash (sale_date DATE NOT NULL ) PARTITION BY hash (sale_date) (PARTITION p1 TABLESPACE system, PARTITION p2 TABLESPACE system, PARTITION p3 TABLESPACE system);-复合分区CREATE TABLE p_box (i NUMBER, j NUMBER) PARTITION BY RANGE(j) SUBPARTITION BY HASH(i) (PARTITION p1 VALUES LESS THAN (10) SUBPARTITION t2_pls1 SUBPARTITION t2_pls2, PARTITION p2 VALUES LESS THAN (20) SUBPARTITION t2_p2s1 SUBPARTITION t2_p2s2); 2 、增加分区(ADD)： 1、如果list分区有default或者range分区有maxvalue，则不能进行add partition操作2、add partition的值必须大于所有分区的值。 alter table p_list add partition p_3 values(20121113); 3 、截断分区(TRUNCATE)： alter table p_list truncate partition p_3 ; 4、删除分区(DROP)alter table p_list drop partition p_3 ; 删除子分区;alter table p_list drop subpartition xxx; 5、分裂分区（split）通常我们会用来拆分MAXVALUE/DEFAULT分区。 alter table p_range split partition pmax at (to_date(2012-11-13,yyyy-mm-dd) into (partition p_3,partition p_max);alter table p_list split partition pdefault values (20121113) into(partition p_3,partition p_defalut);此时会将pmax或pdefault中的20121113值放入P_3，其他的数据会放入p_max或p_defalut。6、交换分区(exchange) 速度很快，可以是分区跟非分区表交换，子分区跟非分区表交换，组合分区跟分区表交换。create table p_u_list(sale_date1 varchar2(10) NOT NULL ) ;insert into p_u_list values(20121111);alter table p_list exchange partition p1 with table p_u_list WITH VALIDATION ;insert into p_u_list values(20121115);alter table p_list exchange partition p1 with table p_u_list WITH VALIDATION;-此时会出错，因为20121115不属于分区平p1，而且做了 WITH VALIDATION检查，如果想成功交换，需加上WITHOUT VALIDATION如果指定WITH VALIDATION(默认) 会对交换进来的数据进行合法检查，看是否符合该分区的规则，WITHOUT VALIDATION 会忽略合法检查(比如ID=12的记录此时可以交换到ID VALUES LESS THAN (10)的分区里)，但如果表上有primary key 或unique 约束的话，指定without validation会被忽略。 7、 合并分区(merge和coalesce)coalesce 仅仅适用于hash分区和复合分区的hash子分区-自动收缩当前的表分区，比如某表当前有5个hash分区，执行coalesce后就变成4个，再执行一次就变成3 个.直至一个。merge 不适用hash分区-如果list分区有default或者range分区有maxvalue，则不能进行merge操作alter table p_hash coalesce partition;alter table p_list merge partitions p1,p2 into partition P0; 8 重命名分区（rename）Alter table xxx rename partition/subpartition p1 to p1_new; 9.移动分区（move） 改变分区的表空间 alter table p_list move partition p1 tablespace sysaux; 10.EXPORT分区： exp sales/sales_password tables=sales:sales1999_q1 rows=Y file=sales1999_q1.dmp 11.IMPORT分区： imp sales/sales_password FILE =sales1999_q1.dmp TABLES = (sales:sales1999_q1) IGNORE=y 12. 修改分区默认属性(modify default attributes)修改表属性：alter table xxx modify default attributes 修改分区属性(适用于组合分区)：alter table xxx modify default attributes for partition p1 只对以后添加的分区产生影响，适用于所有分区，其中hash分区只能修改表空间属性。如：Alter table xxx modify default attributes tablespace users;13. 修改子分区模板属性(set subpartition template)Alter table xxx set subpartition template ();仅影响以后的子分区，当前的子分区属性不会改变如：Alter table xxx set subpartition template(partition p1 tablespace tbs_1,Partition p2 tablespace tbs_2);如果要取消掉子分区模板：Alter table xxx set subpartition template (); 三、分区索引：全局索引和本地索引(分区索引)一般使用LOCAL索引较为方便，而且维护代价较低，并且LOCAL索引是在分区的基础上去创建索引，类似于在一个子表内部去创建索引，这样开销主要是区分分区上，很规范的管理起来，在OLAP系统中应用很广泛；而相对的GLOBAL索引是全局类型的索引，根据实际情况可以调整分区的类别，而并非按照分区结构一一定义，相对维护代价较高一些，在OLTP环境用得相对较多 1、建立普通的索引 create index no_part_idx on p_list(sale_date); 2、建立本地分区索引create index local_part_idx on p_list(sale_date) local; 3、建立全局分区索引create index glo_part_idx on p_list(sale_date)global partition by range(sale_date)(PARTITION p1 VALUES less than (20121111) TABLESPACE system, PARTITION p2 VALUES less than (20121112)TABLESPACE system,PARTITION pmax VALUES less than (maxvalue)TABLESPACE system);删除索引：对于分区索引的删除，local index 不能指定分区名称，需单独的删除分区索引。local index 对应的分区会伴随着data分区的删除而一起被删除。globalpartition index 可以指定分区名称，删除某一分区。但是有一点要注意，如果该分区不为空，则会导致更高一级的索引分区被置为UNUSABLE 。 ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句将导致part_02 状态为UNUSABLE本地分区索引的状态改变-整个索引不可用ALTER INDEXindex_tmp_partition_list UNUSABLE;-单个分区索引不可用ALTER INDEXindex_tmp_partition_list MODIFY PARTITION p_20110816 USABLE;-分区不可用之后，需要重建索引alter indexindex_tmp_partition_list rebuild partition p_20110816;-查看分区索引的状态selectindex_name,partition_name,tablespace_name,statusfromuser_ind_partitionswherelower(index_name)=index_tmp_partition_list-create table v1(vid varchar2(100),vn1 number,vdate date,xh number generated always as(mod(trunc(vdate )-to_date(20010101 00:00:00,yyyymmdd hh24:mi:ss),8)partition by list(xh)(partition v1 values (0) tablespace isbdata,partition v2 values (1) tablespace isbidx, partition v3 values (2) tablespace isbdata,partition v4 values (3) tablespace isbidx, partition v5 values (4) tablespace isbdata,partition v6 values (5) tablespace isbidx, partition v7 values (6) tablespace isbdata,partition v8 values (7) tablespace isbidx)insert into v1(vid,vn1,vdate) select dbms_random.string(A, 30), trunc(dbms_random.value(2, 100), aa.gg from (select sysdate + level gg from dual connect by level = 8) aa, (select level from dual connect by level = 100) bb select * from v1 order by xhselect (case when xh=7 then 0 else xh+1 end)+1 vh,vdate from ( select vdate,xh from v1order by vdate desc )where rownum=1 -1 6 2011-11-1 15:05:01 the now time and partition select table_name,partition_name from USER_TAB_PARTITIONS gghwhere table_name=V1and ggh.partition_position=6-1 V1 V6select * from v1 partition (v6)- do it alter table v1 truncate partition v6 ;- can not use partition purgeselect * from v1 where to_char(vdate,yyyymmdd)=20111027- cannot use partition purgeexplain plan for select * from v1where xh+0+1-1 = (mod(trunc(to_date(20111027, yyyymmdd) - to_date(20010101 00:00:00, yyyymmdd hh24:mi:ss), 8) and to_char(vdate, yyyymmdd) = 20111027 -use partition purgeselect * from v1where xh = (mod(trunc(to_date(20111027, yyyymmdd) - to_date(20010101 00:00:00, yyyymmdd hh24:mi:ss), 8) and to_char(vdate, yyyymmdd) = 20111027楼主，给你模拟了一个。to 1：分区能够将同一份数据分散到多个表空间，同时你可以将多个表空间分散到各个磁盘上，这样在插入的时候io就分散到了各个磁盘上，可以提高插入的效率（其实就是避免所有数据都一起插入到一个磁盘上，造成竞争）同样的，数据分散到多个表空间，并将多个表空间分散到各个磁盘，在读取最新的一个分区的数据时由于数据被打散到多个磁盘，也可以达到分散io的效果，当然这些都只是理论上的。to 2：可以利用oracle11g的虚拟列分区，设置一个最早日期比如20010101，然后将日后的天数减去这个天数然后取8模，然后对这个取模的列分区，如果在10g中就增加一列，实例代码写好了如上。to 3：你要显示的使用分区字段，而且不能用函数，演示如上。-复合分区 CREATE TABLE composite_example