再一次利用with as 最佳化SQL

上海的一個哥們問我有個SQL跑了4個小時都沒跑完，實在受不了了，找我最佳化一下。我確實挺佩服他的，要是我遇到跑了幾分鐘的，就受不了了。

SQL語句和執行計畫如下：

--sql id：1qbbw3th4x8yc 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".FMeasureUnitID "ENTRYMEASUREUNIT.ID",       "ASSISTRECORDS".FID "ASSISTRECORDS.ID",       "ASSISTRECORDS".FSeq "ASSISTRECORDS.SEQ",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FOriginalAmount         ELSE          "ASSISTRECORDS".FOriginalAmount       END "ASSISTRECORDS.ORIGINALAMOUNT",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FLocalAmount         ELSE          "ASSISTRECORDS".FLocalAmount       END "ASSISTRECORDS.LOCALAMOUNT",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FReportingAmount         ELSE          "ASSISTRECORDS".FReportingAmount       END "ASSISTRECORDS.REPORTINGAMOUNT",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FQuantity         ELSE          "ASSISTRECORDS".FQuantity       END "ASSISTRECORDS.QUANTITY",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FStandardQuantity         ELSE          "ASSISTRECORDS".FStandardQuantity       END "ASSISTRECORDS.STANDARDQTY",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FPrice         ELSE          "ASSISTRECORDS".FPrice       END "ASSISTRECORDS.PRICE",       CASE         WHEN ("ACCOUNT".FCAA IS NULL) THEN          NULL         ELSE          "ASSISTRECORDS".FAssGrpID       END "ASSGRP.ID"  FROM T_GL_Voucher "VOUCHER"  LEFT OUTER JOIN T_BD_Period "PERIOD"    ON "VOUCHER".FPeriodID = "PERIOD".FID INNER JOIN T_GL_VoucherEntry "ENTRIES"    ON "VOUCHER".FID = "ENTRIES".FBillID INNER JOIN T_BD_AccountView "ACCOUNT"    ON "ENTRIES".FAccountID = "ACCOUNT".FID  LEFT OUTER JOIN T_GL_VoucherAssistRecord "ASSISTRECORDS"    ON "ENTRIES".FID = "ASSISTRECORDS".FEntryID WHERE "VOUCHER".FID IN       (SELECT "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=')))))) ORDER BY "ID" ASC, "ENTRIES.SEQ" ASC, "ASSISTRECORDS.SEQ" ASC --執行計畫 --------------------------------------------------------------------------------------------------------| 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".FMeasureUnitID "ENTRYMEASUREUNIT.ID",       "ASSISTRECORDS".FID "ASSISTRECORDS.ID",       "ASSISTRECORDS".FSeq "ASSISTRECORDS.SEQ",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FOriginalAmount         ELSE          "ASSISTRECORDS".FOriginalAmount       END "ASSISTRECORDS.ORIGINALAMOUNT",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FLocalAmount         ELSE          "ASSISTRECORDS".FLocalAmount       END "ASSISTRECORDS.LOCALAMOUNT",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FReportingAmount         ELSE          "ASSISTRECORDS".FReportingAmount       END "ASSISTRECORDS.REPORTINGAMOUNT",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FQuantity         ELSE          "ASSISTRECORDS".FQuantity       END "ASSISTRECORDS.QUANTITY",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FStandardQuantity         ELSE          "ASSISTRECORDS".FStandardQuantity       END "ASSISTRECORDS.STANDARDQTY",       CASE         WHEN (("ACCOUNT".FCAA IS NULL) AND              ("ACCOUNT".FhasUserProperty <> 1)) THEN          "ENTRIES".FPrice         ELSE          "ASSISTRECORDS".FPrice       END "ASSISTRECORDS.PRICE",       CASE         WHEN ("ACCOUNT".FCAA IS NULL) THEN          NULL         ELSE          "ASSISTRECORDS".FAssGrpID       END "ASSGRP.ID"  FROM T_GL_Voucher "VOUCHER"  LEFT OUTER JOIN T_BD_Period "PERIOD"    ON "VOUCHER".FPeriodID = "PERIOD".FID INNER JOIN T_GL_VoucherEntry "ENTRIES"    ON "VOUCHER".FID = "ENTRIES".FBillID INNER JOIN T_BD_AccountView "ACCOUNT"    ON "ENTRIES".FAccountID = "ACCOUNT".FID  LEFT OUTER JOIN T_GL_VoucherAssistRecord "ASSISTRECORDS"    ON "ENTRIES".FID = "ASSISTRECORDS".FEntryID WHERE "VOUCHER".FID IN       (select id from x) ORDER BY "ID" ASC, "ENTRIES.SEQ" ASC, "ASSISTRECORDS.SEQ" ASC ---======執行計畫------------------------------------------------------------------------------------------------------| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)|------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                  |                             |    24 | 11208 |   506   (1)||   1 |  TEMP TABLE TRANSFORMATION        |                             |       |       |            ||   2 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D6853_1AD5C99D |       |       |            ||   3 |    HASH JOIN                      |                             |     1 |   415 |   458   (1)||   4 |     NESTED LOOPS                  |                             |       |       |            ||   5 |      NESTED LOOPS                 |                             |   258 | 83850 |   390   (0)||   6 |       NESTED LOOPS                |                             |     6 |  1332 |     3   (0)||   7 |        TABLE ACCESS BY INDEX ROWID| T_BD_ACCOUNTVIEW            |     1 |   111 |     2   (0)||   8 |         INDEX UNIQUE SCAN         | PK_BD_ACCOUNTVIEW           |     1 |       |     1   (0)||   9 |        INDEX RANGE SCAN           | IX_BD_ACTCOMLNUM            |     6 |   666 |     1   (0)||  10 |       INDEX RANGE SCAN            | IX_GL_VCHAACCT              |   489 |       |     1   (0)||  11 |      TABLE ACCESS BY INDEX ROWID  | T_GL_VOUCHERENTRY           |    42 |  4326 |    65   (0)||  12 |     INDEX RANGE SCAN              | IX_GL_VCH_11                |  7536 |   662K|    68   (0)||  13 |   SORT ORDER BY                   |                             |    24 | 11208 |    48   (5)||  14 |    NESTED LOOPS OUTER             |                             |    24 | 11208 |    47   (3)||  15 |     NESTED LOOPS                  |                             |    17 |  6086 |    21   (5)||  16 |      NESTED LOOPS                 |                             |    17 |  5253 |    13   (8)||  17 |       NESTED LOOPS OUTER          |                             |     1 |   121 |     5  (20)||  18 |        NESTED LOOPS               |                             |     1 |    87 |     4  (25)||  19 |         VIEW                      | VW_NSO_1                    |     1 |    29 |     2   (0)||  20 |          HASH UNIQUE              |                             |     1 |    24 |            ||  21 |           VIEW                    |                             |     1 |    24 |     2   (0)||  22 |            TABLE ACCESS FULL      | SYS_TEMP_0FD9D6853_1AD5C99D |     1 |    29 |     2   (0)||  23 |         INDEX RANGE SCAN          | IX_GL_VCH_FIDCMPNUM         |     1 |    58 |     1   (0)||  24 |        INDEX RANGE SCAN           | IX_PERIOD_ENC               |     1 |    34 |     1   (0)||  25 |       TABLE ACCESS BY INDEX ROWID | T_GL_VOUCHERENTRY           |    17 |  3196 |     8   (0)||  26 |        INDEX RANGE SCAN           | IX_GL_VCHENTRYFQ1           |    17 |       |     1   (0)||  27 |      TABLE ACCESS BY INDEX ROWID  | T_BD_ACCOUNTVIEW            |     1 |    49 |     1   (0)||  28 |       INDEX UNIQUE SCAN           | PK_BD_ACCOUNTVIEW           |     1 |       |     1   (0)||  29 |     TABLE ACCESS BY INDEX ROWID   | T_GL_VOUCHERASSISTRECORD    |     1 |   109 |     2   (0)||  30 |      INDEX RANGE SCAN             | IX_GL_VCHASSREC_11          |     2 |       |     1   (0)|------------------------------------------------------------------------------------------------------

最後

有sql 要最佳化 加我qq 692162374 或者 加 群