Oracle之分区技术
一、分区理论知识
Oracle提供了分区技术以支持VLDB（Very Large DataBase)。分区表通过对分区的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。
Oracle的分区表可以包括多个分区，每个分区都是一个独立的段(SEGMENT)，可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
1.1、When to Partition a Table(什么时候需要分区表，官网的2个建议如下：)：
(1)、Tables greater than 2GB should always be considered for partitioning.
(2)、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.
1.2、在Oracle 10g中最多支持：1024k-1个分区：
Tables can be partitioned into up to 1024k-1 separate partitions.
1.3、分区特点：
(1)、可以单独对分区及分区索引进行操作。在分区对象中，可以只对单独分区进行数据加载、数据备份、数据恢复以及索引重建等操作，而不必对整个对象进行操作。
(2)、提高查询效率。在某些时候分区让查询可以更快，因为Oracle有一个分区裁剪功能，只对需要处理的分区进行扫描，这样扫描的数据块会大大地减少，使查询效率提高。
(3)、分区更利于数据维护。可以只对单独分区进行备份、恢复，这样就可以大大缩短数据备份、恢复的时间。
(4)、分区有利于数据库数据的过期化处理。
1.4、分区的种类
在Oracle 10g中，按照官方文档的描述，分区可以分为下面三种：
(1)、范围分区(Range partiton)
(2)、哈希分区(Hash partition)
(3)、列表分区(List partition)
1.5、Range分区：
Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放在列值所在的range分区中。
如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。
在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。
创建语句如：
1 SQL> create table jack_range (id number,time date) partition by range(time) 2 2 ( 3 3 partition p1 values less than (to_date('2012-10-1','yyyy-mm-dd')), 4 4 partition p2 values less than (to_date('2012-11-1','yyyy-mm-dd')), 5 5 partition p3 values less than (to_date('2012-12-1','yyyy-mm-dd')), 6 6 partition p4 values less than (maxvalue) 7 7 ); 8 9 Table created.
1.6、Hash分区：
对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。
创建语句：
1 SQL> create table jack_hash 2 2 (id number primary key,name varchar2(10),type varchar2(10)) 3 3 partition by hash(id) 4 4 ( 5 5 partition hash_01 tablespace jack_hash01, --在这里，我们指定了每个分区的表空间 6 6 partition hash_02 tablespace jack_hash02, 7 7 partition hash_03 tablespace jack_hash03 8 8 ); 9 10 Table created.
1.7、List分区：
List分区也需要指定列的值，其分区值必须明确指定，该分区列值只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。
创建语句：
1 SQL> create table jack_list 2 2 (id number not null,name varchar2(10),type varchar2(10)) 3 3 partition by list(type) 4 4 ( 5 5 partition list_01 values ('oracle','mysql'), 6 6 partition list_02 values ('MongoDB','SimpleDB'), 7 7 partition list_other values (default) 8 8 ); 9 10 Table created.
二、普通表转分区表方法
将普通表转成分区表有4中方法：
1.Export/Import method;
2.Insert with a subquery method;
3.Partition exchange method;
4.DBMS_REDEFINITION.
在这里对于1和2的方法我们将不做讨论。
2.1、交换分区：Partition exchange method
这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。
交换分区的操作步骤如下：
1.创建分区表，假设有2个分区，P1，P2。
2.创建表A存放P1分区规则的数据。
3.创建表B存放P2分区规则的数据。
4.用表A和P1分区交换，把表A的数据放到P1分区。
5.用表B和P2分区交卷，把表B的数据放到P2分区。
三、分区表的其他操作
3.1、添加新的分区
(1)、原分区里边界是maxvalue或者default。这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。或者采用split，对边界分区进行拆分。
(2)、没有边界分区的。这种情况下，直接添加分区就可以了。
以Range分区添加新分区示例(此处使用的用户是sys)：
(1)、创建分区表和索引：
1 SQL> create table range_jack 2 2 (id int not null,name varchar2(10),type varchar2(10)) 3 3 partition by range(id) 4 4 ( 5 5 partition range_10 values less than (10) tablespace range01, 6 6 partition range_max values less than (maxvalue) tablespace range02 7 7 ); 8 9 Table created. 10 11 SQL> create index ind_range on range_jack(id) 12 2 local 13 3 ( 14 4 partition ind_10 tablespace range01, 15 5 partition ind_max tablespace range02 16 6 ); 17 18 Index created.
(2)、插入几条测试数据：
1 SQL> insert into range_jack values (1,'aa','aaa'); 2 3 1 row created. 4 5 SQL> insert into range_jack values (20,'gg','ggg'); 6 7 1 row created. 8 9 SQL> commit; 10 11 Commit complete. 12 13 SQL> select * from range_jack; 14 15 ID NAME TYPE 16 ---------- ---------- ---------- 17 1 aa aaa 18 20 gg ggg 19 20 SQL> select * from range_jack partition(range_max); 21 22 ID NAME TYPE 23 ---------- ---------- ---------- 24 20 gg ggg
(3)删除maxvalue分区：
1 SQL> alter table range_jack drop partition range_max; 2 3 Table altered. 4 5 SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGE_JACK'; 6 7 TABLE_NAME PARTITION_NAME 8 ------------------------------ ------------------------------ 9 RANGE_JACK RANGE_10
(4)、添加新分区：
1 SQL> alter table range_jack add partition range_20 values less than (20) tablespace range02; 2 3 Table altered. 4 5 SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGE_JACK'; 6 7 TABLE_NAME PARTITION_NAME 8 ------------------------------ ------------------------------ 9 RANGE_JACK RANGE_10 10 RANGE_JACK RANGE_20
(5)、添加maxvalue分区：
1 SQL> alter table range_jack add partition range_max values less than (maxvalue) tablespace range03; 2 3 Table altered. 4 5 SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGE_JACK'; 6 7 TABLE_NAME PARTITION_NAME 8 ------------------------------ ------------------------------ 9 RANGE_JACK RANGE_10 10 RANGE_JACK RANGE_20 11 RANGE_JACK RANGE_MAX
(6)、对于局部索引，Oracle会自动增加一个局部分区索引。验证一下：
1 SQL> show user; 2 USER is "SYS" 3 SQL> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='IND_RANGE'; 4 5 OWNER INDEX_NAME TABLE_NAME PARTITION 6 ---------- ------------ ------------ --------- 7 SYS IND_RANGE RANGE_JACK RANGE 8 9 SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IND_RANGE'; 10 11 INDEX_OWNER INDEX_NAME PARTITION_NAME 12 ------------------- ------------ ------------------------------ 13 SYS IND_RANGE RANGE_MAX --分区索引自动创建 14 SYS IND_RANGE RANGE_20 15 SYS IND_RANGE IND_10
3.2、split分区拆分
在3.1中，我们说明了可以使用split的方式来添加分区。这里我们用split方法继续上面的实验。
1 SQL> alter table range_jack split partition range_max at(25) into (partition range_25 tablespace yft,partition range_max tablespace yft); 2 3 Table altered. 4 --------注意这里红色的地方，如果是Range类型的，使用at；List使用Values.注意：分区表会自动维护局部分区索引。全局索引会失效，需要进行rebuild。
5 SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGE_JACK'; 6 7 TABLE_NAME PARTITION_NAME 8 ------------------------------ ------------------------------ 9 RANGE_JACK RANGE_10 10 RANGE_JACK RANGE_20 11 RANGE_JACK RANGE_25 12 RANGE_JACK RANGE_MAX 13 14 SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IND_RANGE'; 15 16 INDEX_OWNE INDEX_NAME PARTITION_NAME 17 ---------- ---------- ------------------------------ 18 JACK IND_RANGE IND_10 19 JACK IND_RANGE RANGE_20 20 JACK IND_RANGE RANGE_25 21 JACK IND_RANGE RANGE_MAX
3.3、合并分区Merge
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。
1 SQL> alter table range_jack merge partitions range_25,range_max into partition range_max; 2 3 Table altered. 4 5 SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGE_JACK'; 6 7 TABLE_NAME PARTITION_NAME 8 ------------------------------ ------------------------------ 9 RANGE_JACK RANGE_10 10 RANGE_JACK RANGE_20 11 RANGE_JACK RANGE_MAX 12 13 SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IND_RANGE'; 14 15 INDEX_OWNE INDEX_NAME PARTITION_NAME 16 ---------- ---------- ------------------------------ 17 JACK IND_RANGE IND_10 18 JACK IND_RANGE RANGE_20 19 JACK IND_RANGE RANGE_MAX
3.4、移动分区
1 SQL> alter table range_jack move partition range_max tablespace users; 2 3 Table altered. 4 5 SQL> alter table range_jack move partition range_max tablespace yft; 6 7 Table altered. 8
---注意：分区移动会自动维护局部分区索引，oracle不会自动维护全局索引，所以需要我们重新rebuild分区索引，具有需要rebuild哪些索引，
可以通过dba_part_indexes,dba_ind_partitions去判断。 9 SQL> select index_name,status from user_indexes where table_name='RANGE_JACK'; 10 11 INDEX_NAME STATUS 12 ---------- -------- 13 IND_RANGE N/A
3.5、Truncate分区
1 SQL> select * from range_jack partition(range_10); 2 3 ID NAME TYPE 4 ---------- ---------- ---------- 5 1 aa aaa 6 7 SQL> alter table range_jack truncate partition(range_10); 8 9 Table truncated. 10 11 SQL> select * from range_jack partition(range_10); 12 13 no rows selected说明：Truncate相对delete操作很快，数据仓库中的大量数据的批量数据加载可能会有用到；截断分区同样会自动维护局部分区索引，同时会使全局索引unusable，需要重建。
3.6、Drop分区
1 SQL> alter table range_jack drop partition range_10; 2 3 Table altered. 4 5 SQL> select table_name,partition_name from user_tab_partitions where table_name='RANGE_JACK'; 6 7 TABLE_NAME PARTITION_NAME 8 ------------------------------ ------------------------------ 9 RANGE_JACK RANGE_20 10 RANGE_JACK RANGE_MAX注意：同样会自动维护局部分区索引，同时会使全局索引unusable，需要重建。
四、分区表的索引
分区索引分为本地索引(local index)和全局索引(global index)，其中局部索引比全局索引容易管理，至于速度的比较请看： 《关于分区索引与全局索引性能比较的示例》
与索引有关的表：
dba_part_indexes：分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)；
dba_ind_partitions：每个分区索引的分区级统计信息；
dba_indexes/dba_part_indexes：可以得到每个表上有哪些非分区索引。
注意：Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。
分区索引分2类：有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引：
(1)、有前缀的分区索引指包含了分区键，并且将其作为引导列的索引。
1 SQL> create index global_ind on range_jack(id) global ----引导列 2 2 partition by range(id) ---分区键
3 3 ( 4 4 partition g1 values less than (20), 5 5 partition gm values less than (maxvalue) 6 6 ); 7 -----说明：这里的ID就是分区键，并且分区键id也是索引的引导列 8 Index created. 9
(2)、无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。 10 SQL> create index local_ind on range_jack(name) 11 2 local 12 3 ( 13 4 partition l_20 tablespace yft, 14 5 partition l_max tablespace yft 15 6 ); 16 17 Index created. 18 ----说明：这个分区按照name来的。但是索引的引导列是id，所以它就是非前缀分区索引。
全局分区索引不支持非前缀的分区索引，如果创建，报错如下： 19 SQL> create index global_ind on range_jack(id) global 20 2 partition by range (name) 21 3 ( 22 4 partition p1 values less than ('aa'), 23 5 partition p2 values less than (maxvalue) 24 6 ); 25 partition by range (name) 26 * 27 ERROR at line 2: 28 ORA-14038: GLOBAL partitioned index must be prefixed
4.1、Local index(本地索引)
对于Local索引，当表的分区发生变化时，索引的维护由Oracle自动进行。
注意事项：
(1)、局部索引一定是分区索引，分区键等同于表的分区键。
(2)、前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
(3)、局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。
(4)、局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。
(5)、位图索引必须是局部分区索引。
(6)、局部索引多应用于数据仓库环境中。
(7)、B树索引和位图索引都可以分区。
示例：
1 SQL> create index locals_index on range_jack(id) 2 2 local 3 3 ( 4 4 partition l_20 tablespace yft, 5 5 partition l_max tablespace yft 6 6 ); 7 8 Index created. 9 10 SQL> create index local_ind on range_jack(name) local; 11 12 Index created. 13 ----验证2个索引的类型-------------
14 SQL> select index_name,table_name,partitioning_type,locality,alignment from user_part_indexes where table_name='RANGE_JACK'; 15 16 INDEX_NAME TABLE_NAME PARTITION LOCALI ALIGNMENT 17 --------------- ---------- --------- ------ ------------ 18 LOCALS_INDEX RANGE_JACK RANGE LOCAL PREFIXED 19 LOCAL_IND RANGE_JACK RANGE LOCAL NON_PREFIXED
4.2、Global索引
对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。全局分区索引只能是B树索引，在10gR2之前。
另外Oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果对分区进行维护操作时不加上update global indexes的话，通常会导致全局索引的INVALDED，必须在执行完操作后REBUILD。
注意事项：
(1)、全局索引可以分区，也可以是不分区索引，全局索引必须是有前缀索引，即全局索引的索引必须是以索引分区键作为其前几列。
(2)、全局索引可以依附于分区表；也可以依附于非分区表。
(3)、全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebuild若干个分区甚至是整个索引。
(4)、全部索引多应用于oltp系统中。
(5)、全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。
(6)、oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用行。
(7)、表用a列做分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑b做全局分区索引。
----示例1：全局索引，全局索引对所有分区类型都支持：
1 SQL> create index global_jack_ind on range_jack(id) global; 2 3 Index created.
----示例2：全局分区索引，只支持Range分区和Hash分区：
----创建2个测试分区表---------------------------
1 SQL> create table jack_range(id number,time date) partition by range(time) 2 2 ( 3 3 partition p1 values less than (to_date('2013-01-1','yyyy-mm-dd')), 4 4 partition p2 values less than (to_date('2013-02-1','yyyy-mm-dd')), 5 5 partition p3 values less than (to_date('2013-03-1','yyyy-mm-dd')), 6 6 partition p4 values less than (maxvalue) 7 7 ); 8 9 Table created. 10 11 SQL> create table jack_hash(id number primary key,item_id number(8) not null) 12 2 partition by hash(id) 13 3 ( 14 4 partition part_01, 15 5 partition part_02, 16 6 partition part_03 17 7 ); 18 19 Table created. 20 ----创建全局分区索引-----------------
21 SQL> create index jack_global_ind on jack_range(id) global 22 2 partition by range(id) 23 3 ( 24 4 partition g1 values less than(200), 25 5 partition g2 values less than(maxvalue) 26 6 ); 27 28 Index created. 29 ----------------这个是有前缀的分区索引。
30 SQL> create index jack_global_time on jack_range(id) global 31 2 partition by range(time) 32 3 ( 33 4 partition g1 values less than(to_date('2013-02-1','YYYY-MM-DD')), 34 5 partition g2 values less than(maxvalue) 35 6 ); 36 partition by range(time) 37 * 38 ERROR at line 2: 39 ORA-14038: GLOBAL partitioned index must be prefixed 40 41 42 SQL> create index jack_global_time on jack_range(time) global 43 2 partition by range(time) 44 3 ( 45 4 partition g1 values less than(to_date('2013-02-1','YYYY-MM-DD')), 46 5 partition g2 values less than(maxvalue) 47 6 ); 48 49 Index created. 50 -----------------有前缀的分区索引。
51 SQL> select index_name,table_name,partitioning_type,locality,alignment from user_part_indexes where table_name='JACK_RANGE'; 52 53 INDEX_NAME TABLE_NAME PARTITION LOCALI ALIGNMENT 54 ------------------------------ ---------- --------- ------ ------------ 55 JACK_GLOBAL_IND JACK_RANGE RANGE GLOBAL PREFIXED 56 JACK_GLOBAL_TIME JACK_RANGE RANGE GLOBAL PREFIXED 57 58 SQL> create index hash_global on jack_range(id,time) global 59 2 partition by hash(id) 60 3 ( 61 4 partition p1, 62 5 partition p2, 63 6 partition p3, 64 7 partition p4 65 8 ); 66 67 Index created.
4.3、索引重建
(1)、分区索引
对于分区索引，不能整体进行重建，只能对单个分区进行重建。语法如下：
Alter index index_name rebuild partition index_partition_name [online nologging];
说明：
online：表示重建的时候不会锁表。
nologging：表示建立索引的时候不生成日志，加快速度。
如果要重建分区索引，只能drop表原索引，在重新创建：
SQL>create index loc_xxxx_col on xxx(col) local tablespace yft;
这个操作要求较大的临时表空间和排序区。
示例：
1 SQL> select index_name,partition_name from user_ind_partitions where index_name='LOCAL_IND'; 2 3 INDEX_NAME PARTITION_NAME 4 --------------------- ------------------------------ 5 LOCAL_IND RANGE_20 6 LOCAL_IND RANGE_MAX 7 8 SQL> alter index local_ind rebuild partition range_20 online nologging; 9 10 Index altered. 11 12 SQL> alter index local_ind rebuild partition range_max online nologging; 13 14 Index altered.
(2)、全局索引
Oracle会自动维护分区索引，对于全局索引，如果在对分区表操作时，没有指定update index，则会导致全局索引失效，需要重建。
1 SQL> select owner,index_name,table_name,status from dba_indexes where index_name='GLOBAL_IND'; 2 3 OWNER INDEX_NAME TABLE_NAME STATUS 4 ---------- -------------------- ---------- -------- 5 JACK GLOBAL_IND RANGE_JACK VALID 6 7 SQL> alter table range_jack drop partition range_20; 8 9 Table altered. 10 11 SQL> select owner,index_name,table_name,status from dba_indexes where index_name='GLOBAL_IND'; 12 13 OWNER INDEX_NAME TABLE_NAME STATUS 14 ---------- ----------------------- ---------- -------- 15 JACK GLOBAL_IND RANGE_JACK VALID
重建全局索引命令如下：
Alter index idx_name rebuild [online nologging]
五、参考资料：
http://blog.csdn.net/tianlesoftware/article/details/4717318
http://blog.csdn.net/kaertiger/article/details/6927568
http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CFAGCHCD
