再一次利用withas优化SQL - 数据库编程

-------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 5733 | 486 (1)| | 1 | SORT ORDER BY | | 13 | 5733 | 486 (1)| | 2 | VIEW | VM_NWVW_2 | 13 | 5733 | 486 (1)| | 3 | HASH UNIQUE | | 13 | 11115 | 486 (1)| | 4 | NESTED LOOPS OUTER | | 13 | 11115 | 485 (1)| | 5 | NESTED LOOPS | | 9 | 6606 | 471 (1)| | 6 | NESTED LOOPS | | 9 | 6057 | 467 (1)| | 7 | MERGE JOIN OUTER | | 1 | 473 | 459 (1)| | 8 | HASH JOIN | | 1 | 427 | 458 (1)| | 9 | NESTED LOOPS | | | | | | 10 | NESTED LOOPS | | 258 | 83850 | 390 (0)| | 11 | NESTED LOOPS | | 6 | 1332 | 3 (0)| | 12 | TABLE ACCESS BY INDEX ROWID| T_BD_ACCOUNTVIEW | 1 | 111 | 2 (0)| | 13 | INDEX UNIQUE SCAN | PK_BD_ACCOUNTVIEW | 1 | | 1 (0)| | 14 | INDEX RANGE SCAN | IX_BD_ACTCOMLNUM | 6 | 666 | 1 (0)| | 15 | INDEX RANGE SCAN | IX_GL_VCHAACCT | 489 | | 1 (0)| | 16 | TABLE ACCESS BY INDEX ROWID | T_GL_VOUCHERENTRY | 42 | 4326 | 65 (0)| | 17 | INDEX RANGE SCAN | IX_GL_VCH_11 | 7536 | 750K| 68 (0)| | 18 | BUFFER SORT | | 1 | 46 | 391 (0)| | 19 | INDEX RANGE SCAN | IX_PERIOD_ENC | 1 | 46 | 1 (0)| | 20 | TABLE ACCESS BY INDEX ROWID | T_GL_VOUCHERENTRY | 17 | 3400 | 8 (0)| | 21 | INDEX RANGE SCAN | IX_GL_VCHENTRYFQ1 | 17 | | 1 (0)| | 22 | TABLE ACCESS BY INDEX ROWID | T_BD_ACCOUNTVIEW | 1 | 61 | 1 (0)| | 23 | INDEX UNIQUE SCAN | PK_BD_ACCOUNTVIEW | 1 | | 1 (0)| | 24 | TABLE ACCESS BY INDEX ROWID | T_GL_VOUCHERASSISTRECORD | 1 | 121 | 2 (0)| | 25 | INDEX RANGE SCAN | IX_GL_VCHASSREC_11 | 2 | | 1 (0)| -------------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version

我靠 plan_table is old version....一般遇到这种执行计划发给我我是理都不理的，但是这哥们关系好，算了吧勉为其难。

那我就不从执行计划入手优化了，直接分析sql语句优化。这个sql挺简单的，from 后面几个表， where 有个 in ，最后有个order by

问他 in 后面返回多少记录

落落 17:11:46
哦
你先看 where in 里面跑多久
上海-咖啡迷 17:12:40
很快
23s 16880rows

其他表大小如下

看到这些就知道怎么优化了。之前跑4个小时不出结果，那行嘛我给你优化到1分钟

in 只返回16880条数据，那么可以用in作为驱动表去驱动 T_GL_Voucher

思路就跟这个博客一样的 http://blog.csdn.net/robinson1988/article/details/10551467 我靠居然还个sb在乱评论

因为in里面有点复杂，不好用hint，我偷懒，难得去搞了，直接用下面的sql 优化

with x as  (SELECT  /*+ materialize */  "VOUCHER".FID "ID"

          FROM T_GL_Voucher "VOUCHER"

         INNER JOIN T_GL_VoucherEntry "ENTRIES"

            ON "VOUCHER".FID = "ENTRIES".FBillID

         INNER JOIN T_BD_AccountView "ACCOUNT"

            ON "ENTRIES".FAccountID = "ACCOUNT".FID

         INNER JOIN t_bd_accountview PAV

            ON ((INSTR("ACCOUNT".flongnumber, pav.flongnumber) = 1 AND

               pav.faccounttableid = "ACCOUNT".faccounttableid) AND

               pav.fcompanyid = "ACCOUNT".fcompanyid)

         WHERE (("VOUCHER".FCompanyID IN ('fSSF82rRSKexM3KKN1d0tMznrtQ=')) AND

               (("VOUCHER".FBizStatus IN (5)) AND

               ((("VOUCHER".FPeriodID IN ('+wQxkBFVRiKnV7OniceMDoI4jEw=')) AND

               "ENTRIES".FCurrencyID =

               'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC') AND

               (pav.FID IN ('vyPiKexLRXiyMb41VSVVzJ2pmCY='))))))

SELECT "VOUCHER".FID "ID",

       "ENTRIES".FID "ENTRIES.ID",

       "ENTRIES".FEntryDC "ENTRIES.ENTRYDC",

       "ACCOUNT".FID "ACCOUNT.ID",

       "ENTRIES".FCurrencyID "CURRENCY.ID",

       "PERIOD".FNumber "PERIOD.NUMBER",

       "ENTRIES".FSeq "ENTRIES.SEQ",

       "ENTRIES".FLocalExchangeRate "LOCALEXCHANGERATE",

       "ENTRIES".FReportingExchangeRate "REPORTINGEXCHANGERATE",

       "ENTRIES".FMeasu

再一次利用withas优化SQL(二)