Oracle分区表常用命令-mysql教程

一、Oracle分区简介 ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的

一、Oracle分区简介

ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。

二、Oracle分区优缺点

优点：
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

缺点：

分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

三、Oracle分区方法

范围分区：

范围分区就是对数据表中的某个&＃20540;的范围进行分区，根据某个&＃20540;的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。

Hash分区（散列分区）：

散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

List分区（列表分区）：

当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。

范围-散列分区（复合分区）：

有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法（注意：先一定要进行范围分区）

范围-列表分区（复合分区）：
范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。（注意：先一定要进行范围分区）

四、Oracle表分区表操作

--Partitioning 是否为true
select * from v$option s order by s.PARAMETER desc

--创建表空间
CREATE TABLESPACE "PARTION_03"
LOGGING
DATAFILE 'D:ORACLEORADATAJZHUAPARTION_03.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

--删除表空间
drop tablespace partion_01


--范围 分区技术
create table Partition_Test
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PID)
(
partition part_01 values less than(50000) tablespace dinya_space01,
partition part_02 values less than(100000) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
)

create table Partition_TTest
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PDATA)
(
partition part_t01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,
partition part_t02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,
partition part_t03 values less than(maxvalue) tablespace dinya_space03
)

insert into Partition_Test(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_Test partition(part_01) t where t.pid = '1961'

--hash 分区技术
create table Partition_HashTest
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by hash(PID)
(
partition part_h01 tablespace dinya_space01,
partition part_h02 tablespace dinya_space02,
partition part_h03 tablespace dinya_space03
)

insert into Partition_HashTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_HashTest partition(part_h03) t where t.pid = '1961'


--复合分区技术
create table Partition_FHTest
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PDATA) subpartition by hash(PID) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_fh01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,
partition part_fh02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,
partition part_fh03 values less than(maxvalue) tablespace dinya_space03
)

insert into Partition_FHTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_FHTest partition(part_fh02) t where t.pid = '1961'

select * from Partition_FHTest partition(part_fh03) t

--速度比较
select * from st_handle h where h.rectime > to_date('2008-01-01','yyyy-mm-dd');

select * from Partition_FHTest partition(part_fh03) t where t.pdata > to_date('2008-01-01','yyyy-mm-dd');


--分区表操作

--增加一个分区
alter table Partition_Test add partition part_05 values less than (10020) tablespace dinya_space03

--查询分区数据
select * from Partition_FHTest partition(part_fh02) t

--修改分区里的数据
update Partition_FHTest partition(part_fh02) t set t.PITEM = 'JZHUA' where t.pid = '1961'

--删除分区里的数据
delete from Partition_FHTest partition(part_fh02) t where t.pid = '1961'

--合并分区
create table Partition_HB
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PID)
(
partition part_01 values less than(50000) tablespace dinya_space01,
partition part_02 values less than(100000) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
)

