mysql表在线转成分区表_11g普通表在线转换分区表

本帖最后由 灯和树 于 2016-5-4 14:58 编辑

由于业务系统数据量增大，对其用户表在线完成分区表转换过程，记录如下，11g数据库支持。创建过渡分区表

根据USER_ID创建分区表

CREATE TABLE SDP_SMECD.TEST_T_USER_ID

(

USER_ID            NUMBER(13) not null,

SUBSCRIBER_ID      VARCHAR2(100),

SMARTCARD_ID       VARCHAR2(100),

TVCODE             VARCHAR2(32),

STB_SEQID          NUMBER(13),

PASSWORD           VARCHAR2(20),

REGION_CODE        VARCHAR2(32),

USER_CODE          VARCHAR2(100) not null,

USER_NAME          VARCHAR2(200),

USER_ALIAS         VARCHAR2(100),

REGISTER_TIME      DATE,

STATUS             VARCHAR2(2) not null,

TOKEN              VARCHAR2(32),

BUSINESS_AREA_CODE VARCHAR2(8),

SERV_TYPES         CHAR(10) not null,

CREDIT_GRADE       VARCHAR2(2),

CREDIT             NUMBER(8),

USER_LEVEL         VARCHAR2(2),

PAY_TYPE           NUMBER(2),

REMAIN_CREDIT      NUMBER(8),

USER_CLASS         NUMBER(2) not null,

STB_SN             VARCHAR2(32),

IMS_ACCOUNT        VARCHAR2(20),

BIND_USER_ID       NUMBER(13),

USER_TYPE          NUMBER(2),

USER_OWNER         NUMBER(2),

UPDATE_TIME        DATE,

BOSS_AREACODE      VARCHAR2(15),

VIDEO_TYPE         VARCHAR2(150)

)

PARTITION by RANGE(user_id)

INTERVAL (2000000)

(

PARTITION TEST_T_USER_UPT_PARTP1 VALUES LESS THAN (3877397)

tablespace TS_TEST_BIG_SEGMENT

pctfree 20    ---增加pctfree，表update较频繁，故pctfree加大，默认为10

)

tablespace TS_TEST_BIG_SEGMENT

pctfree 20;---增加pctfree在线转换普通表为分区表

exec dbms_redefinition.can_redef_table('SDP_SMECD','TEST_T_USER');

exec dbms_redefinition.start_redef_table('SDP_SMECD', 'TEST_T_USER', 'TEST_T_USER_ID');

--220s

--如果start成功不需要执行abort

exec dbms_redefinition.abort_redef_table('SDP_SMECD', 'TEST_T_USER', 'TEST_T_USER_ID');

exec dbms_redefinition.sync_interim_table('SDP_SMECD', 'TEST_T_USER', 'TEST_T_USER_ID');   --14s

exec dbms_redefinition.finish_redef_table('SDP_SMECD', 'TEST_T_USER', 'TEST_T_USER_ID'); -1s创建分区表的索引创建全局分区主键

alter table SDP_SMECD.TEST_T_USER

add constraint PK_TEST_T_USER_USERID primary key (USER_ID)

using index local

tablespace TS_TEST_BIG_SEGMENT

pctfree 10;创建非前缀本地分区

create index  SDP_SMECD.IND_TEST_T_USER_UPT ON SDP_SMECD.TEST_T_USER(update_time) local

tablespace TS_TEST_BIG_SEGMENT

pctfree 20;

select /*+index(seg IDX_USER_UPDATE_TIME)*/

seg.*

from SDP_SMECD.TEST_T_USER seg

where update_time > sysdate - 130

and update_time < sysdate - 100;

注意：提示执行索引的语句中必须使用表的别名

索引名前不需要加模式

select /*+index(seg IDX_USER_UPDATE_TIME)*/

seg.*

from SDP_SMECD.T_USER seg

where update_time > sysdate - 130

and update_time < sysdate - 100;

分区表update_time索引和普通表的update_time索引I/O吞吐率相差无几。

而且如果在DATE日期上面创建索引，范围稍微大点，数据库会默认选择全表扫描。