把非分区表改为分区表

个人分类：性能调优

--说明：把非分区表改为分区表适用于历史表

---1、创建分区表（结构和非分区表TBL_STOCK_BALANCE_LOG相同）

CREATE TABLE TBL_STOCK_BALANCE_LOG_PART1

(

ACCOUNT_ID VARCHAR2(20 BYTE),

OCCUR_DATE DATE,

BRANCH_ID NUMBER(10),

MONEY_TYPE VARCHAR2(10 BYTE),

MONEY_TYPE_NAME VARCHAR2(20 BYTE),

MARKET_CODE VARCHAR2(10 BYTE),

MARKET_NAME VARCHAR2(30 BYTE),

STOCK_CODE VARCHAR2(10 BYTE),

STOCK_NAME VARCHAR2(60 BYTE),

STOCK_TYPE_CODE VARCHAR2(40 BYTE),

STOCK_TYPE_NAME VARCHAR2(60 BYTE),

QTY NUMBER(18),

STOCKVALUE NUMBER(18,4),

INDUST_NUM VARCHAR2(20 BYTE),

CLOSE_PRICE NUMBER(18,4),

ASSET_ACCT NUMBER(18,4),

REFER_COST NUMBER(18,4),

REFER_BREAK NUMBER(18,4),

REFER_BREAK_RA NUMBER(18,4),

INDUST_ID VARCHAR2(20 BYTE),

DEPTID VARCHAR2(10 BYTE)

)

Partition by range (OCCUR_DATE)

(

partition p_other values less than (maxvalue)

);

---2、交换数据（数据从非分区表到分区表）

alter table TBL_STOCK_BALANCE_LOG_PART1 exchange partition p_other with table TBL_STOCK_BALANCE_LOG;

---SELECT * FROM TBL_STOCK_BALANCE_LOG_PART1;

---3、查询分区表数据

select to_char(OCCUR_DATE, 'YYYY-MM'), count(*) from TBL_STOCK_BALANCE_LOG_PART1 group by to_char(OCCUR_DATE, 'YYYY-MM');

---辅助脚本

select

'alter table TBL_STOCK_BALANCE_LOG_PART1 split partition p_other at (to_date('''||to_char(OCCUR_DATE,'yyyy-mm')||''',''yyyy-mm'')) into (partition p'||to_char(OCCUR_DATE,'yyyymm')|| ',partition p_other);'

from TBL_STOCK_BALANCE_LOG_PART1

group by to_char(OCCUR_DATE,'yyyy-mm'),to_char(OCCUR_DATE,'yyyymm')

order by to_char(OCCUR_DATE,'yyyymm')

---执行脚本生成语句

alter table TBL_STOCK_BALANCE_LOG_PART1 split partition p_other at (to_date('2011-08','yyyy-mm'))

into (partition p201108,partition p_other);

alter table TBL_STOCK_BALANCE_LOG_PART1 split partition p_other at (to_date('2011-09','yyyy-mm'))

into (partition p201109,partition p_other);

---4、删除非分区表

DROP TABLE TBL_STOCK_BALANCE_LOG PURGE;

---5、将分区表重命名为原分区表名

ALTER TABLE TBL_STOCK_BALANCE_LOG_PART1 RENAME TO TBL_STOCK_BALANCE_LOG;

---6、测试

select table_name, partition_name from user_tab_partitions where table_name = 'TBL_STOCK_BALANCE_LOG';

select COUNT(*) from TBL_STOCK_BALANCE_LOG partition(P201109)