分区表学习之一 ---非分区表转为分区表实验

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/