ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 使用exchange方式切换普通表到分区表

使用exchange方式切换普通表到分区表

Oracle 作者：lixiao0018 时间：2014-12-11 11:28:24 0 删除 编辑
转自： http://blog.csdn.net/leshami/article/details/34117857

  随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描述的是使用EXCHANGE PARTITION方式来实现，下面是具体的操作示例。

      有关具体的dbms_redefinition在线重定义表的原理及步骤可参考：基于 dbms_redefinition 在线重定义表 
      有关使用DBMS_REDEFINITION在线重定义分区表可参考：使用DBMS_REDEFINITION在线切换普通表到分区表
      有关分区表的描述请参考：Oracle 分区表

 

1、主要步骤
    a、为新的分区表准备相应的表空间
    b、基于源表元数据创建分区表以及相关索引、约束等
    c、使用exchange方式将普通表切换为分区表
    d、更正相关索引及约束名等(可省略)
    e、使用split根据需要将分区表分割为多个不同的分区
    f、收集统计信息

 

2、准备环境     

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. --创建用户  
  2. SQL> create user leshami identified by xxx;  
  3.   
  4. SQL> grant dba to leshami;  
  5.   
  6. --创建演示需要用到的表空间  
  7. SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;  
  8.   
  9. SQL> alter user leshami default tablespace tbs_tmp;  
  10.   
  11. SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;  
  12.   
  13. SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;  
  14.   
  15. SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;  
  16.   
  17. SQL> conn leshami/xxx  
  18.   
  19. -- 创建一个lookup表  
  20. CREATE TABLE lookup (  
  21.   id            NUMBER(10),  
  22.   description   VARCHAR2(50)  
  23. );  
  24.   
  25. --添加主键约束  
  26. ALTER TABLE lookup ADD (  
  27.   CONSTRAINT lookup_pk PRIMARY KEY (id)  
  28. );  
  29.   
  30. --插入数据  
  31. INSERT INTO lookup (id, description) VALUES (1, 'ONE');  
  32. INSERT INTO lookup (id, description) VALUES (2, 'TWO');  
  33. INSERT INTO lookup (id, description) VALUES (3, 'THREE');  
  34. COMMIT;  
  35.   
  36. --创建一个用于切换到分区的大表  
  37. CREATE TABLE big_table (  
  38.   id            NUMBER(10),  
  39.   created_date  DATE,  
  40.   lookup_id     NUMBER(10),  
  41.   data          VARCHAR2(50)  
  42. );  
  43.   
  44. --填充数据到大表  
  45. DECLARE  
  46.   l_lookup_id    lookup.id%TYPE;  
  47.   l_create_date  DATE;  
  48. BEGIN  
  49.   FOR i IN 1 .. 10000 LOOP  
  50.     IF MOD(i, 3) = 0 THEN  
  51.       l_create_date := ADD_MONTHS(SYSDATE, -24);  
  52.       l_lookup_id   := 2;  
  53.     ELSIF MOD(i, 2) = 0 THEN  
  54.       l_create_date := ADD_MONTHS(SYSDATE, -12);  
  55.       l_lookup_id   := 1;  
  56.     ELSE  
  57.       l_create_date := SYSDATE;  
  58.       l_lookup_id   := 3;  
  59.     END IF;  
  60.       
  61.     INSERT INTO big_table (id, created_date, lookup_id, data)  
  62.     VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);  
  63.   END LOOP;  
  64.   COMMIT;  
  65. END;  
  66. /  
  67.   
  68. --为大表添加主、外键约束，索引，以及添加触发器等.  
  69. ALTER TABLE big_table ADD (  
  70.   CONSTRAINT big_table_pk PRIMARY KEY (id)  
  71. );  
  72.   
  73. CREATE INDEX bita_created_date_i ON big_table(created_date);  
  74.   
  75. CREATE INDEX bita_look_fk_i ON big_table(lookup_id);  
  76.   
  77. ALTER TABLE big_table ADD (  
  78.   CONSTRAINT bita_look_fk  
  79.   FOREIGN KEY (lookup_id)  
  80.   REFERENCES lookup(id)  
  81. );  
  82.   
  83. CREATE OR REPLACE TRIGGER tr_bf_big_table  
  84.    BEFORE UPDATE OF created_date  
  85.    ON big_table  
  86.    FOR EACH ROW  
  87. BEGIN  
  88.    :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');  
  89. END tr_bf_big_table;  
  90. /  
  91.   
  92. --收集统计信息  
  93. EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);  
  94. EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);  

