发布于2021-05-29 23:33 阅读(552) 评论(0) 点赞(19) 收藏(5)
生产上遇到了一个问题,有一段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;
创建的存储过程名为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客户端执行存储过程
方式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;
}
}
原文链接:https://blog.csdn.net/jike11231/article/details/117370625
作者:想要飞翔的天使
链接:http://www.javaheidong.com/blog/article/207834/640cf5256f53202f973d/
来源:java黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 java黑洞网 All Rights Reserved 版权所有,并保留所有权利。京ICP备18063182号-2
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!