insert into Partition_HB(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h

select * from Partition_HB partition(part_03) t where t.pid = '100001'

alter table Partition_HB merge partitions part_01,part_02 into partition part_02;

--拆分分区
-- spilt partition 分区名 at(这里是一个临界区，比如：50000就是说小于50000的放在part_01，而大于50000的放在part_02中)
alter table Partition_HB split Partition part_02 at (50000) into (Partition part_01 tablespace dinya_space01, Partition part_02 tablespace dinya_space02);

--更改分区名
alter table Partition_HB rename Partition part_01_test to part_02;
五、Oracle索引分区表操作
分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。全局索引建立时 global 子句允许指定索引的范围&＃20540;，这个范围&＃20540;为索引字段的范围&＃20540;。其实理论上有3中分区索引。

?Global索引（全局索引）：
对于 global 索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的 Invalid，必须在执行完操作后 Rebuild。Oracle9i 提供了 Update Global Indexes 语句，可以在进行分区维护的同时重建全局索引。
1：索引信息的存放位置与父表的Partition（分区）信息完全不相干。甚至父表是不是分区表都无所谓的。

create index dinya_idx_t on dinya_test(item_id) global partition by range(item_id) (
partition idx_1 values less than (1000) tablespace dinya_space01,
partition idx_2 values less than (10000) tablespace dinya_space02,
partition idx_3 values less than (maxvalue) tablespace dinya_space03
);
2：但是在这种情况下，如果父表是分区表，要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确
ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes

Local索引（局部索引）：

对于 local 索引，每一个表分区对应一个索引分区（就是说一个分区表一个字段只可以建一个局部索引），当表的分区发生变化时，索引的维护由 Oracle 自动进行；
1：索引信息的存放位置依赖于父表的Partition（分区）信息，换句话说创建这样的索引必须保证父表是Partition（分区），索引信息存放在父表的分区所在的表空间。
2：但是仅可以创建在父表为HashTable或者composite分区表的。
3：仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致。

create index dinya_idx_t on dinya_test(item_id) local (
partition idx_1 tablespace dinya_space01,
partition idx_2 tablespace dinya_space02,
partition idx_3 tablespace dinya_space03
);

不指定索引分区名直接对整个表建立索引
create index dinya_idx_t on dinya_test(item_id);
-------------------------------------------------------分区2-----------------------------------------------------------------------------

　最近在做一个客户关系管理系统，项目做的到不是非常成功，可还是学到了不少的知识，由于数据量很大，没有专门的oracle数据库人员支持，对数据库优化管理等也只有我这个约懂一点的人上了。在对数据库优化上有一点点心得写出来希望能同大家一起学习和交流。

　　数据库大表的优化：采用蔟表（clustered tables）及蔟索引（Clustered Index）

　　蔟表和蔟索引是oracle所提供的一种技术，其基本思想是将几张具有相同数据项、并且经常性一起使用的表通过共享数据块（data block）的模式存放在一起。各表间的共同字段作为蔟键&＃20540;（cluster key），数据库在访问数据时，首先找到蔟键&＃20540;，以此同时获得若干张表的相关数据。蔟表所能带来的好处是可以减少I/O和减少存储空间，其中我更看重前者。采用表分区（partition）

　　表分区技术是在超大型数据库(VLDB)中将大表及其索引通过分区（patition）的形式分割为若干较小、可管理的小块，并且每一分区可进一步划分为更小的子分区（sub partition）。而这种分区对于应用来说是透明的。通过对表进行分区，可以获得以下的好处：

　　1）减少数据损坏的可能性。

　　2）各分区可以独立备份和恢复，增强了数据库的可管理性。

　　3）可以控制分区在硬盘上的分布，以均衡IO，改善了数据库的性能。

　　蔟表与表分区技术的侧重点各有不同，前者侧重于改进关联表间查询的效率，而表分区侧重于大表的可管理性及局部查询的性能。而这两项对于我的系统来说都是极为重要。由于本人技术限制，目前尚不确定两者是否可以同时实现，有那位在这方面有经验的给点指导将不胜感激。

　　在两者无法同时实现的情况下，应依照需实现的功能有所取舍。综合两种模式的优缺点，我认为采用表分区技术较为适用于我们的应用。

　　Oracle的表分区有以下几种类型：

　　1）范围分区：将表按某一字段或若干个字段的取&＃20540;范围分区。

　　2）hash分区：将表按某一字段的&＃20540;均匀地分布到若干个指定的分区。

　　3）复合分区：结合了前面两种分区类型的优点，首先通过&＃20540;范围将表进行分区，然后以hash模式将数据进一步均匀分配至物理存储位置。

　　综合考虑各项因素，以第三种类型最为优越。（本人实在技术有限仅采用了第1种范围分区，因为比较简单，便于管理）

　　优化的具体步骤：

　　1．确定需要优化分区的表：

　　经过对系统数据库表结构和字段，应用程序的分析，现在确定那些大表需要进行分区：

　　如帐户交易明细表acct_detail.

　　2．确定表分区的方法和分区键：

　　分区类型：采用范围分区。

　　分 区 键：

　　按trans_date（交易时间）字段进行范围分区.

　　3．确定分区键的分区范围，及打算分多少分区：

　　如：帐户交易明细表acct_detail.

　　根据字段（trans_date）分成一下分区：

　　1）．分区1：09/01/2003

　　2）．分区2：10/01/2003

　　3）．分区3：11/01/2003

　　4）．分区4：12/01/2003

　　5）．分区5：01/01/2004

　　6）．分区6：02/01/2004

　　该表明显需要在以后增加分区。

　　4．建立分区表空间和分区索引空间

　　1)．建立表的各个分区的表空间：

　　1．分区1：crm_detail_200309

　　CREATE TABLESPACE crm_detail_200309 DATAFILE

　　‘/u1/oradata/orcl/crm_detail_20030901.dbf’

　　SIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM size 16M;

　　其它月份以后同以上（我在此采用oracle的表空间本地管理的方法）。

　　2）. 建立分区索引表空间

　　1．分区1：index_detail_200309

　　CREATE TABLESPACE index_detail_200309 DATAFILE

　　‘/u3/oradata/orcl/index_detail_20030901.dbf’

　　SIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM size 16M;

　　5．建立基于分区的表：

　　create table table name

