分区表学习之一 ---非分区表转为分区表实验-优快云博客

1、创建结构相同的分区表
CREATE TABLE POSTXNJNLHIS_PART
( "LOGNO" CHAR(14),
"LOGDAT" CHAR(8),
"TTXNCD" CHAR(10),
"TXNTYP" CHAR(1),
"TXNCD" CHAR(10),
"SYSDAT" CHAR(8),
"TXNTM" CHAR(6),
"TRMTYP" CHAR(1),
"TXNSTS" CHAR(1),
"PROCOD" CHAR(6),
"CSEQNO" CHAR(12),
"MERCID" CHAR(15),
"TERMID" CHAR(12),
"BATNO" CHAR(6),
"AGTORG" CHAR(13),
"RTRSVR" CHAR(8),
"RTRCOD" CHAR(10),
"TTXNDT" CHAR(12),
"TTXNTM" CHAR(6),
"TSEQNO" CHAR(20),
"THDCHK" CHAR(1),
"TMERCID" CHAR(15),
"TTERMID" CHAR(14),
"TBATNO" CHAR(6),
"AUTCOD" CHAR(6),
"TOPRID" CHAR(6),
"CPSCOD" CHAR(4),
"TSREFNO" CHAR(12),
"ACTNO" CHAR(21),
"CRDNO" CHAR(30),
"CRDSQN" CHAR(3),
"CRDFLG" CHAR(2),
"EXPDAT" CHAR(4),
"ISSINO" CHAR(11),
"HISINO" CHAR(11),
"CRDNO1" CHAR(30),
"TXNAMT" CHAR(15),
"REFAMT" CHAR(15),
"FEE" CHAR(15),
"ORGNO" CHAR(6),
"CCYCOD" CHAR(3),
"INMOD" CHAR(3),
"IDNO" CHAR(18),
"ACQCOD" CHAR(11),
"FORCOD" CHAR(11),
"ACTTYP" CHAR(2),
"OLOGNO" CHAR(14),
"MERTYP" CHAR(4),
"INSADR" CHAR(40),
"POSCND" CHAR(2),
"RSVDAT" CHAR(30),
"ORNDAT" CHAR(42),
"OPROCOD" CHAR(6),
"OTXNDT" CHAR(4),
"OTXNTM" CHAR(6),
"OSREFNO" CHAR(12),
"OBATNO" CHAR(6),
"EMVFLG" CHAR(1),
"ICDAT" CHAR(510),
"ICLEN" CHAR(3),
"HACQCD" CHAR(11),
"HFORCD" CHAR(11),
"CPSRSV" CHAR(100),
"ACQRSV" CHAR(100),
"ENDFLG" CHAR(1),
"TMSTIM" CHAR(10),
"HSTDAT" CHAR(8),
"CPSDAT" CHAR(8),
"TXNRSV1" VARCHAR2(30 CHAR),
"TXNRSV2" CHAR(20),
"TXNRSV3" CHAR(20),
"OCSEQNO" CHAR(6),
"ROTFLG" CHAR(1),
"BRAID" CHAR(8),
"RISKFLG" CHAR(1) DEFAULT '0',
"COSTFEE" CHAR(15) DEFAULT '000000000000000',
"CHECKX" VARCHAR2(12 CHAR),
"CHECKY" VARCHAR2(12 CHAR),
"TXNPROVINCE" VARCHAR2(20 CHAR),
"TXNCITY" VARCHAR2(20 CHAR),
"TXNADDRESS" VARCHAR2(80 CHAR),
"UPLMT" NUMBER(10,2),
"MERFEE" NUMBER(10,2),
"IDFID" VARCHAR2(3),
"BASEFEEAMT" CHAR(12),
"SREFNO" CHAR(16 CHAR),
"BASEFEEAMTY" VARCHAR2(12)
)
PARTITION BY RANGE(LOGDAT)
(
PARTITION POSTXNJNLHIS_PMAX VALUES LESS THAN(MAXVALUE)
) TABLESPACE TSPOSMMCH

2、交换非分区表的数据到分区表中(130万的数据交换使用30秒)

SQL> set timing on
SQL> ALTER TABLE POSTXNJNLHIS_PART EXCHANGE PARTITION POSTXNJNLHIS_PMAX WITH TABLE POSTXNJNLHIS;

Table altered.

Elapsed: 00:00:30.17

