--說明:把非分區表改為分區表适用于曆史表
---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)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24531354/viewspace-710208/,如需轉載,請注明出處,否則将追究法律責任。
轉載于:http://blog.itpub.net/24531354/viewspace-710208/