　　(

　　........

　　enable row movement --此语句是能修改行分区键&＃20540;，也就是如不添加该 句不能修改记录的分区键&＃20540;，不能使记录分区迁移

　　PARTITION BY RANGE (TRANS_DATE)

　　(

　　PARTITION crm_detail_200309 VALUES LESS THAN

　　(TO_DATE (‘09/01/2003’,’mm/dd/yyyy’

　　TABLESPACE crm_detail_200309,

　　其他分区.....

　　;

　　6．建立基于分区的索引：

　　create index index_name on table_name (分区键&＃43;…)

　　global --这里是全局分区索引，也可以建本地索引

　　PARTITION BY RANGE (TRANS_DATE)

　　(

　　PARTITION index_detail_200309 VALUES LESS THAN

　　(TO_DATE ('09/01/2003','mm/dd/yyyy' )

　　TABLESPACE index_detail_200309,

　　其他索引分区...

　　;

　　对表的分区就这样完成了，第一次主要确定表分区的分区策约是最重要的，可我觉得对表分区难在以后对表分区的管理上面，因为随着数据量的增加，表分区必然存在删除，扩容，增加等。在这些过程中还牵涉到全局等索引，因为对分区表进行ddl操作为破坏全局索引，故全局索引必须在ddl后要重 rebuild.
---------------------------------------------------------------------分区3-----------------------------------------------------------------

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。

方法一：利用原表重建分区表。


步骤：


SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);


表已创建。


SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;


已创建6264行。


SQL> COMMIT;


提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(''2004-7-1'', ''YYYY-MM-DD'')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE(''2005-1-1'', ''YYYY-MM-DD'')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE(''2005-7-1'', ''YYYY-MM-DD'')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE))
6 AS SELECT ID, TIME FROM T;


表已创建。


SQL> RENAME T TO T_OLD;


表已重命名。


SQL> RENAME T_NEW TO T;


表已重命名。


SQL> SELECT COUNT(*) FROM T;


COUNT(*)
----------
6264


SQL> SELECT COUNT(*) FROM T PARTITION (P1);


COUNT(*)
----------
0


SQL> SELECT COUNT(*) FROM T PARTITION (P2);


COUNT(*)
----------
6246


SQL> SELECT COUNT(*) FROM T PARTITION (P3);


COUNT(*)
----------
18


优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。


不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。


适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。




方法二：使用交换分区的方法。


步骤：


SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);


表已创建。


SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;


已创建6264行。


SQL> COMMIT;


提交完成。


SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(''2005-7-1'', ''YYYY-MM-DD'')),
3 PARTITION P2 VALUES LESS THAN (MAXVALUE));


表已创建。


SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;


表已更改。


SQL> RENAME T TO T_OLD;


表已重命名。


SQL> RENAME T_NEW TO T;


表已重命名。


SQL> SELECT COUNT(*) FROM T;


COUNT(*)
----------
6264


优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。


不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。


适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。




方法三：Oracle9i以上版本，利用在线重定义功能


步骤：


SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);


表已创建。


SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;


已创建6264行。


SQL> COMMIT;


提交完成。


SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, ''T'', DBMS_REDEFINITION.CONS_USE_PK);


PL/SQL 过程已成功完成。


SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE(''2004-7-1'', ''YYYY-MM-DD'')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE(''2005-1-1'', ''YYYY-MM-DD'')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE(''2005-7-1'', ''YYYY-MM-DD'')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE));


表已创建。


SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, ''T'', ''T_NEW'', -
> ''ID ID, TIME TIME'', DBMS_REDEFINITION.CONS_USE_PK);


PL/SQL 过程已成功完成。


SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(''YANGTK'', ''T'', ''T_NEW'');


PL/SQL 过程已成功完成。


SQL> SELECT COUNT(*) FROM T;


COUNT(*)
----------
6264


SQL> SELECT COUNT(*) FROM T PARTITION (P2);


COUNT(*)
----------
6246


SQL> SELECT COUNT(*) FROM T PARTITION (P3);


COUNT(*)
----------
18


优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。


不足：实现上比上面两种略显复杂。


适用于各种情况。


这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。


Oracle的在线重定义表功能：http://blog.itpub.net/post/468/12855


Oracle的在线重定义表功能（二）：http://blog.itpub.net/post/468/12962





二、索引分区的概念 及建索引方法


索引分区是在您建立了表分区后，要建索引就必须是建立索引分区。分2大类：一类是把索引信息建立在各个分区上，这叫局部索引分区（或叫本地索引分区）。另一类是把索引集中起来，叫全局索引。

1、局部索引又分2类。
建立方法：



