首页 > 代码库 > Oracle分区
Oracle分区
目录
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分区理论知识
数据库分区是每种数据库都需具备的关键功能之一，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）； |
二、分区表的实现方式
1、范围分区（range partition table）
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 ); |
2、列表分区（list partitioning）
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) ); |
3、散列分区（hash partitioning）
对于一个表执行散列分区时，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 ); |
4、间隔分区（interval partitioning）
间隔分区是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‘)) ) |
5、引用分区（reference partitioning）
引用分区从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中，还不支持间隔分区和引用分区连用，即不能对父表为间隔分区的表创建子表的引用分区类型。
6、组合分区（composite partitioning）
组合分区是范围分区、散列分区、列表分区之间的组合。组合分区所采用的方法（也就是可以混合和匹配的分配机制类型）在不同的版本中有所不同。下表列出了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) ); |
7、行移动（row movement）
在前面所述的各种分区机制中，如果对于确定分区的列有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
1、导入、导出（Export/import method）方式转换分区表
不做说明。
2、插入（Insert with a subquery method）方式转换分区表
这种方法就是使用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 |
3、交换分区（Partition exchange method）方式转换分区数据
这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。
交换分区的操作步骤如下：
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 |
4、在线重定义（DBMS_REDEFINITION）转换分区数据
在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行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、分区表添加新分区、分以下2种情况：
（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);
例：
| 1）查询分区信息： 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 2）splist方式拆分分区：如果是Range类型的用at，List使用Values。 SQL> alter table t_dba split partition part3 at(30) into (partition part4 tablespace users,partition part3 tablespace users); Table altered 3）查询拆分后的分区信息 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 T_DBA PART4 |
2、合并分区merge
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。
ALTERTABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
例：
| SQL> alter table t_dba merge partitions part1,part2 into 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 PART2 T_DBA PART3 T_DBA PART4 T_DBA PART5 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 PART2 SCOTT IX_T_DBA_GRADE PART3 SCOTT IX_T_DBA_GRADE PART4 SCOTT IX_T_DBA_GRADE PART5 SQL> select t.index_name,t.table_name,t.status from user_indexes t where t.index_name=‘SYS_C0023114‘; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ -------- SYS_C0023114 T_DBA UNUSABLE SQL> alter index SYS_C0023114 rebuild; Index altered SQL> select t.index_name,t.table_name,t.status from user_indexes t where t.index_name=‘SYS_C0023114‘; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ -------- SYS_C0023114 T_DBA VALID |
3、移动分区
分区移动会自动维护局部分区索引，oracle不会自动维护全局索引，所以需要我们重新rebuild分区索引，具体需要rebuild哪些索引，可以通过dba_part_indexes,dba_ind_partitions去判断。
| SQL> alter table custaddr move partition P_OTHER tablespace system; |
4、Truncate分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
| SQL> select * from custaddr partition(T_LIST556); |
5、Drop分区
drop分区会自动维护局部分区索引，同时会使全局索引unusable,需要重建。
以下代码删除了P3表分区：
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
| SQL> alter table custaddr drop partition T_LIST551; |
6、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
ALTER TABLE SALES COALESCA PARTITION;
7、 重命名表分区
以下代码将P21更改为P2：
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
五、分区索引
类似于分区表，针对索引也可以进行分区，即可以默认创建索引只占用一个分区，我们可以在创建索引的过程中指定索引的分区规则，将索引分割成多个段空间进行存储、管理。Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。
针对于分区表的索引分为局部索引（locally index）和全局索引（global partitioned index）两种。以下就这两种索引分别介绍。
局部索引随表对索引完成相应分区，每个表分区都有一个索引分区，而且该索引分区只索引该表分区。即一个给定的索引分区中的所有条目都指向一个表分区，表分区中的索引行都表示在一个索引分区中。
全局索引按区间或散列对索引分区，在此，索引按区间分区，一个索引分区可能指向任何（或所有）表分区。对于全局索引分区，索引分区可能不等同于表分区数。
由于全局索引只能按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。局部索引会使用与底层表相同的分区机制分区。
据我经验局部索引更适用于OLAP系统，而全局索引更适合于OLTP系统。局部索引有一些特质决定了对于大多数的OLAP系统实现来说是最佳的选择。局部索引支持一种更可用的状态，另一方面，全局索引可以指向多个表分区，因此可能成为一个故障点，对于某些查询来说，全局索引故障将导致所有分区都不可访问。局部索引易于管理，如果DBA决定移动一个表分区，只有相关的局部索引需要重建或维护，如果是全局索引，那么所有分区索引都得实时重建或维护。局部索引还有利于分区时间点的恢复，由于某些原因，一个分区需要恢复到比其余表更早的某个时间点，所有的局部分区索引都可以恢复到那个同样的时间点。
滑动窗口（sliding window）：删除表中旧分区，并为新加载的数据创建新分区。滑动窗口会导致全局索引失效，但是对于局部索引，却有相当的可用性。
1、局部索引（loaclly index）
Oracle局部索引可以分为以下两种情况：
（1）局部前缀索引（locla prefixed index）：在这些索引中，分区键在索引定义的前几列上（分区键作为引导列）。
（2）局部非前缀索引（local nonprefixed index）：这些所以不已分区键做为其列列表的前几列。索引可能包含分区键列，也可能不包含。
这两类所以都支持分区消除，并且都支持唯一性（只要非前缀索引包含唯一键）。使用局部前缀分区索引的查询总允许分区消除，而使用局部非前缀索引的查询可能不允许。因此，某些人看来局部分前缀索引更慢，这只是因为他们不能保证分区消除。
| createtable partitioned_table ( a int, b int, datavarchar2(30) ) Partition by range(a) ( partition part1 valueslessthen(2)tablespace tbs1; partition part2 valueslessthen(3)tablespace tbs2; ) SQL>create index local_prefixed on partitioned_table (a,b)local; --局部前缀索引 SQL>create index local_nonprefixed on partitioned_table (b)local; --局部非前缀索引 |
注意事项：
| （1） 局部索引一定是分区索引，分区键等同于表的分区键。 |
2、全局索引（global partitioned index）
与局部索引不同，全局索引只有“前缀全局索引（prefixed global index）”一类，全局索引可以用来保证主键的唯一性，而局部分区索引只能保证分区内主键的唯一性。大多数分区操作都会使全局索引无效，除非重建索引，否则无法使用，这回严重影响可用性。
对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。全局分区索引只能是B树索引，到10gR2为止，oracle只支持有前缀的全局索引。
另外oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果对分区进行维护操作时不加上update global indexes的话，通常会导致全局索引的INVALDED，必须在执行完操作后REBUILD。
| 注意：Oracle只支持2中类型的全局分区索引： 官网的说明如下： |
| 示例：全局分区索引，只支持Range分区和Hash分区： 第 2 行出现错误: SQL> create index i_time_global on PDBA(time) global SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name=‘PDBA‘; index_name table_name partition locali alignment i_id_global pdba range global prefixed hash索引分区：散列索引分区不能实现区间扫描，最适合于相等的比较。 SQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL --只要索引的引导列包含分区键，就是有前缀的分区索引。 |
例：
| create table partitioned ( timestamp date, id int ) partition by range(timestamp) ( partition part1 values less than (to_date(‘01-01-2016‘,‘dd-mm-yyyy‘)), partition part2 values less than (to_date(‘01-01-2017‘,‘dd-mm-yyyy‘)) ) create index partitioned_ind on partitioned(id) global partition by range(id) ( partition part1 values less than(1000), partition part2 values less than(maxvalue) ) --全局索引的最高分区必须有一个maxvalue分区上界，可以确定底层表中的所有行都能包含在索引中。 alter table partitioned add constraint partitioned_pk primary key(id); SQL> drop index partitioned_ind; ORA-02429: 无法删除用于强制唯一/主键的索引 SQL> create index partitioned_ind on partitioned(id) global 2 partition by range(timestamp) 3 ( 4 partition part1 values less than(1000), 5 partition part2 values less than(maxvalue) 6 ); ORA-14038: GLOBAL 分区索引必须加上前缀 --全局索引不支持“非前缀分区索引” |
从oracle9i开始，可以再分区操作期间使用update global indexes子句来动态维护全局索引。这意味着，在删除一个分区、分解一个分区以及在分区上执行的任何必要操作时，oracle会对全局索引进行必要的更新，保证它是罪行的。由于大多数操作都可导致全局索引失效，所以update global indexes对于提供数据连续访问的系统来说非常必要。对于使用update global indexes子句的操作，会占用大量的资源，但是会保证数据的100%的访问可用性，而普通的重建索引虽然占用资源少，执行速度快但是其要有一定的停机时间。
update global indexes方法生成的redo量要明显比重建索引多，update global indexes生成的redo是不可避免的，不能通过nologging去掉，因为全局索引的维护不是其结构的完全重建，而应该算是一种增量式的维护。另外，我们维护这活动的索引结构，必须为之生成undo，万一分区操作失败，必须准备好将索引回滚为原来的样子。
| alter table partition_name exchange partition part_name with table table_name including indexes without validation update global indexes; alter table partition_name drop partition part_name; |
总结：
| （1）全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。 |
3、索引rebuild的问题
（1）分区索引
对于分区索引，不能整体进行重建，只能对单个分区进行重建。语法如下：
alter index idx_name rebuild partition index_partition_name [online nologging]
说明：
online:表示重建的时候不会锁表。
nologging：表示建立索引的时候不生成日志，加快速度。
（2）全局索引
Oracle 会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update global indexes，则会导致全局索引失效，需要重建。重建全局索引命令如下：
Alter index idx_name rebuild [online nologging]
示例：
SQL> Alter index ix_pdba_global rebuild online nologging;
索引已更改。
| SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME=‘IX_PDBA_GLOBAL‘; |
| 补充一点，分区表存储空间的问题： TABLE_NAME PARTITION_NAME TABLESPACE_NAME 通过user_tab_partitions 表可以查看到每个分区对应的tablesapce_name.但是，如果通过all_tables 表，却查不到分区表对应表空间的信息。 分区表： OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME 普通表： OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME |
Oracle分区