--原来的非分区表数据没了
SQL> SELECT logdat,count(*) FROM POSTXNJNLHIS GROUP BY logdat;

no rows selected

Elapsed: 00:00:00.00

--交换到分区表了
SQL> SELECT substr(logdat,1,6),count(*) FROM POSTXNJNLHIS_PART GROUP BY substr(logdat,1,6) order by substr(logdat,1,6);

SUBSTR(LOGDAT,1,6) COUNT(*)
------------------------------------ ----------
201405 271
201406 1578
201407 7358
201408 30506
201409 73646
201410 94612
201411 106825
201412 159051
201501 151775
201502 127947
201503 179191

SUBSTR(LOGDAT,1,6) COUNT(*)
------------------------------------ ----------
201504 154729
201505 143352
201506 68757

14 rows selected.

Elapsed: 00:00:02.68

3、对POSTXNJNLHIS_PMAX进行拆分为14个小的分区（每月一个分区） split分区过程中，数据量较大，会产生大量的redo日志。

--201405月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20140601') into (partition POSTXNJNLHIS_P201405, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:49.93

--201406月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20140701') into (partition POSTXNJNLHIS_P201406, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:44.69
--201407月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20140801') into (partition POSTXNJNLHIS_P201407, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:37.40
--201408月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20140901') into (partition POSTXNJNLHIS_P201408, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:46.18

--201409月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20141001') into (partition POSTXNJNLHIS_P201409, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:45.92

--201410月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20141101') into (partition POSTXNJNLHIS_P201410, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:42.55
--201411月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20141201') into (partition POSTXNJNLHIS_P201411, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:32.84

--201412月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150101') into (partition POSTXNJNLHIS_P201412, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:19.12

--201501月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150201') into (partition POSTXNJNLHIS_P201501, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:16.70

--201502月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150301') into (partition POSTXNJNLHIS_P201502, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:01:00.84

--201503月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150401') into (partition POSTXNJNLHIS_P201503, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:00:47.49

--201504月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150501') into (partition POSTXNJNLHIS_P201504, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:00:36.57

--201505月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150601') into (partition POSTXNJNLHIS_P201505, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:00:25.37

--201506月份的数据
SQL> ALTER TABLE POSTXNJNLHIS_PART SPLIT PARTITION POSTXNJNLHIS_PMAX AT ('20150701') into (partition POSTXNJNLHIS_P201506, partition POSTXNJNLHIS_PMAX);

Table altered.

Elapsed: 00:00:00.70

4、重命名表名：
SQL> select count(*) from POSTXNJNLHIS;

COUNT(*)
----------
0

Elapsed: 00:00:00.00
SQL> alter table POSTXNJNLHIS_PART rename to POSTXNJNLHIS;
alter table POSTXNJNLHIS_PART rename to POSTXNJNLHIS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Elapsed: 00:00:00.05
SQL> drop table POSTXNJNLHIS purge;

Table dropped.

Elapsed: 00:00:00.17
SQL> alter table POSTXNJNLHIS_PART rename to POSTXNJNLHIS;（这个动作很快，不影响应用）

Table altered.

Elapsed: 00:00:00.21
SQL> select count(*) from POSTXNJNLHIS;

COUNT(*)
----------
1299598

Elapsed: 00:00:03.84

5、确定分区
SQL> SELECT partition_name,high_value FROM USER_TAB_PARTITIONS WHERE table_name='POSTXNJNLHIS';

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
POSTXNJNLHIS_P201405 '20140531'
POSTXNJNLHIS_P201406 '20140630'
POSTXNJNLHIS_P201407 '20140731'
POSTXNJNLHIS_P201408 '20140831'
POSTXNJNLHIS_P201409 '20140930'
POSTXNJNLHIS_P201410 '20141031'
POSTXNJNLHIS_P201411 '20141130'
POSTXNJNLHIS_P201412 '20141231'
POSTXNJNLHIS_P201501 '20150131'
POSTXNJNLHIS_P201502 '20150228'
POSTXNJNLHIS_P201503 '20150331'

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
POSTXNJNLHIS_P201504 '20150430'
POSTXNJNLHIS_P201505 '20150531'
POSTXNJNLHIS_P201506 '20150630'
POSTXNJNLHIS_PMAX MAXVALUE

15 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28194062/viewspace-1727722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28194062/viewspace-1727722/