oracle 分区表的建立方法

1、.：.；Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表经过对分区列的判别，把分区列不同的记录，放到不同的分区中。分区完全对运用透明。 Oracle的分区表可以包括多个分区，每个分区都是一个独立的段SEGMENT，可以存放到不同的表空间中。查询时可以经过查询表来访问各个分区中的数据，也可以经过在查询时直接指定分区的方法来进展查询。 分区提供以下优点： 由于将数据分散到各个分区中，减少了数据损坏的能够性； 可以对单独的分区进展备份和恢复； 可以将分区映射到不同的物理磁盘上，来分散IO； 提高可管理性、可用性和性能。 Oracle提供了以下几种分区类型：

2、范围分区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 COM

3、MIT; 提交完成。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 (MAXV

4、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(*)- 0 SQL SELECT COUNT(*) FROM T PARTITION (P2); COUNT(*)- 6246 SQL SELECT COUNT(*) FROM T PARTITION (P3);

5、COUNT(*)- 18 优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据曾经在分布到各个分区中了。 缺乏：对于数据的一致性方面还需求额外的思索。由于几乎没有方法经过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修正能够会丧失，假设要保证一致性，需求在执行完语句后对数据进展检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。 适用于修正不频繁的表，在闲时进展操作，表的数据量不宜太大。 方法二：运用交换分区的方法。 步骤： SQL CR

6、EATE 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 PARTITI

7、ON 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

8、中能否存在数据，假设存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丧失。 缺乏：依然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。假设要求数据分布到多个分区中，那么需求进展分区的SPLIT操作，会添加操作的复杂度，效率也会降低。 适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进展操作。 方法三：Oracle9i以上版本，利用在线重定义功能 步骤： SQL CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 表已创建。 SQL INSERT INTO

9、 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(20

10、04-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.CO

11、NS_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操作

12、。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵敏性，对各种不同的需求都能满足。而且，可以在切换前进展相应的授权并建立各种约束，可以做到切换完成后不再需求任何额外的管理操作。 缺乏：实现上比上面两种略显复杂。 适用于各种情况。 这里只给出了在线重定义表的一个最简单的例子，详细的描画和例子可以参考下面两篇文章。 Oracle的在线重定义表功能： Oracle的在线重定义表功能二： 索引也可以进展分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进展。对于global索引，可以选择能否

13、分区，而且索引的分区可以不与表分区相对应。当对分区进展维护操作时，通常会导致全局索引的INVALDED，必需在执行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进展分区维护的同时重建全局索引。 全局索引可以包含多个分区的值 部分索引比全局索引容易管理,而全局索引比较快留意:不能为散列分区 或者 子分区创建全局索引Oracle的分区功能非常强大。不过用起来发现有两点不大方便： 第一是曾经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能，可以经过这种方式来完成普通表到分区表的转化。可以参考这个例子： 第二点是假设

14、采用了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 par