create index ind_1 on dept (deptno)
local
(partition d1 ,
partition d2);
（1）局部前缀索引分区和局部非前缀分区。如果您拟建立的索引的首个字段，和进行分区时的range列一样，那就是局部前缀索引分区。
优点是：理论上（我认为的），比方说您以年代为range分区，2007年一个分区、2008年一个分区，然后您又在这个时间列上建立了局部前缀索引分区，那么ORACLE就会直接利用这个区上的索引仅进行这个分区上的搜索，所以效率会很高。
在我建立的2000万的表中进行查询，实践是，这个局部前缀复合索引的花销cost是5，而没有分区前是4。当然这也无所谓了。又进行了其他几个查询，其cost都相差无几。
（2）局部非前缀索引。如果您建立索引的列的首个字段不是range列，那么就叫局部非前缀索引。
优点是：如果您查一个电话号码，它在每年都会出现，当您要count汇总时，这种索引就会同时把这几个分区进行并行处理查询，速度理论上要快。
但我的试验比较令我失望：我建了一个2000万的无分区的表，然后把这个表又复制了一遍，进行了6个分区。但结果在对某列进行查询统计时，如果在一个分区，两者速度相差不大，分区的查询速度是：0.25m，无分区的查询速度是：0.065m。但在我期望的跨区统计时，分区的第一次统计时间是：61.875m，第二次是：10m；而无分区的表仅为：3.703m。

2、全局索引。
建立方法：


create index ind_2 on sales (amount_sold)
global partition by range (amount_sold)
(partition d1 ,
partition d2);
因为全局索引的首个字段必须是range字段，所以就无所谓前缀和非前缀了，都是前缀。
经过试验，我觉得建立全局索引的速度要略逊于局部前缀索引。

也可能是我的能力问题，现在觉得建立分区还不然不建立索引。如果大家能给我解惑

------------------------------------------------------------------------------------------------------------------------

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。







方法一：利用原表重建分区表。


步骤：


SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);


表已创建。


SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;


已创建6264行。


SQL> COMMIT;


提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE))
6 AS SELECT ID, TIME FROM T;


表已创建。


SQL> RENAME T TO T_OLD;


表已重命名。


SQL> RENAME T_NEW TO T;


表已重命名。


SQL> SELECT COUNT(*) FROM T;


COUNT(*)
----------
6264


SQL> SELECT COUNT(*) FROM T PARTITION (P1);


COUNT(*)
----------
0


SQL> SELECT COUNT(*) FROM T PARTITION (P2);


COUNT(*)
----------
6246


SQL> SELECT COUNT(*) FROM T PARTITION (P3);


COUNT(*)
----------
18


优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。


不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。


适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。




方法二：使用交换分区的方法。


步骤：


SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);


表已创建。


SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;


已创建6264行。


SQL> COMMIT;


提交完成。


SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (MAXVALUE));


表已创建。


SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;


表已更改。


SQL> RENAME T TO T_OLD;


表已重命名。


SQL> RENAME T_NEW TO T;


表已重命名。


SQL> SELECT COUNT(*) FROM T;


COUNT(*)
----------
6264


优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。


不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。


适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。




方法三：Oracle9i以上版本，利用在线重定义功能


步骤：


SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);


表已创建。


SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;


已创建6264行。


SQL> COMMIT;


提交完成。


SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);


PL/SQL 过程已成功完成。


SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
5 PARTITION P4 VALUES LESS THAN (MAXVALUE));


表已创建。


SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW', -
> 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);


PL/SQL 过程已成功完成。


SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');


PL/SQL 过程已成功完成。


SQL> SELECT COUNT(*) FROM T;


COUNT(*)
----------
6264


SQL> SELECT COUNT(*) FROM T PARTITION (P2);


COUNT(*)
----------
6246


SQL> SELECT COUNT(*) FROM T PARTITION (P3);


COUNT(*)
----------
18


优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。


不足：实现上比上面两种略显复杂。


适用于各种情况。


这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。


Oracle的在线重定义表功能：http://blog.itpub.net/post/468/12855


Oracle的在线重定义表功能（二）：http://blog.itpub.net/post/468/12962




索引也可以进行分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALDED，必须在执行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进行分区维护的同时重建全局索引。

全局索引可以包含多个分区的&＃20540; 局部索引比全局索引容易管理,而全局索引比较快
注意:不能为散列分区 或者 子分区创建全局索引


Oracle的分区功能十分强大。不过用起来发现有两点不大方便：


第一是已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能，可以通过这种方式来完成普通表到分区表的转化。可以参考这个例子：http://blog.itpub.net/post/468/13091


第二点是如果采用了local分区索引，那么在增加表分区的时候，索引分区的表空间是不可控制的。如果希望将表和索引的分区分开到不同的表空间且不同索引分区也分散到不同的表空间中，那么只能在增加分区后，对新增的分区索引单独rebuild。

Oracle最大允许存在多少个分区呢?

我们可以从Oracle的Concepts手册上找到这个信息，对于Oracle9iR2:

Tables can be partitioned into up to 64,000 separate partitions.

对于Oracle10gR2，Oracle增强了分区特性：

Tables can be partitioned into up to 1024K-1 separate partitions.

关于何时应该进行分区,Oracle有如下建议:

■ 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.