Oralce普通表 ->分区表

A:内信息表结构更改
1.1.	创建站内信息表RANGE分区表
-- Create table
create table US_INTERNALINFO_BACK
(
  MSGID      NUMBER(19) not null,
  RECMSISDN  VARCHAR2(11) not null,
  SENDMSISDN VARCHAR2(11) not null,
  TYPE       VARCHAR2(2) not null,
  ISREAD     VARCHAR2(1) not null,
  SENDTIME   DATE not null,
  MESSAGE    CLOB,
  TITLE      VARCHAR2(90),
  DELTYPE    VARCHAR2(1) default '0'
)
partition by range(SENDTIME)
interval(numtoyminterval(1,'month'))
 (PARTITION P1 values LESS THAN(to_date('2009-01-01','yyyy-mm-dd'))
) 
tablespace TBS_MREAD_DAT;
1.1.1.	更改会话中db_file_multiblock_read_count的值
Sql>Alter session set db_file_multiblock_read_count=128;
1.1.2.	往临时表中插入数据（测试环境34199648记录需26分钟）
insert /*+ append */
  into US_INTERNALINFO_BACK
select * 
  from US_INTERNALINFO t;
Commit;
1.2.	更改站内信息表名称和索引名称
1.2.1.	更改站内信息表表名
sql> alter table US_INTERNALINFO rename to US_INTERNALINFO_BAK1108;
sql>alter table US_INTERNALINFO_BAK1108 rename constraint PK_US_INTERNALINFO to PK_US_INTERNALINFO_BAK1108;
1.2.2.	更改表索引名
sql> ALTER index IDX_US_INTERNALINFO_SENDMSISDN  RENAME TO IDX_US_INTERNALINFO_SEND1108;
sql> ALTER index INDEX_US_INTERNALINFO RENAME TO INDEX_US_INTERNALINFO_BAK1108;
SQL> ALTER index PK_US_INTERNALINFO RENAME TO PK_US_INTERNALINFO_BAK1108;
1.3.	在临时表上创建索引(注意：操作时确保TBS_MREAD_IDX表空间大小够用)
1.3.1.	更改临时表表名
alter table US_INTERNALINFO_BACK rename to US_INTERNALINFO;
1.3.2.	创建主键
-- 创建主键和索引
alter table US_INTERNALINFO add constraint PK_US_INTERNALINFO primary key (MSGID) using index  TABLESPACE tbs_mread_idx NOLOGGING;
1.3.3.	创建索引
sql> alter session set workarea_size_policy=manual;
sql> alter session set sort_area_size=1073741824;
sql> alter session set sort_area_retained_size=1073741824; 
sql> alter session set db_file_multiblock_read_count=128;


create index IDX_US_INTERNALINFO_SENDMSISDN on US_INTERNALINFO (SENDMSISDN) local
  tablespace TBS_MREAD_IDX NOLOGGING;           --测试环境执行时间4分钟
  
create index INDEX_US_INTERNALINFO on US_INTERNALINFO (RECMSISDN) local
  tablespace TBS_MREAD_IDX NOLOGGING;           --测试环境执行时间4分钟
1.4.	统计信息收集(测试环境6分30秒)
BEGIN
dbms_stats.gather_table_stats(
ownname => 'mread',
tabname => 'US_INTERNALINFO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => true,
method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'all' ); 
END;
1.5.	验证执行计划
1.5.1.	创建存储过程
PRO_MESSAGE_DELETE.sql
B:内信息表结构回滚
1.6.	删除站内信息表RANGE分区表
-- Create table
sql>drop table US_INTERNALINFO

1.6.1.	还原更改站内信息表表名
sql>alter table US_INTERNALINFO_BAK1108 rename to US_INTERNALINFO;
sql>alter table US_INTERNALINFO rename constraint PK_US_INTERNALINFO_BAK1108 to PK_US_INTERNALINFO;

1.6.2.	还原更改表索引名
sql> ALTER index IDX_US_INTERNALINFO_SEND1108 RENAME TO IDX_US_INTERNALINFO_SENDMSISDN;
sql> ALTER index INDEX_US_INTERNALINFO_BAK1108 RENAME TO INDEX_US_INTERNALINFO;
sql> ALTER index PK_US_INTERNALINFO_BAK1108  RENAME TO PK_US_INTERNALINFO;

sql> DROP  PROCEDURE  PRO_MESSAGE_DELETE