上海的一个哥们问我有个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)|
- ------------------------------------------------------------------------------------------------------
最后 1分钟全部查询出来,42956条数据
其实这个sql 没有 优化彻底,如果真的要认真对待,大概30秒左右就能跑完 不过难得搞了
有sql 要优化 加我qq 692162374 或者 加 群 220761024
想要跟我学优化的,请点击这里
最后我才知道 这个哥们 18k了 现在 我操。。。。他跟我学了ORACLE sql优化大概4个月了