繫結變數，組合查詢方式，導致CBO錯誤一例

生產系統，出現一個SQL，執行計劃不對，SQL如下：

SELECT SDMFID GMFMFID,
'[' || TO_CHAR(TO_NUMBER(PMCODE)) || ']' || PMNAME NAME,
SUM(SPGGDMONEY * FGETINVSIGN(SHDJLB)) JE
FROM SELLHEAD, SELLDETAIL, SELLPAYGOODS, PAYMODE, GOODSMFRAME
WHERE SHBILLNO = SPGBILLNO
AND SHBILLNO = SDBILLNO
AND SDROWNO = SPGGDROW
AND SPGPMCODE = PMCODE
AND SDMFID = GMFMFID
AND SDGDID = GMFGDID
AND GMFMARKET = :as_mkt
AND SPGPMCODE <> '0500'
AND PMTYPE = '5'
AND (GMFMFID = :as_mf OR NVL(:as_mf, '#') = '#')
AND SHDATE >= TRUNC(to_date(:adt_start,'YYYY-MM-DD'))
AND SHDATE < TRUNC(to_date(:adt_end,'YYYY-MM-DD')) + 1
GROUP BY SDMFID, PMCODE, PMNAME

這裡要注意的是，:as_mf 變數傳遞進來的值是空''。可以看到，採用了繫結變數的方式。執行計劃如下：

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1265 (100)| | | |
| 1 | HASH GROUP BY | | 1 | 150 | 1265 (1)| 00:00:16 | | |
|* 2 | FILTER | | | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| SELLPAYGOODS | 1 | 34 | 3 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 150 | 1264 (1)| 00:00:16 | | |
|* 5 | HASH JOIN | | 1 | 116 | 1261 (1)| 00:00:16 | | |
| 6 | REMOTE | SELLHEAD | 1101 | 27525 | 29 (0)| 00:00:01 | POS_DB | R->S |
| 7 | NESTED LOOPS | | 2160 | 191K| 1238 (1)| 00:00:15 | | |
| 8 | MERGE JOIN CARTESIAN | | 122 | 5490 | 322 (1)| 00:00:04 | | |
|* 9 | TABLE ACCESS FULL | PAYMODE | 1 | 20 | 3 (0)| 00:00:01 | | |
| 10 | BUFFER SORT | | 166 | 4150 | 319 (1)| 00:00:04 | | |
|* 11 | TABLE ACCESS FULL | GOODSMFRAME | 166 | 4150 | 319 (1)| 00:00:04 | | |
| 12 | REMOTE | SELLDETAIL | 18 | 828 | 46 (0)| 00:00:01 | POS_DB | R->S |
|* 13 | INDEX RANGE SCAN | PK_SELLPAYGOODS | 1 | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------

這個執行計劃的主要問題是粗黑體顯示的NEST LOOP，而導致這裡選擇nest loop的主要原因是MERGE JOIN CARTESIAN 完畢後，Card只有122

看看實際的值：

SQL> SELECT count(*)
2 FROM PAYMODE, GOODSMFRAME
3 WHERE GMFMARKET = '5101'
4 AND PMTYPE = 5
5 AND (GMFMFID = '' OR NVL('', '#') = '#');

COUNT(*)
----------
93058

由於使用了變數，導致(GMFMFID = '' OR NVL('', '#') = '#')的選擇度判斷不正確，並且，即使進行了變數窺視，也依然如此。

解決的方式是，這裡不要進行變數繫結。

SELECT SDMFID GMFMFID,
'[' || TO_CHAR(TO_NUMBER(PMCODE)) || ']' || PMNAME NAME,
SUM(SPGGDMONEY * FGETINVSIGN(SHDJLB)) JE
FROM SELLHEAD, SELLDETAIL, SELLPAYGOODS, PAYMODE, GOODSMFRAME
WHERE SHBILLNO = SPGBILLNO
AND SHBILLNO = SDBILLNO
AND SDROWNO = SPGGDROW
AND SPGPMCODE = PMCODE
AND SDMFID = GMFMFID
AND SDGDID = GMFGDID
AND GMFMARKET = :as_mkt
AND SPGPMCODE <> '0500'
AND PMTYPE = 5
AND (GMFMFID = '' OR NVL('', '#') = '#')
AND SHDATE >= TRUNC(:adt_start)
AND SHDATE < TRUNC(:adt_end) + 1
GROUP BY SDMFID, PMCODE, PMNAME

---------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 150 | 3583 | | |
| 1 | HASH GROUP BY | | 1 | 150 | 3583 | | |
| 2 | FILTER | | | | | | |
| 3 | NESTED LOOPS | | 1 | 150 | 3582 | | |
| 4 | NESTED LOOPS | | 3 | 375 | 3576 | | |
| 5 | NESTED LOOPS | | 43 | 3397 | 3493 | | |
| 6 | HASH JOIN | | 4283 | 246K| 3492 | | |
| 7 | REMOTE | SELLHEAD | 4212 | 102K| 182 | POS_DB | R->S |
| 8 | TABLE ACCESS FULL | SELLPAYGOODS | 89123 | 2959K| 3309 | | |
| 9 | TABLE ACCESS BY INDEX ROWID| PAYMODE | 1 | 20 |1 | | |
| 10 | INDEX UNIQUE SCAN | PK_PAYMODE | 1 | |0 | | |
| 11 | REMOTE | SELLDETAIL | 1 | 46 |2 | POS_DB | R->S |
| 12 | TABLE ACCESS BY INDEX ROWID | GOODSMFRAME | 1 | 25 |2 | | |
| 13 | INDEX UNIQUE SCAN | PK_GOODSMFRAME | 1 | |1 | | |
---------------------------------------------------------------------------------------------------

資料庫版本：10.2.0.1

[@more@]