为已有表快速创建自动分区和Long类型like 的方法-Oracle 11G

时间：2024-12-09 15:06:56

对上一篇文章进行实际的运用。在工作中遇到有一张大表（五千万条数据），在开始的时候忘记了创建自动分区，导致现在使用非常不方便，查询的速度非常的满，所以就准备重新的分区表，最原始方法是先创建新的分区表，然后将数据依次插入到新的表中，但是我们的表的数据比较的大，如果这样做可能导致效率相对较低，经过寻扎发现了上一篇文章，这篇文章有三个方法，第一个就是最原始的方法，我没有进行实验，第二种（交换分区）和第三种的（在线重定义）我都进行了测试，第三种方法，我初以为会比较快速，但是经过测试需要超过2个小时的时间，不是很理想，于是使用了第二种方法（交换分区）！大概是20分钟，还是比较理想的。

以下是实践中的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的操作。

相关文章