oracle分区表的建立方法

oracle分区表的建立方法

Oracle的分区表能够包括多个分区，每个分区差不多上一个独立的段（SEGMENT），能够存放到不同的表空间中。查询时能够通过查询表来访咨询各个分区中的数据，也能够通过在查询时直截了当指定分区的方法来进行查询。

分区提供以下优点：

由于将数据分散到各个分区中，减少了数据损坏的可能性；

能够对单独的分区进行备份和复原；

能够将分区映射到不同的物理磁盘上，来分散IO；

提高可治理性、可用性和性能。

Oracle提供了以下几种分区类型：

范畴分区（range）；

哈希分区（hash）；

列表分区（list）；

范畴－哈希复合分区（range-hash）；

范畴－列表复合分区（range-list）。

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 V ALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 V ALUES LESS THAN (TO_DA TE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 V ALUES LESS THAN (TO_DA TE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 V ALUES LESS THAN (MAXV ALUE))

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(*)

----------

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 DA TE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 V ALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 V ALUES LESS THAN (MAXV ALUE));

表已创建。

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操作，会增加操作的复杂度，效率也会降低。

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