Oracle分区表，哈希分区的新建与增加

来源：互联网发布：人工智能会议 2017 编辑：程序博客网时间：2024/06/15 08:29

一、背景知识

Oralce中的一张表数据量达到亿数量级后或是单表达到2G大小，查询效率似乎会明显下降。需要通过分区的方式，从行的维度对表进行划分，避免单表数据量过大

分区方法有下面几类：

范围，最常见，按照某列数据的范围分区，比如按时间分区
哈希，对于数据规律性不强，或者取值范围难以确定的，推荐哈希法，强行进行分区。分区个数需设置成2的幂次
列表，类似枚举，需事先知道字段的精确值
混合，上述各种组合

oracle 11g又新增了几种分区方法，如下：

引用分区，父表的外键作为关键字建立分区，则子表可以以同样的方式分区
虚拟列分区，通过表中字段的运算结果进行分区
间隔分区，范围分区的变种，范围固定且分区可自动扩展
系统分区，insert和select时，全完手工指定分区

参考资料：http://space.itpub.net/7607759/viewspace-260278

http://tech.it168.com/db/2008-03-24/200803241734089.shtml

二、新建分区

这里就写下在线重定义的方法，离线的就不写了。

1.新建一张表

CREATE TABLE T_HASH (ID NUMBER PRIMARY KEY, TIME DATE);

2.往里面插测试数据

3.测试权限

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(userName, 'T_HASH', DBMS_REDEFINITION.CONS_USE_PK);

如果提示没有权限，通过dba账户添加当前账户权限，或者直接用dba账户操作

4.建一个与原表结构一样的临时表，需在临时表建分区

CREATE TABLE T_TMP (ID NUMBER PRIMARY KEY, TIME DATE)partition by hash (ID)(  partition p1 tablespace spaceName,  partition p2 tablespace spaceName,);

5. 在线重定义

表的在线重定义

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(userName, 'T_HASH', 'T_TMP');

数据同步，保证操作过程中，临时表和原表的数据一致性

execute dbms_redefinition.sync_interim_table(userName,'T_HASH','T_TMP');

执行结束

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(userName, 'T_HASH', 'T_TMP');

6.检查结果

SELECT COUNT(1) FROM T_HASH;SELECT COUNT(1) FROM T_HASH PARTITION (P1);SELECT COUNT(1) FROM T_HASH PARTITION (P2);

7.删除临时表

参考资料：http://www.blogjava.net/willpower88/archive/2007/04/19/111987.html

三、增加分区

对于哈希分区，一条SQL搞定：

ALTER TABLE tableName ADD PARTITION partitionName;