生产中使用Oracle存储过程解决数据库查询缓慢问题

末蓝、 2022-10-16 08:50 353阅读 0赞

生产上遇到了一个问题，有一段SQL语句需要查询信息，超级缓慢，语句如下：

SELECT extractvalue(d.document_data, '/root/deliveryNumber') AS deliveryNumber
    FROM dat_document d
    WHERE d.form_name     = 'SD04'
    AND (document_status != 'deleted'
    OR document_status   IS NULL )
    AND extractvalue(d.document_data, '/root/deliveryNumber')    = '82844161';

**由于Where后使用了oracle函数extractvalue破坏了索引查询，导致查询是全表扫描！**，但是生产上dat\_document 表数据量太大，而且给dat\_document 增加extractvalue函数索引，风险性极高，特意采用了**存储过程**进行解决！通过定时或者手动执行存储过程，将从dat\_document 查询替换成从中间表aac\_sd04\_tab 查询，查询效率提升飞速！

# 一、创建存储表和存储过程 #

表aac\_sd04\_tab

create table aac_sd04_tab (
        docid    varchar2(500),
        creator    varchar2(500),
        deliveryNumber varchar2(500),
        ordernum    varchar2(500)
      );
      --drop table aac_sd04_tab_son;
      --drop index aac_sd04tabindex;
         
      CREATE INDEX aac_sd04tabindex
      ON aac_sd04_tab (docid, deliverynumber);

存储过程savesd04，根据docid保存数据

PROCEDURE savesd04( docid IN VARCHAR2)
      AS
      BEGIN
      DELETE FROM aac_sd04_tab WHERE docid = docid;
      COMMIT;
      INSERT
      INTO aac_sd04_tab tab
        (
          tab.docid ,
          tab.creator ,
          tab.deliveryNumber,
          tab.ordernum 
        )
      SELECT 
        t.document_id,
        t.creator                                                              AS creator,
        extractvalue(t.document_data, '//root/deliveryNumber')                       AS deliveryNumber,   
        extractvalue(t.document_data, '//root/orderNum')                       AS ordernum                       
      FROM dat_document t
      WHERE t.document_id = docid
      AND t.form_name     = 'SD04' ;
      COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('saveSD04执行异常 docid=' || docid);
      ROLLBACK;
    END;

存储过程savesd04all，保存所有数据

procedure savesd04all as 
    begin
      dbms_output.put_line('AAC_SD04_TAB insert all');
      insert into aac_sd04_tab tab (
        tab.docid ,
          tab.creator ,
          tab.deliveryNumber,
          tab.ordernum 
      )
    select 
        t.document_id,
        t.creator AS creator,
        extractvalue(t.document_data, '//root/deliveryNumber') AS deliveryNumber,   
        extractvalue(t.document_data, '//root/orderNum') AS ordernum                       
    from
      dat_document t
    where
      t.form_name = 'SD04';
    commit;
    EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('saveSD04执行异常');
                ROLLBACK;
    end;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]

# 二、创建存储过程 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]  
创建的存储过程名为PRC\_SD04\_ALL

create or replace PROCEDURE PRC_SD04_ALL
    (
      docid IN VARCHAR2 
    ) AS 
    BEGIN
     aac_document_pag.savesd04all;
    END PRC_SD04_ALL;

savesd04all代表将所有数据批量写入到aac\_sd04\_tab中

# 三、执行存储过程 #

方式1：通过Oracle客户端执行存储过程  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 2]  
方式2：通过Java后台代码执行

import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Types;
    import org.hibernate.Session;
    import org.hibernate.SessionFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import com.gzsolartech.service.BaseDataService;
    import org.hibernate.engine.spi.SessionFactoryImplementor;
    
    @Service
    public class CreateBusinessService extends BaseDataService{ 
    //sproc存储过程名称
    public boolean executePro(String sproc,String docId){ 
    		Session session = null;
    		Connection conn = null;
    		boolean isSuccess = false;
    		try { 
    			session = gdao.getSession();
    			conn = ((SessionFactoryImplementor)session.getSessionFactory()).getConnectionProvider().getConnection();
    			if(!StringUtils.isEmpty(sproc)){ 
    				CallableStatement call = conn.prepareCall("{Call " + sproc + "(?)}");
    				call.setString(1, docId);//设置输入参数
    				isSuccess = call.execute();
    			}
    		} catch (Exception e) { 
    			
    			LOG.error("------------------CreateBusinessService:"+e.getMessage());
    		}finally{ 
    			if(conn!=null){ 
    				try { 
    					conn.close();
    				} catch (SQLException e) { 
    					e.printStackTrace();
    				}
    			}
    		}
    		return isSuccess;
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]: /images/20221014/23a903b287d040439b09777a32744b83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]: /images/20221014/d1bda8bf6b4546f6abb9872c0da29669.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 2]: /images/20221014/0438b78e1fa041049e4191e886956417.png