--------------------------------
| 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