3、创建分区表

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. CREATE TABLE big_table2 (     
  2.   id            NUMBER(10),     
  3.   created_date  DATE,     
  4.   lookup_id     NUMBER(10),     
  5.   data          VARCHAR2(50)     
  6. )     
  7. PARTITION BY RANGE (created_date)     
  8. (PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);    
  9.   
  10. ALTER TABLE big_table2 ADD (  
  11.   CONSTRAINT big_table_pk2 PRIMARY KEY (id)  
  12. );  
  13.   
  14. CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;  
  15.   
  16. CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;  
  17.   
  18. ALTER TABLE big_table2 ADD (  
  19.   CONSTRAINT bita_look_fk2  
  20.   FOREIGN KEY (lookup_id)  
  21.   REFERENCES lookup(id)  
  22. );  
  23.   
  24. --触发器也需要单独添加到分区表  
  25. CREATE OR REPLACE TRIGGER tr_bf_big_table2              --Author: Leshami  
  26.    BEFORE UPDATE OF created_date                        --Blog   : http://blog.csdn.net/leshami  
  27.    ON big_table2  
  28.    FOR EACH ROW  
  29. BEGIN  
  30.    :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');  
  31. END tr_bf_big_table2;  
  32. /  

4、使用exchange切换为分区表

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. --下面的这个命令就是通过exchange方式来直接将普通表来切换为分区表  
  2. ALTER TABLE big_table2  
  3.   EXCHANGE PARTITION big_table_2014  
  4.   WITH TABLE big_table  
  5.   WITHOUT VALIDATION  
  6.   UPDATE GLOBAL INDEXES;  
  7.     
  8. SQL> select count(*) from big_table2;  
  9.   
  10.   COUNT(*)  
  11. ----------  
  12.      10000  
  13.   
  14. DROP TABLE big_table;  
  15. RENAME big_table2 TO big_table;  
  16.   
  17. ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;  
  18. ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;  
  19. ALTER INDEX big_table_pk2 RENAME TO big_table_pk;  
  20. ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;  
  21. ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;  
  22. ALTER TRIGGER tr_bf_big_table2 RENAME TO tr_bf_big_table;  

5、使用split方式分割分区表

[sql] view plaincopyprint?在CODE上查看代码片派生到我的代码片
  1. ALTER TABLE big_table  
  2.   SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))  
  3.   INTO (PARTITION big_table_2012 tablespace tbs1 ,  
  4.         PARTITION big_table_2014)  
  5.   UPDATE GLOBAL INDEXES;  
  6.   
  7. ALTER TABLE big_table  
  8.   SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))  
  9.   INTO (PARTITION big_table_2013 tablespace tbs2,  
  10.         PARTITION big_table_2014)  
  11.   UPDATE GLOBAL INDEXES;  
  12.   
  13. --收集统计信息，如果表很大的话，需要考虑使用并行度，采样值  
  14. --对于上述的操作中，本地分区索引和数据存储在指定的表空间，存在混用情形；对于全局索引则保存在缺省表空间，  
  15. --上面提到的2种情形，可以根据需要作相应调整  
  16. EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);  
  17.   
  18. --验证结果  
  19. SQL> col HIGH_VALUE format a45 wrapped  
  20. SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions  
  21.   2  where table_name='BIG_TABLE';  
  22.   
  23. TABLE_NAME      PARTITION_NAME       HIGH_VALUE                                      NUM_ROWS  
  24. --------------- -------------------- --------------------------------------------- ----------  
  25. BIG_TABLE       BIG_TABLE_2012       TO_DATE(' 2012-12-31 23:59:59', 'SYYYY-MM-DD        3333  
  26.                                      HH24:MI:SS', 'NLS_CALENDAR=GREGORIA  
  27.   
  28. BIG_TABLE       BIG_TABLE_2013       TO_DATE(' 2013-12-31 23:59:59', 'SYYYY-MM-DD        3334  
  29.                                      HH24:MI:SS', 'NLS_CALENDAR=GREGORIA  
  30.   
  31. BIG_TABLE       BIG_TABLE_2014       MAXVALUE                                            3333       

本文参考：Partitioning an Existing Table using EXCHANGE PARTITION




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20970776/viewspace-1364208/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： shell中的特殊符号
下一篇： 浅谈Hadoop
请登录后发表评论 登录
全部评论
lixiao0018

注册时间：2014-10-13

  • 博文量
    16
  • 访问量
    22118

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593