概述

前段时间开发丢了一个超长的sql给我，说需要优化，因为太长，连PL/SQL的美化工具都美化不了...下面简单记录一下优化的过程。

with改写

WITH AS短语，也叫做子查询部分（subquery factoring），可以做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。

1、查看数据量及表信息

这里有一张大表，2000万数据，还有一个小表，整个sql其实就是先对小表做查询得出结果集2000多条，然后2000多条再去跟大表去做关联。

2、获取sqlid

3、查看执行计划

--explain plan for select XXX
--select * from table(dbms_xplan.display(table_name=>'PLAN_TABLE',statement_id=>null,format=>'ALL'));

4、优化前耗时

15行耗时64秒

全部1000多条查完需要11分钟

5、奇怪的现象

结果集t查询出来速度是不慢的，数据量2000多条，但是跟fsl_trunkshipment_detail做关联查询的时候查询100条需要63秒，查询出全部数据就需要最少11分钟了。

这里用with写法改写了下：

WITH tmp AS (select DISTINCT sequence_no, location_xid, packaged_item_gid,
 packaging_unit_gid, transport_handling_unit_gid, pkg_foldv,
 pkg_regularv, pte_foldv, pte_regularv
....
SELECT d.handle_plate_code,d.plastic_box_code FROM fsl_trunkshipment_detail d,tmp 
WHERE d.packaged_item_id = REPLACE(tmp.packaged_item_gid, 'FSL.', '') AND d.request_plant_id = tmp.location_xid
 AND d.shipment_xid = 'BS2019033054709' AND d.jit_month = '201904'

优化后sql耗时为7秒，满足需求。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~