很多时候我们建立了一个普通堆表,随着时间增长,dml会越来越慢,这个时候就会考虑到将该表变为分区表了,或者一开始忘记建分区表,把海量数据已经导进来之后,才想起来,这个时候就可以用在线重定义的方式将普通表变为分区表;
普通表转换为分区表的做法:其中一种便是使用oracle自带的包进行重定义,需要注意几点:①需要有相应的系统权限;②重定义的方式,默认按照主键,可选择的为rowid;
包中的定义,可以用pl/sql工具看下包dbms_redefinition的用法
源表temp,查看下建表语句
( statis_date number,
user_id VARCHAR2(16),
user_serv_number VARCHAR2(64),
acct_name VARCHAR2(512),
group_name VARCHAR2(100),
group_id VARCHAR2(20),
product_type VARCHAR2(20),
manager_name VARCHAR2(64),
qf_area_name VARCHAR2(60),
balance NUMBER,
cycle_id_start NUMBER,
bad_balance NUMBER
);
--建立索引
步骤一、测试能否表进行在线重定义,
出现错误
ORA-12089: cannot online redefine table "LZHM"."TEMP" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
原因是默认为主键在线重定义的方式,需要设定参数为2,按照rowid重定义;
PL/SQL procedure successfully completed
步骤二、创建一个和temp表结构一样的中间表(是分区表)
user_id VARCHAR2(16),
user_serv_number VARCHAR2(64),
acct_name VARCHAR2(512),
group_name VARCHAR2(100),
group_id VARCHAR2(20),
product_type VARCHAR2(20),
manager_name VARCHAR2(64),
qf_area_name VARCHAR2(60),
balance NUMBER,
cycle_id_start NUMBER,
bad_balance NUMBER
partition by list (statis_date)
(
partition P20140731 values ('20140731')
tablespace TBS_user_tj
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
)
);
步骤三、开始进行重定义
execute dbms_redefinition.start_redef_table('lzhm','temp','temp_mid',null,2);
---------需要注意这里的输入参数选择
start_redef_table执行时间为12.5s 数据量为80万。
步骤三和步骤四中间步骤:
select count(*)
from temp;
select count(*)
from temp_mid;
----数据会有差别 而且与实际不符
----并且可以对表进行DML操作
步骤四、执行同步,保证数据的一致性:
execute dbms_redefinition.sync_interim_table ('lzhm','temp','temp_mid‘);
步骤五、开始重定义之后,完成重定义之前,需要在中间表上创建与源表对应的索引、外键、触发器等,而中间表与源表对应的主键,如果需要按照主键重定义,需要再开始redefine之前创建,如果按照rowid进行重定义,对应主键呢?
CREATE INDEX lzhm.IDX_WEBID_TICKET_CLIFO
步骤六、完成重定义
----完成重定义
execute dbms_redefinition.finish_redef_table('lzhm','temp','temp_mid');
select table_name,index_name,index_type,status
from user_indexes
where table_name in ('temp', 'temp_mid');
------看一下
此时的temp的索引为lzhm.IDX_WEBID_TICKET_CLIFO,而非原来lzhm.IDX_WEBID_TICKET_CLINFO.
步骤七:删除没有用的中间表
DROP TABLE lzhm.temp_mid
结论:
重定义的方式有2个按照rowid、主键;
重定义的过程中可以对表进行DML操作,适合7*24的系统
重定义的速度还是挺快的;
重定义需要用户有一定系统权限、需要一个中间表(简单起见中间表的结构最好和源表一样,不一样的情况没有测试)、重定义开始到重定义结束需要在中间表上建一系列约束;重定义完成之后的效果是:
源表结构、约束信息与中间表的互相交换,二者数据量一致。
可以普通表转为分区表;
分区表也可以重定义。