将普通表转变为分区表、索引分区，及与无分区前的查询效率比较

 

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字段，所以就无所谓前缀和非前缀了，都是前缀。
经过试验，我觉得建立全局索引的速度要略逊于局部前缀索引。

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