Oracle-CLOB 字段导致查询慢的解决方案

2020年07月29日 16:45 · 阅读(2378) ·

开发环境

名称 版本
操作系统 Windows 10 X64
Oracle win64_11gR2_database
PLSQL Developer 11.0.4.1788（64 bit）01.179332 - Unlimited user license

问题描述

系统里面 PATENT_HISTORY 表的导出功能超过半小时都无法导出，导致系统报错。

问题分析

PATENT_HISTORY 表结构如下：

  1. create table PATENT_HISTORY
  2. (
  3.   case_id                   VARCHAR2(255) not null,
  4.   tencent_code              VARCHAR2(600),
  5.   invent_name               VARCHAR2(3000),
  6.   create_time               DATE,
  7.   creator_full_name         VARCHAR2(255),
  8.   apply_code                VARCHAR2(600),
  9.   apply_date                DATE,
  10.   first_inventor            VARCHAR2(510),
  11.   other_inventor            VARCHAR2(1000),
  12.   costcenter_code           VARCHAR2(255),
  13.   costcenter_name           VARCHAR2(255),
  14.   pact_company              VARCHAR2(255),
  15.   applicantor               VARCHAR2(600),
  16.   dept_id                   VARCHAR2(255),
  17.   org_name                  VARCHAR2(600),
  18.   proclaim_date             DATE,
  19.   proclaim_no               VARCHAR2(600),
  20.   law_state_name            VARCHAR2(255),
  21.   patent_type_name          VARCHAR2(255),
  22.   apply_country_chname      VARCHAR2(600),
  23.   is_university_cooperation VARCHAR2(255),
  24.   related_product           VARCHAR2(600),
  25.   keyword                   VARCHAR2(1500),
  26.   transaction_type_name     VARCHAR2(600),
  27.   sync_date                 DATE,
  28.   balancing_seg             VARCHAR2(255),
  29.   ou_name                   VARCHAR2(255),
  30.   current_step_name         CLOB,
  31.   abstract                  CLOB,
  32.   inventor                  CLOB,
  33.   first_tag_name            CLOB,
  34.   second_tag_name           CLOB,
  35.   third_tag_name            CLOB
  36. )

猜测是 PATENT_HISTORY 中的 CLOB 导致的。

问题解决

1.备份 PATENT_HISTORY 表

  1. alter table PATENT_HISTORY rename to PATENT_HISTORY20200728;

2.创建新的表

CLOB 字段修改为 varchar2(4000)，注意数据丢失问题

  1. create table PATENT_HISTORY
  2. (
  3.   case_id                   VARCHAR2(255) not null,
  4.   tencent_code              VARCHAR2(600),
  5.   invent_name               VARCHAR2(3000),
  6.   create_time               DATE,
  7.   creator_full_name         VARCHAR2(255),
  8.   apply_code                VARCHAR2(600),
  9.   apply_date                DATE,
  10.   first_inventor            VARCHAR2(510),
  11.   other_inventor            VARCHAR2(1000),
  12.   costcenter_code           VARCHAR2(255),
  13.   costcenter_name           VARCHAR2(255),
  14.   pact_company              VARCHAR2(255),
  15.   applicantor               VARCHAR2(600),
  16.   dept_id                   VARCHAR2(255),
  17.   org_name                  VARCHAR2(600),
  18.   proclaim_date             DATE,
  19.   proclaim_no               VARCHAR2(600),
  20.   law_state_name            VARCHAR2(255),
  21.   patent_type_name          VARCHAR2(255),
  22.   apply_country_chname      VARCHAR2(600),
  23.   is_university_cooperation VARCHAR2(255),
  24.   related_product           VARCHAR2(600),
  25.   keyword                   VARCHAR2(1500),
  26.   transaction_type_name     VARCHAR2(600),
  27.   sync_date                 DATE,
  28.   balancing_seg             VARCHAR2(255),
  29.   ou_name                   VARCHAR2(255),
  30.   current_step_name         VARCHAR2(4000),
  31.   abstract                  VARCHAR2(4000),
  32.   inventor                  VARCHAR2(4000),
  33.   first_tag_name            VARCHAR2(4000),
  34.   second_tag_name           VARCHAR2(4000),
  35.   third_tag_name            VARCHAR2(4000)
  36. );

3.同步 PATENT_HISTORY20200728 表数据到 PATENT_HISTORY

  1. -- =============================================
  2. -- Author:      v_hwhao
  3. -- Create date: 2020年7月29日10:15:25
  4. -- Description: 同步 TPS_OST_PATENT20200728 表数据到 TPS_OST_PATENT
  5. -- =============================================
  6. insert /*+APPEND*/ into PATENT_HISTORY
  7. (
  8.   case_id
  9.   ,tencent_code              
  10.   ,invent_name               
  11.   ,create_time               
  12.   ,creator_full_name         
  13.   ,apply_code                
  14.   ,apply_date                
  15.   ,first_inventor            
  16.   ,other_inventor            
  17.   ,costcenter_code           
  18.   ,costcenter_name           
  19.   ,pact_company              
  20.   ,applicantor               
  21.   ,dept_id                   
  22.   ,org_name                  
  23.   ,proclaim_date             
  24.   ,proclaim_no               
  25.   ,law_state_name            
  26.   ,patent_type_name          
  27.   ,apply_country_chname      
  28.   ,is_university_cooperation 
  29.   ,related_product           
  30.   ,keyword                   
  31.   ,transaction_type_name     
  32.   ,sync_date                 
  33.   ,balancing_seg             
  34.   ,ou_name                   
  35.   ,current_step_name         
  36.   ,abstract                  
  37.   ,inventor                  
  38.   ,first_tag_name            
  39.   ,second_tag_name           
  40.   ,third_tag_name            
  41. )
  42. select 
  43.   case_id
  44.   ,tencent_code              
  45.   ,invent_name               
  46.   ,create_time               
  47.   ,creator_full_name         
  48.   ,apply_code                
  49.   ,apply_date                
  50.   ,first_inventor            
  51.   ,other_inventor            
  52.   ,costcenter_code           
  53.   ,costcenter_name           
  54.   ,pact_company              
  55.   ,applicantor               
  56.   ,dept_id                   
  57.   ,org_name                  
  58.   ,proclaim_date             
  59.   ,proclaim_no               
  60.   ,law_state_name            
  61.   ,patent_type_name          
  62.   ,apply_country_chname      
  63.   ,is_university_cooperation 
  64.   ,related_product           
  65.   ,keyword                   
  66.   ,transaction_type_name     
  67.   ,sync_date                 
  68.   ,balancing_seg             
  69.   ,ou_name                   
  70.   ,current_step_name         
  71.   ,abstract                  
  72.   ,inventor                  
  73.   ,first_tag_name            
  74.   ,second_tag_name           
  75.   ,third_tag_name    
  76. from PATENT_HISTORY20200728;
  77. commit;

测试结果

这里的数据量是 32900 条

1.使用 PATENT_HISTORY 表导出数据

导出时间 17:52-17:55

2.使用 PATENT_HISTORY20200728 导出数据

导出时间 10:08-10:58