普通表转换成分区表
描述:将表trans_new转换成分区表，分区字段为data_date。
一、准备
SQL> select /*+ parallel(t,8)*/ count(*) from trans_new t;
COUNT(*)
----------
445600483
SQL> select /*+ parallel(t,8)*/ max(DATA_DATE),min(DATA_DATE) from edw.dw_fact_trans_new2014 t;
MAX(DATA_ MIN(DATA_
--------- ---------
05-MAY-14 01-JUL-13
rename trans_new to trans_new2014;
二、创建分区表
-- Create table
create table trans_new
(
id NUMBER not null,
data_date DATE,
mobile_num VARCHAR2(255),
mobile_secret VARCHAR2(255),
mobile_code VARCHAR2(21),
psam VARCHAR2(50),
psam_code VARCHAR2(20),
term_type VARCHAR2(50),
term_area VARCHAR2(20),
trancde VARCHAR2(100),
merno VARCHAR2(50),
tcat_lv4 VARCHAR2(20),
s_no VARCHAR2(50),
outcdno VARCHAR2(255),
outcdno_secret VARCHAR2(255),
outcdno_bin VARCHAR2(20),
incdno VARCHAR2(255),
incdno_secret VARCHAR2(255),
incdno_bin VARCHAR2(255),
pay_status VARCHAR2(50),
pay_respn VARCHAR2(50),
trans_respn VARCHAR2(50),
de37 VARCHAR2(50),
total_am NUMBER,
pro_am NUMBER,
free_am NUMBER,
sid VARCHAR2(255),
accetp_id NUMBER,
pay_id NUMBER,
content_id NUMBER,
is_sucess CHAR(1),
flag CHAR(1),
back1 VARCHAR2(255),
back2 VARCHAR2(256),
back3 VARCHAR2(257),
back4 VARCHAR2(258),
back5 VARCHAR2(259),
load_date DATE
) tablespace ETLDATA
partition by range (DATA_DATE)
(
partition DW_MONTH_201201 values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201202 values less than (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201203 values less than (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201204 values less than (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201205 values less than (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201206 values less than (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201207 values less than (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201208 values less than (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201209 values less than (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201210 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201211 values less than (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201212 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201301 values less than (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201302 values less than (TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201303 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201304 values less than (TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201305 values less than (TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201306 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201307 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201308 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201309 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201310 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201311 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201312 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201401 values less than (TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201402 values less than (TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201403 values less than (TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201404 values less than (TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201405 values less than (TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201406 values less than (TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201407 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201408 values less than (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201409 values less than (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201410 values less than (TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201411 values less than (TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201412 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201507 values less than (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201508 values less than (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201509 values less than (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201510 values less than (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201511 values less than (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_201512 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition DW_MONTH_MAX values less than (MAXVALUE)
);
三、插入
必须减少日志量。
alter table trans_new nologging;
SQL> insert /*+ append parallel(t,32)*/
2 into EDW.dw_fact_trans_new t
3 (ID,DATA_DATE,MOBILE_NUM,MOBILE_SECRET,MOBILE_CODE,PSAM,PSAM_CODE,TERM_TYPE,TERM_AREA,TRANCDE,MERNO,TCAT_LV4,S_NO,OUTCDNO,OUTCDNO_SECRET,OUTCDNO_BIN,INCDNO,INCDN
O_SECRET,INCDNO_BIN,PAY_STATUS,PAY_RESPN,TRANS_RESPN,DE37,TOTAL_AM,PRO_AM,FREE_AM,SID,ACCETP_ID,PAY_ID,CONTENT_ID,IS_SUCESS,FLAG,BACK1,BACK2,BACK3,BACK4,BACK5,LOAD_DATE
)
4 select /*+ parallel(t1,32)*/
5 ID,DATA_DATE,MOBILE_NUM,MOBILE_SECRET,MOBILE_CODE,PSAM,PSAM_CODE,TERM_TYPE,TERM_AREA,TRANCDE,MERNO,TCAT_LV4,S_NO,OUTCDNO,OUTCDNO_SECRET,OUTCDNO_BIN,INCDNO,INCDN
O_SECRET,INCDNO_BIN,PAY_STATUS,PAY_RESPN,TRANS_RESPN,DE37,TOTAL_AM,PRO_AM,FREE_AM,SID,ACCETP_ID,PAY_ID,CONTENT_ID,IS_SUCESS,FLAG,BACK1,BACK2,BACK3,BACK4,BACK5,LOAD_DATE
6 from EDW.dw_fact_trans_new2014 t1
7 where DATA_DATE < TO_DATE(' 2013-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN');
136255073 rows created.
Elapsed: 00:14:13.38
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.37
SQL>
SQL> insert /*+ append parallel(t,32)*/
2 into EDW.dw_fact_trans_new t
3 (ID,DATA_DATE,MOBILE_NUM,MOBILE_SECRET,MOBILE_CODE,PSAM,PSAM_CODE,TERM_TYPE,TERM_AREA,TRANCDE,MERNO,TCAT_LV4,S_NO,OUTCDNO,OUTCDNO_SECRET,OUTCDNO_BIN,INCDNO,INCDN
O_SECRET,INCDNO_BIN,PAY_STATUS,PAY_RESPN,TRANS_RESPN,DE37,TOTAL_AM,PRO_AM,FREE_AM,SID,ACCETP_ID,PAY_ID,CONTENT_ID,IS_SUCESS,FLAG,BACK1,BACK2,BACK3,BACK4,BACK5,LOAD_DATE
)
4 select /*+ parallel(t1,32)*/
5 ID,DATA_DATE,MOBILE_NUM,MOBILE_SECRET,MOBILE_CODE,PSAM,PSAM_CODE,TERM_TYPE,TERM_AREA,TRANCDE,MERNO,TCAT_LV4,S_NO,OUTCDNO,OUTCDNO_SECRET,OUTCDNO_BIN,INCDNO,INCDN
O_SECRET,INCDNO_BIN,PAY_STATUS,PAY_RESPN,TRANS_RESPN,DE37,TOTAL_AM,PRO_AM,FREE_AM,SID,ACCETP_ID,PAY_ID,CONTENT_ID,IS_SUCESS,FLAG,BACK1,BACK2,BACK3,BACK4,BACK5,LOAD_DATE
6 from EDW.dw_fact_trans_new2014 t1
7 where DATA_DATE >= TO_DATE(' 2013-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')
8 and DATA_DATE < TO_DATE(' 2014-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN');
152147213 rows created.
Elapsed: 00:21:27.15
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.40
SQL>
SQL>
SQL> insert /*+ append parallel(t,32)*/
2 into EDW.dw_fact_trans_new t
3 (ID,DATA_DATE,MOBILE_NUM,MOBILE_SECRET,MOBILE_CODE,PSAM,PSAM_CODE,TERM_TYPE,TERM_AREA,TRANCDE,MERNO,TCAT_LV4,S_NO,OUTCDNO,OUTCDNO_SECRET,OUTCDNO_BIN,INCDNO,INCDN
O_SECRET,INCDNO_BIN,PAY_STATUS,PAY_RESPN,TRANS_RESPN,DE37,TOTAL_AM,PRO_AM,FREE_AM,SID,ACCETP_ID,PAY_ID,CONTENT_ID,IS_SUCESS,FLAG,BACK1,BACK2,BACK3,BACK4,BACK5,LOAD_DATE
)
4 select /*+ parallel(t1,32)*/
5 ID,DATA_DATE,MOBILE_NUM,MOBILE_SECRET,MOBILE_CODE,PSAM,PSAM_CODE,TERM_TYPE,TERM_AREA,TRANCDE,MERNO,TCAT_LV4,S_NO,OUTCDNO,OUTCDNO_SECRET,OUTCDNO_BIN,INCDNO,INCDN
O_SECRET,INCDNO_BIN,PAY_STATUS,PAY_RESPN,TRANS_RESPN,DE37,TOTAL_AM,PRO_AM,FREE_AM,SID,ACCETP_ID,PAY_ID,CONTENT_ID,IS_SUCESS,FLAG,BACK1,BACK2,BACK3,BACK4,BACK5,LOAD_DATE
6 from EDW.dw_fact_trans_new2014 t1
7 where DATA_DATE >= TO_DATE(' 2014-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN');
157198197 rows created.
Elapsed: 00:15:31.40
SQL>
SQL>
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.37
alter table trans_new logging;
四、验证
SQL> select /*+ parallel(t,8)*/ count(*) from trans_new t;
COUNT(*)
----------
445600483
