大佬教程收集整理的这篇文章主要介绍了oracle中关于clob类型字段的查询效率问题，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
今天，公司项目某个模块的导出报如下错误：
http Status 500 – Internal Server Error Type Exception Report message Handler dispatch Failed; nested exception is java.lang.outOfMemoryError: GC overhead limit exceeded Description The server encountered an unexpected condition that prevented it from fulfilling the request. Exception org.springframework.web.util.nestedservletexception: Handler dispatch Failed; nested exception is java.lang.outOfMemoryError: GC overhead limit exceeded org.springframework.web.servlet.dispatcherServlet.dodispatch(dispatcherServlet.java:1006) org.springframework.web.servlet.dispatcherServlet.doservice(dispatcherServlet.java:925) org.springframework.web.servlet.FrameworkServlet.processrequest(FrameworkServlet.java:978) org.springframework.web.servlet.FrameworkServlet.dopost(FrameworkServlet.java:881) javax.servlet.http.httpServlet.service(httpServlet.java:661) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855) javax.servlet.http.httpServlet.service(httpServlet.java:742) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.apache.shiro.web.servlet.ProxIEdFilterChain.doFilter(ProxIEdFilterChain.java:61) org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) org.apache.shiro.web.servlet.oncePerrequestFilter.doFilter(OncePerrequestFilter.java:125) org.apache.shiro.web.servlet.ProxIEdFilterChain.doFilter(ProxIEdFilterChain.java:66) org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) org.apache.shiro.subject.support.DelegaTingSubject.execute(DelegaTingSubject.java:387) org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) org.apache.shiro.web.servlet.oncePerrequestFilter.doFilter(OncePerrequestFilter.java:125) org.springframework.web.filter.DelegaTingFilterProxy.invokeDelegate(DelegaTingFilterProxy.java:357) org.springframework.web.filter.DelegaTingFilterProxy.doFilter(DelegaTingFilterProxy.java:270) com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209) com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244) org.springframework.web.filter.CharacterEnCodingFilter.doFilterInternal(CharacterEnCodingFilter.java:200) org.springframework.web.filter.oncePerrequestFilter.doFilter(OncePerrequestFilter.java:107) Root Cause java.lang.outOfMemoryError: GC overhead limit exceeded java.util.Arrays.copyOf(Arrays.java:3332) java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124) java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448) java.lang.StringBuilder.append(StringBuilder.java:136) org.apache.ibatis.executor.resultset.ResultSetWrapper.getMapKey(ResultSetWrapper.java:176) org.apache.ibatis.executor.resultset.ResultSetWrapper.getMappedcolumnnames(ResultSetWrapper.java:158) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertymapPings(DefaultResultSetHandler.java:428) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:916) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applynestedResultMapPings(DefaultResultSetHandler.java:963) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:918) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesFornestedResultMap(DefaultResultSetHandler.java:881) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:303) org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:196) org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) org.apache.ibatis.executor.statement.RoutIngStatementHandler.query(RoutIngStatementHandler.java:79) sun.reflect.GeneratedMethodAccessor370.invoke(UnkNown sourcE) sun.reflect.DelegaTingMethodAccessorImpl.invoke(DelegaTingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) com.sun.proxy.$Proxy1141.query(UnkNown sourcE) org.apache.ibatis.executor.SimpleExecutor.doquery(SimpleExecutor.java:63) org.apache.ibatis.executor.baseExecutor.queryFromDatabase(BaseExecutor.java:326) org.apache.ibatis.executor.baseExecutor.query(BaseExecutor.java:156) org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) sun.reflect.GeneratedMethodAccessor369.invoke(UnkNown sourcE) sun.reflect.DelegaTingMethodAccessorImpl.invoke(DelegaTingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) com.sun.proxy.$Proxy1140.query(UnkNown sourcE) org.apache.ibatis.session.defaults.DefaultsqlSession.SELEctList(DefaultsqlSession.java:148) org.springframework.remoTing.support.RemoteInvocationUtils.fillinClIEntStackTraceIfPossible(RemoteInvocationUtils.java:45) org.springframework.remoTing.support.RemoteInvocationResult.recreate(RemoteInvocationResult.java:156) org.springframework.remoTing.support.RemoteInvocationBasedAccessor.recreateRemoteInvocationResult(RemoteInvocationBasedAccessor.java:85) org.springframework.remoTing.httpinvoker.httpinvokerClIEnTinterceptor.invoke(httpinvokerClIEnTinterceptor.java:162) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) com.sun.proxy.$Proxy765.LisTinvoiceDetail(UnkNown sourcE) com.csw.purchase.controller.InvoicequeryController.exportAll(InvoicequeryController.java:174) com.csw.purchase.controller.InvoicequeryController$$FastClassBySpringcglib$$f1d52ef0.invoke(<generated>) org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) org.springframework.aop.framework.cglibAopProxy$cglibMethodInvocation.invokeJoinpoint(cglibAopProxy.java:747) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) org.apache.shiro.spring.security.interceptor.AopAllianceAnnotationsAuthorizingMethodInterceptor$1.proceed(AopAllianceAnnotationsAuthorizingMethodInterceptor.java:82) org.apache.shiro.authz.aop.AuthorizingMethodInterceptor.invoke(AuthorizingMethodInterceptor.java:39) org.apache.shiro.spring.security.interceptor.AopAllianceAnnotationsAuthorizingMethodInterceptor.invoke(AopAllianceAnnotationsAuthorizingMethodInterceptor.java:115) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) org.springframework.aop.framework.cglibAopProxy$DynamicAdvisedInterceptor.intercept(cglibAopProxy.java:689) com.csw.purchase.controller.InvoicequeryController$$EnhancerBySpringcglib$$b3090726.exportAll(<generated>) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegaTingMethodAccessorImpl.invoke(DelegaTingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.web.method.support.invocableHandlerMethod.doInvoke(invocableHandlerMethod.java:209) org.springframework.web.method.support.invocableHandlerMethod.invokeForrequest(invocableHandlerMethod.java:136) org.springframework.web.servlet.mvc.method.Annotation.ServleTinvocableHandlerMethod.invokeAndHandle(ServleTinvocableHandlerMethod.java:102) org.springframework.web.servlet.mvc.method.Annotation.requestMapPingHandlerAdapter.invokeHandlerMethod(requestMapPingHandlerAdapter.java:870) org.springframework.web.servlet.mvc.method.Annotation.requestMapPingHandlerAdapter.handleInternal(requestMapPingHandlerAdapter.java:776) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) org.springframework.web.servlet.dispatcherServlet.dodispatch(dispatcherServlet.java:991) org.springframework.web.servlet.dispatcherServlet.doservice(dispatcherServlet.java:925) org.springframework.web.servlet.FrameworkServlet.processrequest(FrameworkServlet.java:978) org.springframework.web.servlet.FrameworkServlet.dopost(FrameworkServlet.java:881) javax.servlet.http.httpServlet.service(httpServlet.java:661) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855) javax.servlet.http.httpServlet.service(httpServlet.java:742) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.apache.shiro.web.servlet.ProxIEdFilterChain.doFilter(ProxIEdFilterChain.java:61) org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) org.apache.shiro.web.servlet.oncePerrequestFilter.doFilter(OncePerrequestFilter.java:125) org.apache.shiro.web.servlet.ProxIEdFilterChain.doFilter(ProxIEdFilterChain.java:66) org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) org.apache.shiro.subject.support.DelegaTingSubject.execute(DelegaTingSubject.java:387) org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) org.apache.shiro.web.servlet.oncePerrequestFilter.doFilter(OncePerrequestFilter.java:125) org.springframework.web.filter.DelegaTingFilterProxy.invokeDelegate(DelegaTingFilterProxy.java:357) org.springframework.web.filter.DelegaTingFilterProxy.doFilter(DelegaTingFilterProxy.java:270) com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209) com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244) org.springframework.web.filter.CharacterEnCodingFilter.doFilterInternal(CharacterEnCodingFilter.java:200) org.springframework.web.filter.oncePerrequestFilter.doFilter(OncePerrequestFilter.java:107) Note The full stack trace of the root cause is available in the server logs. Apache tomcat/8.5.24
内存溢出，导出前需要查询的sql如下：
SELECT pID.ID,pID.materials_ID,pID.invoice_ID,pID.inform_order_detail_ID,pID.unit_price,pID.invoice_amt,pID.REMARK,pID.biz_status,pID.version,pID.is_deleted,pID.create_user,pID.gmt_create,pID.create_user_iD,pID.modifIEd_user,pID.gmt_modifIEd,pID.materials_release_ID,m.ID m_ID,m.item_ID m_item_ID,m.code m_code,m.unit m_unit,m.name m_name,m.short_code m_short_code,m.priMary_uom m_priMary_uom,m.key_component_flag m_key_component_flag,m.materials_type m_materials_type,m.classification m_classification,m.is_examine m_is_examine,m.item_status m_item_status,m.is_examine_item m_is_examine_item,m.purchase_type m_purchase_type,m.gmt_modifIEd m_gmt_modifIEd,m.modifIEd_user m_modifIEd_user,iiod.ID iiod_ID,iiod.invoice_inform_order_ID iiod_invoice_inform_order_ID,iiod.materials_ID iiod_materials_ID,iiod.invoice_qty iiod_invoice_qty,iiod.unit_price iiod_unit_price,iiod.dispense_num iiod_dispense_num,iiod.package_deal_code iiod_package_deal_code,iiod.current_differ_price iiod_current_differ_price,i.ID i_iD,i.code i_code,i.supplier_ID i_supplier_ID,i.invoice_val i_invoice_val,i.old_code i_old_code,i.invoice_tax_val i_invoice_tax_val,i.tax_amount i_tax_amount,i.vmi_type i_vmi_type,i.invoice_abstract i_invoice_abstract,i.general_ledger_date i_general_ledger_date,i.input_time i_input_time,i.is_hand_dispose i_is_hand_dispose,i.invoice_status i_invoice_status,i.organization_ID i_organization_ID,i.invoice_type i_invoice_type,s.ID s_ID,s.name s_name,s.code s_code,s.short_code s_short_code,s.TYPE s_type,s.main_product s_main_product,s.tax_reg_num s_tax_reg_num,iio.ID iio_ID,iio.code iio_code,iio.END_DATE iio_END_DATE,iio.department_id iio_department_id,iio.supplier_ID iio_supplier_ID,iio.drawer iio_drawer,iio.vmi_type iio_vmi_type,iio.invoice_audit_status iio_invoice_audit_status,iio.organization_ID iio_organization_ID,iio.inform_order_type iio_inform_order_type,it.ID it_ID,it.invoice_ID it_invoice_ID,it.tax_rate it_tax_rate,it.tax_amount it_tax_amount FROM p_invoice_detail pID left JOIN s_materials m ON pID.materials_ID = m.ID left JOIN p_invoice_inform_order_detail iiod ON pID.inform_order_detail_ID = iiod.ID left JOIN p_invoice_inform_order iio ON iiod.invoice_inform_order_ID = iio.ID left JOIN p_invoice i ON pID.invoice_ID = i.ID left JOIN p_invoice_tax it ON i.ID = it.invoice_ID left JOIN s_supplier s ON s.ID = i.supplier_ID WHERE i.is_deleted = 0
其中表p_invoice_detail 中大概34W条数据，p_invoice_inform_order_detail中大概14W条数据，其它表中数据不超1W条，于是博主各种折腾，建索引，调整连接顺序等，都不起作用，博主用的navicat，每次查询我的naviicat就罢工，报unkNown internal error (A70529121901) bad alLOCATIOn，没办法，只好一个表一个表的排除，最后发现，左连接p_invoice_inform_order_detail 表后就会出现上述问题，其它表都没事，于是博主单独查询这张表发现也很慢：
用了37.704s只查询出8300条记录（表中共有约14W条数据），但是同样的查询有34W条数据的表p_invoice_detail却用了12.640s就全部查询出来了，博主就奇怪了，于是一个字段一个字段的试，终于发现是表p_invoice_inform_order_detail中的dispose_num字段导致的，后来发现该字段为clob类型，问题定位就好说了，上网查询发现将clob字段转为String类型再查询出来会提高效率，于是照做：将最开始的iiod.dispense_num修改为dbms_lob.substr(iiod.dispense_num,DBMS_lob.getlength(iiod.dispense_num),1) dispense_num：
SELECT pID.ID, dbms_lob.substr(iiod.dispense_num,1) dispense_num, iiod.package_deal_code iiod_package_deal_code,it.tax_amount it_tax_amount FROM p_invoice_detail pID left JOIN s_materials m ON pID.materials_ID = m.ID left JOIN p_invoice_inform_order_detail iiod ON pID.inform_order_detail_ID = iiod.ID left JOIN p_invoice_inform_order iio ON iiod.invoice_inform_order_ID = iio.ID left JOIN p_invoice i ON pID.invoice_ID = i.ID left JOIN p_invoice_tax it ON i.ID = it.invoice_ID left JOIN s_supplier s ON s.ID = i.supplier_ID WHERE i.is_deleted = 0
修改后查询，发现还是很慢，于是将其它不需要的字段去掉，调整为：
SELECT i.ID,i.gmt_create,i.code,i.invoice_tax_val,m.code materials_code,m.name materials_name,s.code supplier_code,s.name supplier_name,it.tax_rate,i.tax_amount,iiod.current_differ_price,i.invoice_val,pID.unit_price * pID.invoice_amt @R_228_10586@l_money,dbms_lob.substr(iiod.dispense_num,1) dispense_num,decode(i.invoice_type,‘1‘,‘供应商平台‘,‘2‘,‘发票平台‘) FROM p_invoice_detail pID left JOIN p_invoice i ON pID.invoice_ID = i.ID left JOIN s_materials m ON pID.materials_ID = m.ID left JOIN p_invoice_inform_order_detail iiod ON pID.inform_order_detail_ID = iiod.ID left JOIN p_invoice_inform_order iio ON iiod.invoice_inform_order_ID = iio.ID left JOIN p_invoice_tax it ON i.ID = it.invoice_ID left JOIN s_supplier s ON s.ID = i.supplier_ID WHERE i.is_deleted = 0
这次总算不报错了，虽然查询单速度还是有点慢，但还能接受：
以上是大佬教程为你收集整理的oracle中关于clob类型字段的查询效率问题全部内容，希望文章能够帮你解决oracle中关于clob类型字段的查询效率问题所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。