普通表转换成分区表

首页 > 代码库 > 普通表转换成分区表

2024-07-30 12:17:30 220人阅读

描述:将表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

普通表转换成分区表

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 普通表转换成分区表

普通表转换成分区表

看完仍有疑问？有类似问题直接问程序猿