oracle中where条件后用in这样子查询后变慢

电玩女神 2022-06-05 02:59 1093阅读 0赞

今日开发一个需求，其中由于需求变态，写了一个挺复杂的sql，在本地测试环境看不出问题，但到了生产就变得非常慢（由于表数据量比较大），把sql粘贴出来执行了一下大概要42秒...我滴天

我写的sql大概是这个样子的（大概描绘下）TABLE\_A表数据量大概200w，其中一些关键字段建了索引

select \* from TABLE\_A a

where a.lng>=\{变量\} and a.lng<=\{变量\}

and a.lat>=\{变量\} and a.lat<=\{变量\}

and a.ci\_code in(

...一个复杂sql,执行条件后大概也就几十条数据

)

就是这样一个sql执行却很慢，如果我单独执行红色或单独执行蓝色部分sql就很快，又或者我把红色部分>=与<=截开查询就很快，但放在一起就相当慢。

后来看到了一篇博文，改造了一下果然就很快了，大概1~2秒就完事了。

改造后

select \* from TABLE\_A a

where a.lng>=\{变量\} and a.lng<=\{变量\}

and a.lat>=\{变量\} and a.lat<=\{变量\}

intersect

select \* from TABLE\_A a

where

a.ci\_code in(

...一个复杂sql,执行条件后大概也就几十条数据

)

然后看了下执行计划，主要原因是由于where条件后><（或者or之类的）并且用到in导致驱动表被错误的选择，驱动表选择了“大表”。

又例：

<table style="word-wrap:break-word; empty-cells:show; border-collapse:collapse; table-layout:fixed; width:1661px; color:rgb(68,68,68); font-family:Tahoma,&quot;Microsoft Yahei&quot;,Simsun; font-size:12px; background-color:rgb(241,241,241)"> 
 <tbody style="word-wrap:break-word"> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; font-size:14px"> or 和子查询连用导致oracle无法查询重写了<br style="word-wrap:break-word"> <br style="word-wrap:break-word"> (t.DSTC_ID = 1919 or<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; t.DSTC_ID in<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; (select b.regoinid<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;from area_info_center@srcdb b<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; where b.parentregoinid = 1919)<br style="word-wrap:break-word"> <br style="word-wrap:break-word"> 改成&nbsp;<br style="word-wrap:break-word"> <br style="word-wrap:break-word"> &nbsp; &nbsp;t.DSTC_ID in<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; (select b.regoinid<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;from area_info_center@srcdb b<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; where b.parentregoinid = 1919&nbsp;<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; union&nbsp;<br style="word-wrap:break-word"> &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; select 1919 from dual)</td> 
  </tr> 
 </tbody> 
</table>

以上问题的解决参考博文：

[http://www.itpub.net/thread-1869066-1-1.html][http_www.itpub.net_thread-1869066-1-1.html]

[http_www.itpub.net_thread-1869066-1-1.html]: http://www.itpub.net/thread-1869066-1-1.html