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

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用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> create user 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> alter user 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表

CREATE TABLE lookup (

id NUMBER(10),

description VARCHAR2(50)

);

--添加主键约束

ALTER TABLE lookup ADD (

CONSTRAINT lookup_pk PRIMARY KEY (id)

);

--插入数据

INSERT INTO lookup (id, description) VALUES (1, ‘ONE‘);

INSERT INTO lookup (id, description) VALUES (2, ‘TWO‘);

INSERT INTO lookup (id, description) VALUES (3, ‘THREE‘);

COMMIT;

--创建一个用于切换到分区的大表

CREATE TABLE 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;

INSERT INTO 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;

/

--为大表添加主、外键约束，索引，以及添加触发器等.

ALTER TABLE big_table ADD (

CONSTRAINT big_table_pk PRIMARY KEY (id)

);

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (

CONSTRAINT bita_look_fk

FOREIGN KEY (lookup_id)

REFERENCES lookup(id)

);

CREATE OR REPLACE TRIGGER tr_bf_big_table

BEFORE UPDATE OF 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、创建分区表CREATE TABLE 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);

ALTER TABLE big_table2 ADD (

CONSTRAINT big_table_pk2 PRIMARY KEY (id)

);

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (

CONSTRAINT bita_look_fk2

FOREIGN KEY (lookup_id)

REFERENCES lookup(id)

);

--触发器也需要单独添加到分区表

CREATE OR REPLACE TRIGGER tr_bf_big_table2 --Author: Leshami

BEFORE UPDATE OF created_date --Blog : http://blog.youkuaiyun.com/leshami

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方式来直接将普通表来切换为分区表

ALTER TABLE big_table2

EXCHANGE PARTITION big_table_2014

WITH TABLE big_table

WITHOUT VALIDATION

UPDATE GLOBAL INDEXES;

SQL> select count(*) from big_table2;

COUNT(*)

----------

10000

DROP TABLE big_table;

RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;

ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;

ALTER INDEX big_table_pk2 RENAME TO big_table_pk;

ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;

ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

ALTER TRIGGER tr_bf_big_table2 RENAME TO tr_bf_big_table;

5、使用split方式分割分区表ALTER TABLE 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)

UPDATE GLOBAL INDEXES;

ALTER TABLE 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)

UPDATE GLOBAL 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

   

更多参考

原文：http://blog.youkuaiyun.com/leshami/article/details/34117857