时间：2021-07-01 10:21:17 帮助过：11人阅读
目录
Oracle分区 0
一、Oracle分区理论知识 1
二、分区表的实现方式 1
1、范围分区（range partition table） 1
2、列表分区（list partitioning） 3
3、散列分区（hash partitioning） 4
4、间隔分区（interval partitioning） 5
5、引用分区（reference partitioning） 6
6、组合分区（composite partitioning） 7
7、行移动（row movement） 10
三、普通表转换为分区表方法 10
1、导入、导出（Export/import method）方式转换分区表 10
2、插入（Insert with a subquery method）方式转换分区表 11
3、交换分区（Partition exchange method）方式转换分区数据 14
4、在线重定义（DBMS_REDEFINITION）转换分区数据 16
四、分区表的管理 19
1、分区表添加新分区、分以下2种情况： 19
2、合并分区merge 23
3、移动分区 24
4、Truncate分区 24
5、Drop分区 24
6、接合分区(coalesca) 25
7、重命名表分区 25
五、分区表索引 25
1、local局部索引 26
2、global索引 27
3、rebuild索引的问题 29
数据库分区是每种数据库都需具备的关键功能之一，oracle数据库在oracle8.0中引入分区概念，过程是物理上将一个表或索引分解成多个部分，逻辑上还是一个表或索引对象。分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。使用分区可以提高数据可用性、减轻DBA管理负担、改善某些查询的性能、减少个别段的竞争。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（segment），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
分区对于OLAP系统性能提升效果显著，尤其分区与并行连用可以起到明显效果；对于OLTP系统要慎用分区，分区对于OLTP系统的性能改善可能起不到明显作用，反而可能影响系统性能，但是OLTP系统可以通过分区来均衡热段对象的竞争，此种方法还是非常可行的。
When to Partition a Table（什么时候需要分区表）、官网的2个建议如下：
| · Tables greater than 2GB should always be considered for partitioning. · Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month‘s data is updatable and the other 11 months are read only. |
Oracle分区表的优缺点：
| 优点： （1）提高可用性。 缺点： （1）oracle中已经存在的表没有办法直接转化为分区表。不过 Oracle 提供了在线重定义表的功能可以间接实现普通表到分区表的转化。 |
oracle分区表的类型：
| （1）范围分区（range partition table）； （4）间隔分区（interval partitioning）； （5）引用分区（reference partitioning）； |
Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。如果插入的数据无法映射到任何一个指定的分区，此时insert会报错，因此，使用范围分区时要求DBA要实时注意分区创建情况，及时或预创建未来要插入数据的分区，另一种做法就是指定一个maxvalue分区，将所有无法映射到具体分区的insert值插入到maxvalue分区中。另外需要注意的时，分区区间是严格小于某一个值，而不是小于或等于某一个值。
当使用范围分区时，请考虑以下几个规则：
| 1)每一个分区都必须有一个VALUES LESS THAN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。 2)所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。 3)在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THAN的值，同时包括空值。 |
如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。
在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。
| 在没有maxsize的分区表中添加不符合分区规则的表数据： SQL>insert into p_dba values(999999,to_date(‘2012-12-29‘,‘yyyy-mm-dd‘)); insert into p_dba values(999999,to_date(‘2012-12-29‘,‘yyyy-mm-dd‘)) * 第 1 行出现错误: ORA-14400: 插入的分区关键字未映射到任何分区 通过这个测试可以清楚，如果插入的数据不满足分区规则，会报ORA-14400错误。 |
例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：
| CREATETABLE CUSTOMER ( CUSTOMER_ID NUMBERNOTNULLPRIMARYKEY, FIRST_NAME VARCHAR2(30)NOTNULL, LAST_NAME VARCHAR2(30)NOTNULL, PHONE VARCHAR2(15)NOTNULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITIONBYRANGE(CUSTOMER_ID) ( PARTITION CUS_PART1 VALUESLESSTHAN(100000)TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUESLESSTHAN(200000)TABLESPACE CUS_TS02 ); |
例2：按时间划分
| CREATETABLE ORDER_ACTIVITIES ( ORDER_ID NUMBER(7)NOTNULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITIONBYRANGE(ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUESLESSTHAN(TO_DATE(‘01- MAY -2003‘,‘DD-MON-YYYY‘))TABLESPACE ORD_TS01, PARTITION ORD_ACT_PART02 VALUESLESSTHAN(TO_DATE(‘01-JUN-2003‘,‘DD-MON-YYYY‘))TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUESLESSTHAN(TO_DATE(‘01-JUL-2003‘,‘DD-MON-YYYY‘))TABLESPACE ORD_TS03 ); |
例3：带MAXVALUE值得分区
| CREATETABLE RangeTable ( idd INTPRIMARYKEY, iNAME VARCHAR(10), grade INT ) PARTITIONBYRANGE(grade) ( PARTITION part1 VALUESLESSTHAN(1000)TABLESPACE Part1_tb, PARTITION part2 VALUESLESSTHAN(MAXVALUE)TABLESPACE Part2_tb ); |
List分区是oracle 9ir1的一个新特性，他提供了这样的功能，可以根据离散的值列表来指定一行位于哪个区。list分区需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。这里需要注意的是一旦一个list分区包含了一个default分区，就不能再向其中添加新的分区了，此时如果想要添加新的分区需要首先删除default分区，再添加新的分区，然后再回填default分区。
例1：
| CREATETABLE PROBLEM_TICKETS ( PROBLEM_ID NUMBER(7)NOTNULLPRIMARYKEY, DESCRIPTIONVARCHAR2(2000), CUSTOMER_ID NUMBER(7)NOTNULL, DATE_ENTERED DATENOTNULL, STATUS VARCHAR2(20) ) PARTITIONBYLIST(STATUS) ( PARTITION PROB_ACTIVE VALUES(‘ACTIVE‘)TABLESPACE PROB_TS01, PARTITION PROB_INACTIVE VALUES(‘INACTIVE‘)TABLESPACE PROB_TS02 ); |
例2：
| CREATETABLE ListTable ( idINTPRIMARYKEY, nameVARCHAR(20), area VARCHAR(10) ) PARTITIONBYLIST(area) ( PARTITION part1 VALUES(‘guangdong‘,‘beijing‘)TABLESPACE Part1_tb, PARTITION part2 VALUES(‘shanghai‘,‘nanjing‘)TABLESPACE Part2_tb ); |
例3：
| createtable custaddr ( idvarchar2(15byte)notnull, areacode varchar2(4byte) ) partitionbylist(areacode) (partition t_list025 values(‘025‘), partition t_list372 values(‘372‘), partition t_list510 values(‘510‘), partition p_other values(default) ); |
对于一个表执行散列分区时，oracle会对分区键应用一个散列函数，以确定数据应当放入n个分区中中的哪一个分区，oracle建立n是2的一个幂。
散列分区设计为能使数据很好的分布在多个不同的I/O设备上，或者将数据聚集到更可管理的块上。为表选择散列键应当是唯一的一个列或一组列，或者有足够多的相异值，以便能在多个分区上均匀分布。如果你选择一个只有4个相异值的列，并使用两个分区，那么最后的结果很可能是所有的行都散列在同一个分区上，这就有悖于分区的最初目标。
对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
如果改变散列分区的个数，数据会在所有分区中重新分布（向一个散列分区表增加或删除一个分区时，将导致所有分区重写，因为现在的每一行都可能属于一个不同的分区）。
例1：
| CREATETABLE HASH_TABLE ( COL NUMBER(8), INF VARCHAR2(100) ) PARTITIONBYHASH(COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 ); |
简写：
| CREATETABLE emp ( empno NUMBER(4), ename VARCHAR2(30), sal NUMBER ) PARTITIONBYHASH(empno)PARTITIONS8 STOREIN(emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8); |
例2：
| createtabletest ( transaction_id numberprimarykey, item_id number(8)notnull ) partitionbyhash(transaction_id) ( partition part_01 tablespace tablespace01, partition part_02 tablespace tablespace02, partition part_03 tablespace tablespace03 ); |
间隔分区是oracle11gR1及以上版本新增的新特性，它与分区分区很类似。事实上，间隔分区就是以一个区间分区表为起点，不过是在它的定义中增加了一个规则（间隔），是数据库知道什么时候才能自动增加新的分区。间隔分区的目标是当且仅当存在一个给定分区的数据而且这些数据加入数据库时才为数据创建新的分区。换句话说，不需要在数据库中预先创建分区，而是当插入数据时让数据库自动创建分区。
使用间隔分区，首先从一个没有maxvalue分区的区间分区表开始，指定一个要增加至上界的间隔（上界是一个最大值，如果达到这个最大值，分区表就要创建一个新的分区）。这个分区表要按某一列进行范围分区，而这一列应该能增加number或interval类型的值。对于任何合适的现有范围分区都可以使用间隔分区，也就是说，可以使用alter将一个现有的范围分区表修改成间隔分区表，也可以使用create table创建一个新的间隔分区表。
采用间隔分区时，可以创建一张表，同时指定一个分区和一个间隔，数据库就会在数据到来时创建各个分区。数据库并不是预先创建所有可能的分区，因为这是不现实的，不过随着各个数据行的到来，数据库会查看对应分区是否存在，如果需要，数据库会自动创建分区。
间隔分区采用oracle系统自动命名，如果需要对分区名称进行控制，需要人为重命名。另外需要注意的是间隔分区的边界限制与范围分区是不同的（这里不做过多介绍）。
例1：
| createtable audit_trail ( ts timestamp, datavarchar2(30) ) partitionbyrange(ts)interval(numtoyminterval(1,‘month‘))storein(users,example) ( partition p1valueslessthan to_date(‘01_01_2010‘,‘dd-mm-yyyy‘)) ) |
引用分区从oracle11gR2引入，它处理的是父/子对等分区的问题。即以某种方式对父表分区，使得各个子表分区分别与一个父表分区存在一对一关系。这对于应用而言至关重要，父表按照某一列进行分区，子表不一定有父表对应的列，所以子表无法根据父表分区情况进行划分，这对于后期维护会带来必要的困难。
在引用分区引入之前，开发人员必须对数据逆规范化（denormalize），具体做法是：把父表的分区列复制到子表，这回引入数据冗余，相应地会带来冗余数据的一系列常见问题，比如存储开销、数据加载资源增加、级联更新问题等等。另外，如果在数据库中启用了外键约束，会发现无法截断或删除父表中原来的分区（清楚老数据）。
例1：逆规范化管理
| createtable orders ( order# numberprimarykey, order_date date, datavarchar2(30) ) enablerowmovement partitionbyrange(order_date) ( partition part_2009 valueslessthen(to_date(‘2010-01-01‘,‘yyyy-mm-dd‘)), partition part_2010 valueslessthen(to_date(‘2011-01-01‘,‘yyyy-mm-dd‘)) ) insertinto orders values(1,to_date(‘2009-05-05‘,‘yyyy-mm-dd‘),‘xxx‘); insertinto orders values(1,to_date(‘2010-05-05‘,‘yyyy-mm-dd‘),‘xxx‘); createtable order_line_items ( order# number, line# number, order_date date,--父表分区字段 datavarchar2(30), constraint c1_pk primarykey(order#,line#), constraint c1_fk_p foreignkey(order#)references orders ) enablerowmovement partitionbyrange(order_date) ( partition part_2009 valueslessthen(to_date(‘2010-01-01‘,‘yyyy-mm-dd‘)), partition part_2010 valueslessthen(to_date(‘2011-01-01‘,‘yyyy-mm-dd‘)) ) insertinto order_line_items values(1,1,to_date(‘2009-05-05‘,‘yyyy-mm-dd‘),‘yyy‘); insertinto order_line_items values(1,1,to_date(‘2010-05-05‘,‘yyyy-mm-dd‘),‘yyy‘); 删除part_2009分区： altertable order_line_items droppartition part_2009; altertable orders droppartition part_2009; altertable orders droppartition part_2009 * ERRORat line 1: ORA_02266:unique/primary keys intablereferencedbyenabledforeign keys |
采用引用分区，子表会继承父表的分区机制，而不必对分区键逆规范化，而且更重要的是，他会让数据库了解这个子表与父表之间存在对等分区特点。也就是说，截断或删除子表分区时，也能删除或截断父表分区，因此引用分区很适合OLAP类型数据库。另外需要注意的是，如果父表使用了行移动，那么相应地子表页必须启用行移动功能。
例2：引用分区
| createtable orders ( order# numberprimarykey, order_date date, datavarchar2(30) ) enablerowmovement partitionbyrange(order_date) ( partition part_2009 valueslessthen(to_date(‘2010-01-01‘,‘yyyy-mm-dd‘)), partition part_2010 valueslessthen(to_date(‘2011-01-01‘,‘yyyy-mm-dd‘)) ) insertinto orders values(1,to_date(‘2009-05-05‘,‘yyyy-mm-dd‘),‘xxx‘); insertinto orders values(1,to_date(‘2010-05-05‘,‘yyyy-mm-dd‘),‘xxx‘); createtable order_line_items ( order# number, line# number, datavarchar2(30), constraint c1_pk primarykey(order#,line#), constraint c1_fk_p foreignkey(order#)references orders ) enablerowmovement partitionbyreference(c1_fk_p) insertinto order_line_items values(1,1,‘yyy‘); insertinto order_line_items values(1,2,‘yyy‘); |
注意：在oracle11gR2中，还不支持间隔分区和引用分区连用，即不能对父表为间隔分区的表创建子表的引用分区类型。
组合分区是范围分区、散列分区、列表分区之间的组合。组合分区所采用的方法（也就是可以混合和匹配的分配机制类型）在不同的版本中有所不同。下表列出了oracle的主要版本里可以有哪些组合。表中纵向所列的分区机制是所允许的顶层分区机制，而表中横向列出的分区机制是子分区机制。
| | 区间 | 列表 | 散列 |
| 区间 | Oracle11gR1 | Oracle9iR2 | Oracle9iR1 |
| 列表 | Oracle11gR1 | Oracle11gR1 | Oracle11gR1 |
| 散列 | Oracle11gR2 | Oracle11gR2 | Oracle11gR2 |
使用组合分区时，并没有分区段，而只有子分区段，数据物理存储在子分区段上，分区只是一个逻辑容器。
利用组合分区，就能把数据先按区间分解，如果某表的某个分区仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。每个分区不需要有相同数目的子分区。
例：
| createtabletest ( transaction_id numberprimarykey, transaction_date date ) partitionbyrange(transaction_date)subpartitionbyhash(transaction_id) subpartitions3storein(tablespace01,tablespace02,tablespace03) ( partition part_01 valueslessthan(to_date(‘2009-01-01‘,‘yyyy-mm-dd‘)), partition part_02 valueslessthan(to_date(‘2010-01-01‘,‘yyyy-mm-dd‘)), partition part_03 valueslessthan(maxvalue) ); |
| createtable emp_sub_template (deptno number, empname varchar(32), grade number) partitionbyrange(deptno)subpartitionbyhash(empname) subpartitiontemplate ( subpartition a tablespace ts1, subpartition b tablespace ts2, subpartition c tablespace ts3, subpartition d tablespace ts4 ) ( partition p1 valueslessthan(1000), partition p2 valueslessthan(2000), partition p3 valueslessthan(maxvalue) ); |
| createtable quarterly_regional_sales ( deptno number, item_no varchar2(20), txn_date date, txn_amount number, state varchar2(2)) tablespace ts4 partitionbyrange(txn_date)subpartitionbylist(state) (partition q1_1999 valueslessthan(to_date(‘1-apr-1999‘,‘dd-mon-yyyy‘)) (subpartition q1_1999_northwest values(‘or‘,‘wa‘), subpartition q1_1999_southwest values(‘az‘,‘ut‘,‘nm‘), subpartition q1_1999_northeast values(‘ny‘,‘vm‘,‘nj‘), subpartition q1_1999_southeast values(‘fl‘,‘ga‘), subpartition q1_1999_northcentral values(‘sd‘,‘wi‘), subpartition q1_1999_southcentral values(‘ok‘,‘tx‘) ), partition q2_1999 valueslessthan( to_date(‘1-jul-1999‘,‘dd-mon-yyyy‘)) (subpartition q2_1999_northwest values(‘or‘,‘wa‘), subpartition q2_1999_southwest values(‘az‘,‘ut‘,‘nm‘), subpartition q2_1999_northeast values(‘ny‘,‘vm‘,‘nj‘), subpartition q2_1999_southeast values(‘fl‘,‘ga‘), subpartition q2_1999_northcentral values(‘sd‘,‘wi‘), subpartition q2_1999_southcentral values(‘ok‘,‘tx‘) ), partition q3_1999 valueslessthan(to_date(‘1-oct-1999‘,‘dd-mon-yyyy‘)) (subpartition q3_1999_northwest values(‘or‘,‘wa‘), subpartition q3_1999_southwest values(‘az‘,‘ut‘,‘nm‘), subpartition q3_1999_northeast values(‘ny‘,‘vm‘,‘nj‘), subpartition q3_1999_southeast values(‘fl‘,‘ga‘), subpartition q3_1999_northcentral values(‘sd‘,‘wi‘), subpartition q3_1999_southcentral values(‘ok‘,‘tx‘) ), partition q4_1999 valueslessthan( to_date(‘1-jan-2000‘,‘dd-mon-yyyy‘)) (subpartition q4_1999_northwest values(‘or‘,‘wa‘), subpartition q4_1999_southwest values(‘az‘,‘ut‘,‘nm‘), subpartition q4_1999_northeast values(‘ny‘,‘vm‘,‘nj‘), subpartition q4_1999_southeast values(‘fl‘,‘ga‘), subpartition q4_1999_northcentral values(‘sd‘,‘wi‘), subpartition q4_1999_southcentral values(‘ok‘,‘tx‘) ) ); |
| CREATETABLE SALES ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYLIST(STATUS) ( PARTITION P1 VALUESLESSTHAN(TO_DATE(‘2003-01-01‘,‘YYYY-MM-DD‘))TABLESPACE rptfact2009 ( SUBPARTITION P1SUB1 VALUES(‘ACTIVE‘)TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES(‘INACTIVE‘)TABLESPACE rptfact2009 ), PARTITION P2 VALUESLESSTHAN(TO_DATE(‘2003-03-01‘,‘YYYY-MM-DD‘))TABLESPACE rptfact2009 ( SUBPARTITION P2SUB1 VALUES(‘ACTIVE‘)TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES(‘INACTIVE‘)TABLESPACE rptfact2009 ) ); |
| createtable dinya_test ( transaction_id numberprimarykey, item_id number(8)notnull, item_description varchar2(300), transaction_date date ) partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)subpartitions3storein(dinya_space01,dinya_space02,dinya_space03) ( partition part_01 valueslessthan(to_date(‘2006-01-01‘,‘yyyy-mm-dd‘)), partition part_02 valueslessthan(to_date(‘2010-01-01‘,‘yyyy-mm-dd‘)), partition part_03 valueslessthan(maxvalue) ); |
在前面所述的各种分区机制中，如果对于确定分区的列有update会发生什么？常规情况下，有以下两种可能性：
（1）修改不会导致使用不同的分区，修改的行仍然属于原来的分区。此种情况数据库默认支持。
（2）修改将导致行跨分区移动，即修改的行已经不属于原来的分区。此种情况除非对表启用“行移动”（enable row movement），否则数据库将抛出错误。
| 注意：执行行移动时，在内部就好像是先删除这一行，然后再将其重新插入。这会更新这个表上的索引，删除旧的索引条目，再插入新的索引条目。此时会完成一个delete和insert的相应物理工作。不过，尽管在此执行了删除和插入，oracle看来还是一个update，因此不会导致delete和insert的触发器，而只会触发update触发器。另外，由于外键约束可能不允许，所以delete的子表也不会触发delete触发器。不过，还是要对将要完成的额外工作有所准备，行移动的开销比正常的update昂贵的多。 |
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
不做说明。
这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据，也可以创建好后在insert 进去。 这种方法采用DDL语句，不产生undo，只产生少量redo，建表完成后数据已经在分布到各个分区中。
例：
| 1）创建普通表： CREATETABLE RTable ( idd INT, iNAME VARCHAR(10), grade INT ); 2）插入数据： insertinto rtable(idd,iname,grade)values(1,‘wen‘,1); insertinto rtable(idd,iname,grade)values(1,‘wen‘,2); insertinto rtable(idd,iname,grade)values(1,‘wen‘,3); insertinto rtable(idd,iname,grade)values(1,‘wen‘,4); insertinto rtable(idd,iname,grade)values(1,‘wen‘,5); insertinto rtable(idd,iname,grade)values(1,‘wen‘,6); insertinto rtable(idd,iname,grade)values(1,‘wen‘,7); insertinto rtable(idd,iname,grade)values(1,‘wen‘,8); insertinto rtable(idd,iname,grade)values(1,‘wen‘,9); insertinto rtable(idd,iname,grade)values(1,‘wen‘,10); insertinto rtable(idd,iname,grade)values(1,‘wen‘,11); insertinto rtable(idd,iname,grade)values(1,‘wen‘,12); insertinto rtable(idd,iname,grade)values(1,‘wen‘,13); insertinto rtable(idd,iname,grade)values(1,‘wen‘,14); insertinto rtable(idd,iname,grade)values(1,‘wen‘,15); insertinto rtable(idd,iname,grade)values(1,‘wen‘,16); insertinto rtable(idd,iname,grade)values(1,‘wen‘,17); insertinto rtable(idd,iname,grade)values(1,‘wen‘,18); insertinto rtable(idd,iname,grade)values(1,‘wen‘,19); insertinto rtable(idd,iname,grade)values(1,‘wen‘,1); insertinto rtable(idd,iname,grade)values(1,‘wen‘,2); insertinto rtable(idd,iname,grade)values(1,‘wen‘,3); insertinto rtable(idd,iname,grade)values(1,‘wen‘,4); insertinto rtable(idd,iname,grade)values(1,‘wen‘,5); insertinto rtable(idd,iname,grade)values(1,‘wen‘,6); insertinto rtable(idd,iname,grade)values(1,‘wen‘,7); insertinto rtable(idd,iname,grade)values(1,‘wen‘,8); insertinto rtable(idd,iname,grade)values(1,‘wen‘,9); insertinto rtable(idd,iname,grade)values(1,‘wen‘,10); insertinto rtable(idd,iname,grade)values(1,‘wen‘,11); insertinto rtable(idd,iname,grade)values(1,‘wen‘,12); insertinto rtable(idd,iname,grade)values(1,‘wen‘,13); insertinto rtable(idd,iname,grade)values(1,‘wen‘,14); insertinto rtable(idd,iname,grade)values(1,‘wen‘,15); insertinto rtable(idd,iname,grade)values(1,‘wen‘,16); insertinto rtable(idd,iname,grade)values(1,‘wen‘,17); insertinto rtable(idd,iname,grade)values(1,‘wen‘,18); insertinto rtable(idd,iname,grade)values(1,‘wen‘,19); 3）查询表结构及其数据量： SQL> desc rtable Name Type Nullable Default Comments ----- ------------ -------- ------- -------- IDD INTEGER Y INAME VARCHAR2(10) Y GRADE INTEGER Y SQL> select count(*) from rtable; COUNT(*) ---------- 38 4）创建转换分区表： CREATETABLE RangeTable ( idd INT, iNAME VARCHAR(10), grade INT ) PARTITIONBYRANGE(grade) ( PARTITION part1 VALUESLESSTHAN(10)TABLESPACEusers, PARTITION part2 VALUESLESSTHAN(MAXVALUE)TABLESPACEusers ); 5）将数据insert到新的分区表中： insert/*+append*/into rangetable select*from rtable; 6）查询分区表结构及数据量： SQL> select table_name,partition_name from user_tab_partitions where table_name=‘RANGETABLE‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ RANGETABLE PART1 RANGETABLE PART2 SQL> select count(*) from rangetable; COUNT(*) ---------- 38 SQL> select count(*) from rangetable partition (part1); COUNT(*) ---------- 18 SQL> select count(*) from rangetable partition (part2); COUNT(*) ---------- 20 7）表的重命名： SQL> rename rtable to rtable_old; Table renamed SQL> rename rangetable to rtable; Table renamed SQL> select table_name,partition_name from user_tab_partitions where table_name=‘RTABLE‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ RTABLE PART1 RTABLE PART2 SQL> select count(*) from rtable; COUNT(*) ---------- 38 SQL> select count(*) from rtable partition(part1); COUNT(*) ---------- 18 SQL> select count(*) from rtable partition(part2); COUNT(*) ------------------- 20 |
注意：
| 1）Oracle 11g的Interval：在11g里的Interval创建，这种方法对没有写全的分区会自动创建。比如我这里只写了1月日期，如果插入的数据有其他月份的，会自动生成对应的分区。 CREATETABLE intervaldave PARTITIONBYRANGE(time_fee) INTERVAL(NUMTOYMINTERVAL(1,‘MONTH‘)) (PARTITION part1 VALUESLESSTHAN(TO_DATE (‘01/12/2010‘,‘MM/DD/YYYY‘))) AS SELECTID, TIME_FEE FROM DAVE; SQL>select table_name,partition_name from user_tab_partitions where table_name=‘INTERVALDAVE‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALDAVE PART1 INTERVALDAVE SYS_P24 INTERVALDAVE SYS_P25 INTERVALDAVE SYS_P26 |
| 2）oracle 10g版本中一定要写全分区： createtable pdba (id,time)partitionbyrange(time) (partition p1 valueslessthan(to_date(‘2010-10-1‘,‘yyyy-mm-dd‘)), partition p2 valueslessthan(to_date(‘2010-11-1‘,‘yyyy-mm-dd‘)), partition p3 valueslessthan(to_date(‘2010-12-1‘,‘yyyy-mm-dd‘)), partition p4 valueslessthan(maxvalue)) asselectid, time_fee fromdba; SQL>select table_name,partition_name from user_tab_partitions where table_name=‘PDBA‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ PDBA P1 PDBA P2 PDBA P3 PDBA P4 sql>selectcount(*)from pdba partition(p1); count(*) ---------- 1718285 sql>selectcount(*)from pdba partition(p2); count(*) ---------- 183667 sql>selectcount(*)from pdba partition(p3); count(*) ---------- 188701 sql>selectcount(*)from pdba partition(p4); count(*) ---------- 622582 |
这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。
交换分区的操作步骤如下：
1. 创建分区表，假设有2个分区，P1，P2.
2. 创建表A存放P1规则的数据。
3. 创建表B 存放P2规则的数据。
4. 用表A 和P1 分区交换。把表A的数据放到到P1分区
5. 用表B 和p2 分区交换。把表B的数据存放到P2分区。
例：
| 1）创建分区表 CREATETABLE P_DBA ( idd INT, iNAME VARCHAR(10), grade INT ) PARTITIONBYRANGE(grade) ( PARTITION part1 VALUESLESSTHAN(10)TABLESPACEusers, PARTITION part2 VALUESLESSTHAN(MAXVALUE)TABLESPACEusers ); 2）创建普通表： SQL>createtable dba_1 asselect*from rtable t where t.grade<10; 表已创建。 SQL>createtable dba_2 asselect*from rtable t where t.grade>=10; 表已创建。 SQL> select count(*) from dba_1; COUNT(*) ---------- 18 SQL> select count(*) from dba_2; COUNT(*) ---------- 20 3）进行分区交换：
SQL> select count(*) from p_dba partition(part1); COUNT(*) ---------- 18 SQL> select count(*) from p_dba partition(part2); COUNT(*) ---------- 20 SQL> select count(*) from dba_1; COUNT(*) ---------- 0 SQL> select count(*) from dba_2; COUNT(*) ---------- 0 |
在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
这个功能只在9.2.0.4以后的版本才有，在线重定义表具有以下功能：
| （1）修改表的存储参数； |
使用在线重定义的一些限制条件：
| （1） There must be enough space to hold two copies of the table. |
在Oracle 10.2.0.4和11.1.0.7 版本下，在线重定义可能会遇到如下bug：
| Bug 7007594 - ORA-600 [12261] |
在线重定义的大致操作流程如下：
| （1）创建基础表A，如果存在，就不需要操作。 |
例：
| 1）创建基础表及索引： CREATETABLE t_dba ( idd INT, iNAME VARCHAR(10), grade INT ) SQL>insertinto t_dba selectrownum,INAME,GRADE from rtable; SQL> select count(*) from t_dba; COUNT(*) ---------- 38 SQL> create index ind_idd on t_dba(idd); Index created 2）收集t_dba的统计信息： begin dbms_stats.gather_table_stats(ownname =>‘SCOTT‘, tabname =>‘T_DBA‘, estimate_percent =>100, method_opt =>‘FOR ALL COLUMNS SIZE AUTO‘, degree=>4, cascade=>TRUE); end; 3）创建临时分区表： CREATETABLE P_T_DBA ( idd INT, iNAME VARCHAR(10), grade INT ) PARTITIONBYRANGE(grade) ( PARTITION part1 VALUESLESSTHAN(10)TABLESPACEusers, PARTITION part2 VALUESLESSTHAN(MAXVALUE)TABLESPACEusers ); 4）进行在线重定义操作：
5）验证： SQL> select partitioned from user_tables where table_name=‘T_DBA‘; PARTITION --------- YES SQL> select partitioned from user_tables where table_name=‘P_T_DBA‘; PARTITION --------- NO SQL> select T.TABLE_NAME,T.PARTITION_NAME from user_tab_partitions t where t.table_name=‘T_DBA‘; TABLE_NAME PARTITION_NAME ------------------------------------------------------------ T_DBA PART1 T_DBA PART2 SQL> select T.TABLE_NAME,T.PARTITION_NAME from user_tab_partitions t where t.table_name=‘P_T_DBA‘; no rows selected SQL> select count(*) from t_dba partition(part1); COUNT(*) ---------- 18 SQL> select count(*) from t_dba partition(part2); COUNT(*) ---------- 20 6）删除临时分区表： SQL> drop table p_t_dba; Table dropped |
（1）原分区里边界是maxvalue或者default。 这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。 或者采用split，对边界分区进行拆分。
（2）没有边界分区的。 这种情况下，直接添加分区就可以了。
以下代码给SALES表添加了一个P3分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE(‘2003-06-01‘,‘YYYY-MM-DD‘));
注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(‘COMPLETE‘);
例1：
| 1）表结构及其数据： createtable T_DBA ( IDD INTEGER, INAME VARCHAR2(10), GRADE INTEGER ) partitionbyrange(GRADE) ( partition PART1 valueslessthan(10)tablespaceUSERS), partition PART2 valueslessthan(MAXVALUE)tablespaceUSERS); SQL> select t.table_name,t.partition_name from user_tab_partitions t where t.table_name=‘T_DBA‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T_DBA PART1 T_DBA PART2 SQL> select count(*) from t_dba partition(part1); COUNT(*) ------------------------------------------------------------- 18 SQL> select count(*) from t_dba partition(part2); COUNT(*) -------------------------------------------------------------- 20 2）创建索引 createindex ix_t_dba_grade on t_dba(grade) local( partition part1 tablespaceusers, partition part2 tablespaceusers ); SQL> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name=‘IX_T_DBA_GRADE‘; OWNER INDEX_NAME TABLE_NAME PARTITIONING_TYPE ------------------------------ ------------------------------ ------------------------------ ----------------- SCOTT IX_T_DBA_GRADE T_DBA RANGE SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name=‘IX_T_DBA_GRADE‘; INDEX_OWNER INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SCOTT IX_T_DBA_GRADE PART1 SCOTT IX_T_DBA_GRADE PART2 3）删除part2分区 SQL> alter table t_dba drop partition part2; Table altered SQL> select t.table_name,t.partition_name from user_tab_partitions t where t.table_name=‘T_DBA‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T_DBA PART1 SQL> select count(*) from t_dba partition(part1); COUNT(*) ------------------------------------------------------------- 18 SQL> select count(*) from t_dba partition(part2); select count(*) from t_dba partition(part2) ORA-02149: 指定的分区不存在 注意：如果maxsize所在分区里有数据，直接删除该分区，将导致该分区内的数据丢失。正确做法如下：
4)添加分区 SQL> alter table t_dba add partition part2 values less than(20) tablespace users; Table altered SQL> select t.table_name,t.partition_name from user_tab_partitions t where t.table_name=‘T_DBA‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T_DBA PART1 T_DBA PART2 5）添加含有maxvalue的分区： SQL> alter table t_dba add partition part3 values less than(maxvalue) tablespace users; Table altered SQL> select t.table_name,t.partition_name from user_tab_partitions t where t.table_name=‘T_DBA‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T_DBA PART1 T_DBA PART2 T_DBA PART3 SQL> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name=‘IX_T_DBA_GRADE‘; OWNER INDEX_NAME TABLE_NAME PARTITIONING_TYPE ------------------------------ ------------------------------ ------------------------------ ----------------- SCOTT IX_T_DBA_GRADE T_DBA RANGE SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name=‘IX_T_DBA_GRADE‘; INDEX_OWNER INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ ------------------------------ SCOTT IX_T_DBA_GRADE PART1 SCOTT IX_T_DBA_GRADE PART2 SCOTT IX_T_DBA_GRADE PART3 注意：对于局部索引，oracle新加分区后会自动维护索引。 6）将备份数据insert回t_dba： 插入前t_dba分区表数据如下： SQL> select count(*) from t_dba partition(part1); COUNT(*) ---------- 18 SQL> select count(*) from t_dba partition(part2); COUNT(*) ---------- 0 SQL> select count(*) from t_dba partition(part3); COUNT(*) ---------- 0 执行插入操作： SQL> insert /*+append*/ into t_dba select * from t_dba_part2; insert /*+append*/ into t_dba select * from t_dba_part2 ORA-26026: 唯一的索引 SCOTT.SYS_C0023114 最初处于无法使用的状态 注意：新添加分区后，oracle会自动维护局部索引，但是全局索引将失效，需要重新rebuild。 SQL> alter index SYS_C0023114 rebuild; Index altered SQL> insert /*+append*/ into t_dba select * from t_dba_part2; 20 rows inserted SQL> commit; Commit complete 插入后t_dba分区表数据如下： SQL> select t.table_name,t.partition_name from user_tab_partitions t where t.table_name=‘T_DBA‘; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T_DBA PART1 T_DBA PART2 T_DBA PART3 SQL> select count(*) from t_dba partition(part1); COUNT(*) ---------- 18 SQL> select count(*) from t_dba partition(part2); COUNT(*) ---------- 20 SQL> select count(*) from t_dba partition(part3); COUNT(*) ---------- 0 |
例2、 split 分区拆分
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。此种方式可以自动进行数据分配，局部索引自动维护，全局索引失效需要rebuild。
1）ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE(‘2003-02-01‘,‘YYYY-MM-DD‘)) INTO (PARTITION P21,PARTITION P22);
2）alter table custaddr split partition p_other values(‘552‘) into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service);
例：