涉及知識(shí)：in 和 exist的原理
問題現(xiàn)象：
在應(yīng)收應(yīng)付的單據(jù)相關(guān)業(yè)務(wù)中上真，出現(xiàn)了明顯的SQL效率問題咬腋。SQL執(zhí)行耗時(shí)三分鐘以上。
由于最終查詢數(shù)據(jù)量較小睡互，故認(rèn)為可以優(yōu)化至3秒以內(nèi)根竿。
問題SQL如下
SELECT DISTINCT ar_recbill.pk_recbill,
ar_recbill.billdate,
ar_recbill.billno
FROM ar_recbill ar_recbill
WHERE (ar_recbill.pk_org = '00014910000000002Z8G' AND
(ar_recbill.billdate >= '2015-12-14 00:00:00' and
ar_recbill.billdate <= '2015-12-14 23:59:59'))
AND ar_recbill.billstatus <> 9
AND ar_recbill.isinit = 'N'
AND ar_recbill.pk_group = '00013910000000000N4S'
AND (ar_recbill.pk_recbill NOT IN
(SELECT fr.src_relationid
FROM fip_relation fr
where fr.des_org in (select pk_accountingbook
from org_accountingbook
where accounttype = 1)))
AND ar_recbill.dr = 0
ORDER BY ar_recbill.billdate, ar_recbill.billno

問題分析：
從語句中我們可以看到，并沒有復(fù)雜的連接操作就珠，所以效率問題應(yīng)該是在where的選擇條件中寇壳。而這里比較可疑的條件就是 not in語句的存在。通過執(zhí)行計(jì)劃妻怎，我們可以確信這一點(diǎn)壳炎。
Plan hash value: 920333179

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |

| 0 | SELECT STATEMENT | | 1 | | 11 |00:03:34.92 | 13M| 12M| | | |
| 1 | SORT ORDER BY | | 1 | 1 | 11 |00:03:34.92 | 13M| 12M| 2048 | 2048 | 2048 (0)|
|* 2 | FILTER | | 1 | | 11 |00:03:34.92 | 13M| 12M| | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | AR_RECBILL | 1 | 1 | 11 |00:00:00.01 | 11 | 0 | | | |
|* 4 | INDEX RANGE SCAN | AR_RECBILL_BILLDATE | 1 | 1 | 11 |00:00:00.01 | 3 | 0 | | | |
| 5 | NESTED LOOPS | | 11 | 1 | 0 |00:03:34.92 | 13M| 12M| | | |
| 6 | NESTED LOOPS | | 11 | 1 | 0 |00:03:34.92 | 13M| 12M| | | |
|* 7 | TABLE ACCESS FULL | FIP_RELATION | 11 | 1 | 0 |00:03:34.92 | 13M| 12M| | | |
|* 8 | INDEX UNIQUE SCAN | PK__ACCOUNTINGBOOK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| ORG_ACCOUNTINGBOOK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |

Predicate Information (identified by operation id):

2 - filter( IS NULL)
3 - filter(("AR_RECBILL"."ISINIT"='N' AND "AR_RECBILL"."DR"=0 AND "AR_RECBILL"."BILLSTATUS"<>9 AND
"AR_RECBILL"."PK_GROUP"='00013910000000000N4S'))
4 - access("AR_RECBILL"."PK_ORG"='00014910000000002Z8G' AND "AR_RECBILL"."BILLDATE">='2015-12-14 00:00:00' AND
"AR_RECBILL"."BILLDATE"<='2015-12-14 23:59:59')
7 - filter(LNNVL("FR"."SRC_RELATIONID"<>:B1))
8 - access("FR"."DES_ORG"="PK_ACCOUNTINGBOOK")
9 - filter("ACCOUNTTYPE"=1)

我們可以發(fā)現(xiàn)，最耗時(shí)的語句為7逼侦，走了全表掃描匿辩。而這個(gè)全表掃描，走了filter榛丢〔颍看到filter我們得知，SQL沒有走索引晰赞，同時(shí)還進(jìn)行了函數(shù)的轉(zhuǎn)換稼病。LNNVL("FR"."SRC_RELATIONID"<>:B1选侨。源SQL語句中是查詢pk_recbill NOT IN SELECT fr.src_relationid。這里發(fā)生了隱式轉(zhuǎn)化溯饵。

問題解決：