使用exchange方式切换普通表到分区表_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1908 | 回复: 0   主题: 使用exchange方式切换普通表到分区表        下一篇 
刘习托
注册用户
等级:中士
经验:208
发帖:66
精华:0
注册:2011-11-23
状态:离线
发送短消息息给刘习托 加好友    发送短消息息给刘习托 发消息
发表于: IP:您无权察看 2015-7-13 11:15:03 | [全部帖] [楼主帖] 楼主

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

2、准备环境   

--创建用户


SQL> createuser leshami identified by xxx;
SQL> grant dba to leshami;
--创建演示需要用到的表空间


SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf'size 10m autoextend on;
SQL> alteruser leshami default tablespace tbs_tmp;
SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf'size 10m autoextend on;
SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf'size 10m autoextend on;
SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf'size 10m autoextend on;
SQL> conn leshami/xxx
-- 创建一个lookup表


CREATETABLE lookup (
id            NUMBER(10),
description   VARCHAR2(50)
);
--添加主键约束


ALTERTABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARYKEY (id)
);
--插入数据


INSERTINTO lookup (id, description) VALUES (1, 'ONE');
INSERTINTO lookup (id, description) VALUES (2, 'TWO');
INSERTINTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
--创建一个用于切换到分区的大表


CREATETABLE big_table (
id            NUMBER(10),
created_date  DATE,
lookup_id     NUMBER(10),
data          VARCHAR2(50)
);
--填充数据到大表


DECLARE
l_lookup_id    lookup.id%TYPE;
l_create_date  DATE;
BEGIN
FOR i IN 1 .. 10000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id   := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id   := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id   := 3;
END IF;
INSERTINTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for '   i);
END LOOP;
COMMIT;
END;
/
--为大表添加主、外键约束,索引,以及添加触发器等.


ALTERTABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARYKEY (id)
);
CREATEINDEX bita_created_date_i ON big_table(created_date);
CREATEINDEX bita_look_fk_i ON big_table(lookup_id);
ALTERTABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGNKEY (lookup_id)
REFERENCES lookup(id)
);
CREATEORREPLACETRIGGER tr_bf_big_table
BEFORE UPDATEOF created_date
ON big_table
FOR EACH ROW
BEGIN
:new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');
END tr_bf_big_table;
/
--收集统计信息
EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);


3、创建分区表

CREATETABLE big_table2 (
id            NUMBER(10),
created_date  DATE,
lookup_id     NUMBER(10),
data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);
ALTERTABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARYKEY (id)
);
CREATEINDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATEINDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTERTABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGNKEY (lookup_id)
REFERENCES lookup(id)
);
--触发器也需要单独添加到分区表
CREATEORREPLACETRIGGER tr_bf_big_table2
BEFORE UPDATEOF created_date
ON big_table2
FOR EACH ROW
BEGIN
:new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');
END tr_bf_big_table2;
/


4、使用exchange切换为分区表

--下面的这个命令就是通过exchange方式来直接将普通表来切换为分区表
ALTERTABLE big_table2
EXCHANGE PARTITION big_table_2014
WITHTABLE big_table
WITHOUT VALIDATION
UPDATEGLOBAL INDEXES;
SQL> selectcount(*) from big_table2;
COUNT(*)
----------
10000
DROPTABLE big_table;
RENAME big_table2 TO big_table;
ALTERTABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTERTABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTERINDEX big_table_pk2 RENAME TO big_table_pk;
ALTERINDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTERINDEX bita_created_date_i2 RENAME TO bita_created_date_i;
ALTERTRIGGER tr_bf_big_table2 RENAME TO tr_bf_big_table;


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

ALTERTABLE big_table
SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2012 tablespace tbs1 ,
PARTITION big_table_2014)
UPDATEGLOBAL INDEXES;
ALTERTABLE big_table
SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2013 tablespace tbs2,
PARTITION big_table_2014)
UPDATEGLOBAL INDEXES;
--收集统计信息,如果表很大的话,需要考虑使用并行度,采样值


--对于上述的操作中,本地分区索引和数据存储在指定的表空间,存在混用情形;对于全局索引则保存在缺省表空间,


--上面提到的2种情形,可以根据需要作相应调整


EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);
--验证结果


SQL> col HIGH_VALUE format a45 wrapped
SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions
2  where table_name='BIG_TABLE';
TABLE_NAME      PARTITION_NAME       HIGH_VALUE                                      NUM_ROWS
--------------- -------------------- --------------------------------------------- ----------
BIG_TABLE       BIG_TABLE_2012       TO_DATE(' 2012-12-31 23:59:59', 'SYYYY-MM-DD        3333
HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
BIG_TABLE       BIG_TABLE_2013       TO_DATE(' 2013-12-31 23:59:59', 'SYYYY-MM-DD        3334
HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
BIG_TABLE       BIG_TABLE_2014       MAXVALUE                                            3333


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论