Fast creation of auto-partitioning and long-type like methods for existing tables-oracle 11G

Make a practical use of the previous article. Encountered in the work of a large table (50 million data), at the beginning of the forgotten to create an automatic partition, resulting in the use of the very inconvenient, the speed of the query is very full, so prepare to re-partition table, the original method is to create a new partition table, and then insert the data into the new table sequentially, But our table data comparison of large, if this may lead to relatively low efficiency, after searching for the last article found, this article has three methods, the first is the most primitive method, I did not experiment, the second (swap partition) and the third (on-line redefinition) I have been tested, a third method, I thought it would be faster, but after testing it took more than 2 hours, not ideal, and then used the second method ( swap partition )!

Here's the SQL in practice:

--prepare a new partition table
--To do is to partition the month
CREATE TABLEfact_bond_profit_new (d_date DATE, Vc_curve_nameVARCHAR2( -), Vc_curve_codeVARCHAR2(Ten), Vc_curve_classifyVARCHAR2( -), F_curve_future Number( +,4), F_standard_term Number( +,4), Vc_standard_term_descVARCHAR2( -), F_profitratio Number( +,4), Vc_sourceVARCHAR2( -), D_updatetime DATE) PARTITION byRANGE (d_date) INTERVAL (Numtoyminterval (1,'MONTH')) (PARTITION P1VALUESLess THAN (To_date ('2013-1-1','YYYY-MM-DD'))    );--Add comments to the tableComment on Tablefact_bond_profit_new is 'bond yield Curve';--Add Comments to the columnsComment on columnfact_bond_profit_new.d_date is 'Business Date'; Comment on columnFact_bond_profit_new.vc_curve_name is 'Curve name'; Comment on columnFact_bond_profit_new.vc_curve_code is 'Curve Code'; Comment on columnfact_bond_profit_new.vc_curve_classify is 'curve Classification, data dictionary: curve_classify'; Comment on columnfact_bond_profit_new.f_curve_future is 'Forward Period'; Comment on columnFact_bond_profit_new.f_standard_term is 'Standard term'; Comment on columnFact_bond_profit_new.vc_standard_term_desc is 'Standard Term Description'; Comment on columnFact_bond_profit_new.f_profitratio is 'Rate of Return'; Comment on columnFact_bond_profit_new.vc_source is 'Data Source'; Comment on columnFact_bond_profit_new.d_updatetime is 'Update Time';--create/recreate IndexesCreate IndexIdx_fact_bond_profit_new1 onfact_bond_profit_new (D_date, Vc_curve_code, Vc_source) tablespace tpa_dw_index;--Grant/revoke Object PrivilegesGRANT SELECT  onFact_bond_profit_new toTpa_query;--Create a temporary table of partition informationCREATE TABLEtmp_dba_tab_partitions (table_nameVARCHAR2( -), Partition_nameVARCHAR2( -), High_value CLOB);--Monthly Data sheet for Fact_bond_profitCREATE TABLEfact_bond_profit1 (d_date DATE);--Process run time informationCREATE TABLETmp_sysdime (TIME1VARCHAR2( -), TIME2 DATE);--Created on 2017/4/11 by Zw_huangzhiyaDeclare   --Local variables herePartition_nameVARCHAR2( -);--Partition nameMin_date DATE;--minimum date in the original tableMax_date DATE;--maximum date in the original tablev_date DATE; V_date1VARCHAR2( -); Ls_sqlVARCHAR2( -); begin --Test Statements her SELECT MIN(D_date),MAX(d_date) intomin_date,max_date fromFact_bond_profit; V_date:=min_date; --create partitions in new tables for easy Exchange  whileV_date<=max_date LOOPINSERT  intofact_bond_profit_new (d_date)SELECTV_date fromDUAL; COMMIT; V_date:=V_date+ 1; ENDLOOP; --Empty the data you just inserted EXECUTEIMMEDIATE'TRUNCATE TABLE fact_bond_profit_new'; EXECUTEIMMEDIATE'TRUNCATE TABLE tmp_dba_tab_partitions'; --inserting data from partition information into a staging table INSERT  intotmp_dba_tab_partitionsSELECTA.table_name, A.partition_name, To_lob (a.high_value) High_value fromdba_tab_partitions AWHEREA.table_name= 'fact_bond_profit_new'     andA.table_owner= 'Tpa_dw'; COMMIT; --loop to swap each partition  forAainch(SELECT *  fromtmp_dba_tab_partitions) LOOP--get the partition name for each month   SELECTTo_char (SUBSTR (AA. High_value, One,Ten) ), AA. Partition_name intoV_date1,partition_name fromDUAL; --create a corresponding partition of the data table, in fact, the swap partition, the table has no data, clear the table is a habit   EXECUTEIMMEDIATE'TRUNCATE TABLE fact_bond_profit1'; EXECUTEIMMEDIATE'DROP TABLE fact_bond_profit1 PURGE'; --Create monthly information tables, and generally create tables faster than inserted into tablesLs_sql:= 'CREATE TABLE fact_bond_profit1 nologging as SELECT * from Fact_bond_profit WHERE d_date between Add_months (To_date ( /c0>" "||V_date1||" ","'YYYY-MM-DD"'), -1) and To_date (" "||V_date1||" ","'YYYY-MM-DD"')-1'; EXECUTEIMMEDIATE Ls_sql; --to swap partitions   EXECUTEIMMEDIATE'ALTER TABLE fact_bond_profit_new EXCHANGE PARTITION'||Partition_name||'With TABLE fact_bond_profit1'; --Generate log   INSERT  intoTmp_sysdimeSELECTV_date1,sysdate fromDUAL; COMMIT; ENDLOOP; EXECUTEIMMEDIATE'DROP TABLE tmp_dba_tab_partitions'; EXECUTEIMMEDIATE'DROP TABLE fact_bond_profit1'; exception whenOthers ThenDbms_output. Put_Line (SQLERRM); End;--Log--DROP TABLE tmp_sysdime;--SELECT * from Tmp_sysdime;--new table for Fact_bond_profit--SELECT * from Fact_bond_profit_new;

The long type of data in Oracle is not a like operation, we can convert long data to CLOB type before doing like.

Fast creation of auto-partitioning and long-type like methods for existing tables-oracle 11G