為已有錶快速建立自動分區和Long類型like 的方法-Oracle 11G

標籤：arch   ace   收益率   class   insert   htm   range   html   des   

        對上一篇文章進行實際的運用。在工作中遇到有一張大表（五千萬條資料），在開始的時候忘記了建立自動分區，導致現在使用非常不方便，查詢的速度非常的滿，所以就準備重新的分區表，最原始方法是先建立新的分區表，然後將資料依次插入到新的表中，但是我們的表的資料比較的大，如果這樣做可能導致效率相對較低，經過尋紮發現了上一篇文章，這篇文章有三個方法，第一個就是最原始的方法，我沒有進行實驗，第二種（交換分區）和第三種的（線上重定義）我都進行了測試，第三種方法，我初以為會比較快速，但是經過測試需要超過2個小時的時間，不是很理想，於是使用了第二種方法（交換分區）！

      以下是實踐中的SQL：

--準備新的分區表
--做的是安月進行分區
CREATE TABLE FACT_BOND_PROFIT_NEW (  D_DATE                DATE,  VC_CURVE_NAME         VARCHAR2(100),  VC_CURVE_CODE         VARCHAR2(10),  VC_CURVE_CLASSIFY     VARCHAR2(20),  F_CURVE_FUTURE        NUMBER(19,4),  F_STANDARD_TERM       NUMBER(19,4),  VC_STANDARD_TERM_DESC VARCHAR2(20),  F_PROFITRATIO         NUMBER(19,4),  VC_SOURCE             VARCHAR2(20),  D_UPDATETIME          DATE)PARTITION BY RANGE (D_DATE)   INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH‘))     (     PARTITION P1 VALUES LESS THAN (TO_DATE(‘2013-1-1‘, ‘YYYY-MM-DD‘))    );-- Add comments to the table comment on table FACT_BOND_PROFIT_NEW  is ‘債券收益率曲線‘;-- Add comments to the columns comment on column FACT_BOND_PROFIT_NEW.d_date  is ‘業務日期‘;comment on column FACT_BOND_PROFIT_NEW.vc_curve_name  is ‘曲線名稱‘;comment on column FACT_BOND_PROFIT_NEW.vc_curve_code  is ‘曲線代碼‘;comment on column FACT_BOND_PROFIT_NEW.vc_curve_classify  is ‘曲線分類，資料字典：CURVE_CLASSIFY‘;comment on column FACT_BOND_PROFIT_NEW.f_curve_future  is ‘遠期期限‘;comment on column FACT_BOND_PROFIT_NEW.f_standard_term  is ‘標準期限‘;comment on column FACT_BOND_PROFIT_NEW.vc_standard_term_desc  is ‘標準期限描述‘;comment on column FACT_BOND_PROFIT_NEW.f_profitratio  is ‘收益率‘;comment on column FACT_BOND_PROFIT_NEW.vc_source  is ‘資料來源‘;comment on column FACT_BOND_PROFIT_NEW.d_updatetime  is ‘更新時間‘;-- Create/Recreate indexes create index IDX_FACT_BOND_PROFIT_NEW1 on FACT_BOND_PROFIT_NEW (D_DATE, VC_CURVE_CODE, VC_SOURCE)  tablespace TPA_DW_INDEX;-- Grant/Revoke object privileges GRANT SELECT ON FACT_BOND_PROFIT_NEW TO TPA_QUERY;--建立分區資訊的暫存資料表CREATE TABLE TMP_DBA_TAB_PARTITIONS(  TABLE_NAME     VARCHAR2(30),  PARTITION_NAME VARCHAR2(30),  HIGH_VALUE     CLOB);--FACT_BOND_PROFIT的各月資料表CREATE TABLE FACT_BOND_PROFIT1 (D_DATE DATE); --過程已耗用時間資訊CREATE TABLE TMP_SYSDIME(  TIME1 VARCHAR2(30),  TIME2 DATE);-- Created on 2017/4/11 by ZW_HUANGZHIYA declare   -- Local variables here  PARTITION_NAME VARCHAR2(30); --分區名稱  MIN_DATE DATE; --原表中的最小日期  MAX_DATE DATE; --原表中的最大日期  V_DATE  DATE;  V_DATE1 VARCHAR2(20);  LS_SQL VARCHAR2(2000); begin -- Test statements her SELECT MIN(D_DATE),MAX(D_DATE)   INTO MIN_DATE,MAX_DATE   FROM FACT_BOND_PROFIT;     V_DATE := MIN_DATE;  --在新表中產生分區，方便交換 WHILE V_DATE <= MAX_DATE LOOP   INSERT INTO FACT_BOND_PROFIT_NEW (D_DATE)   SELECT V_DATE FROM DUAL;    COMMIT;   V_DATE := V_DATE + 1; END LOOP;   --清空剛剛插入的資料 EXECUTE IMMEDIATE ‘TRUNCATE TABLE FACT_BOND_PROFIT_NEW‘; EXECUTE IMMEDIATE ‘TRUNCATE TABLE TMP_DBA_TAB_PARTITIONS‘;  --將分區資訊的資料插入暫存資料表 INSERT INTO TMP_DBA_TAB_PARTITIONS  SELECT A.TABLE_NAME, A.PARTITION_NAME, TO_LOB(A.HIGH_VALUE) HIGH_VALUE   FROM DBA_TAB_PARTITIONS A  WHERE A.TABLE_NAME = ‘FACT_BOND_PROFIT_NEW‘    AND A.TABLE_OWNER = ‘TPA_DW‘; COMMIT;  --迴圈對各個分區進行交換 FOR AA IN (SELECT * FROM TMP_DBA_TAB_PARTITIONS) LOOP      --擷取各個月對應的分區名稱   SELECT TO_CHAR(SUBSTR(AA.HIGH_VALUE,11,10)),AA.PARTITION_NAME     INTO V_DATE1,PARTITION_NAME     FROM DUAL;      --建立對應符合分區的資料表，其實進行了交換分區，表中就沒有了資料，清空表是習慣   EXECUTE IMMEDIATE ‘TRUNCATE TABLE FACT_BOND_PROFIT1‘;   EXECUTE IMMEDIATE ‘DROP TABLE FACT_BOND_PROFIT1 PURGE‘;       --建立各月資訊表，一般創表的速度要高於向表中插入的速度   LS_SQL := ‘CREATE TABLE FACT_BOND_PROFIT1 NOLOGGING AS   SELECT * FROM FACT_BOND_PROFIT WHERE D_DATE BETWEEN ADD_MONTHS(TO_DATE(‘‘‘||V_DATE1||‘‘‘,‘‘YYYY-MM-DD‘‘),-1) AND TO_DATE(‘‘‘||V_DATE1||‘‘‘,‘‘YYYY-MM-DD‘‘) - 1‘;   EXECUTE IMMEDIATE LS_SQL;      --進行交換分區   EXECUTE IMMEDIATE ‘ALTER TABLE FACT_BOND_PROFIT_NEW EXCHANGE PARTITION ‘||PARTITION_NAME||‘ WITH TABLE FACT_BOND_PROFIT1‘;      --組建記錄檔   INSERT INTO TMP_SYSDIME    SELECT V_DATE1,SYSDATE FROM DUAL;    COMMIT; END LOOP;  EXECUTE IMMEDIATE ‘DROP TABLE TMP_DBA_TAB_PARTITIONS‘; EXECUTE IMMEDIATE ‘DROP TABLE FACT_BOND_PROFIT1‘;exception when others then   DBMS_OUTPUT.PUT_LINE(SQLERRM); end;--日誌--DROP TABLE TMP_SYSDIME;--SELECT * FROM TMP_SYSDIME;--FACT_BOND_PROFIT對應的新表--SELECT * FROM FACT_BOND_PROFIT_NEW;

Oracle中的Long類型的資料是不能進行like操作的，我們可以將long類型的資料，先轉換為clob類型，然後再進行like的操作。

為已有錶快速建立自動分區和Long類型like 的方法-Oracle 11G