Oralce普通表 ->分区表
A:内信息表结构更改1.1.创建站内信息表RANGE分区表-- Create tablecreate 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_BACKselect * 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秒)BEGINdbms_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.sqlB:内信息表结构回滚1.6.删除站内信息表RANGE分区表-- Create tablesql>drop table US_INTERNALINFO1.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