2015年5月的一天，客戶發來郵件請求幫助：一個SQL執行需要11個小時，執行計劃中使用nested loops好像效率差了些，能不能用hint讓優化器使用hash join，用use_hash 的 hint好像沒生效？
下面是客戶郵件原文截取：
SQL代碼如下：
SELECT DISTINCT N.ID_NBDL_TCIMS_PCFC_ORDER, M.POLICY_NO, N.POLICY_NO
FROM (SELECT /*+ USE_HASH(R,C) */
C.ID_BSE_TCIMS_PCFC_ORDER,
C.PRODUCT_CODE,
D.INSURANT_CERTIFICATE_NUMBER,
C.DATE_BEGIN,
C.POLICY_NO
FROM N_SM_PCFC_ORDER_MERGE_02_TMP R,
BSE_TCIMS_PCFC_ORDER_BM_TMP C,
BSE_TCIMS_PCFC_INS_BM_TMP D
WHERE R.TCIMS_LIST_ID IS NULL
AND R.RN = 1
AND R.ID_NBDL_TCIMS_PCFC_ORDER = C.ID_BSE_TCIMS_PCFC_ORDER
AND C.ID_BSE_TCIMS_PCFC_ORDER = D.ID_BSE_TCIMS_PCFC_ORDER
AND C.PARENT_POLICY_NO IS NULL OR INSTR(C.PARENT_POLICY_NO, 'tmp') > 0
) M,
NBDL_TCIMS_PCFC_ORDER N,
NBDL_TCIMS_PCFC_INSURANT Q,
BDL_TCIMS_PCFC_TB_PRODUCT K
WHERE M.INSURANT_CERTIFICATE_NUMBER = Q.INSURANT_CERTIFICATE_NUMBER
AND M.PRODUCT_CODE = K.NEW_PRODUCT_CODE
AND N.PRODUCT_CODE = K.LAST_PRODUCT_CODE
AND (M.DATE_BEGIN + 98) >= N.DATE_END
AND N.DATE_END + 30 >= M.DATE_BEGIN
AND N.POLICY_TYPE = 'NB'
AND N.ID_NBDL_TCIMS_PCFC_ORDER = Q.ID_NBDL_TCIMS_PCFC_ORDER;
SQL目前執行計劃：
其中第7步的nested loops操作确實是最消耗時間的一步：hint指定了N_SM_PCFC_ORDER_MERGE_02_TMP表要做hash join，但實際上還是使用了nested loops 的join 方法，而且該表做為被驅動表，使用全表掃描不正常。
仔細分析了一些SQL代碼，發現紅色部分的業務邏輯極有可能是有問題的，于是請求客戶找他們的研發人員确認，客戶答複是這樣的：
老虎劉覺得客戶可能沒有真正去找研發去确認，于是發郵件再次請求确認，并告知如果這就是正确的業務邏輯，可優化的空間也不大：
客戶這次應該真的找研發确認了，确實是邏輯有問題。修正了SQL邏輯并執行，執行效率還是不高：
既然客戶一開始想讓執行計劃都使用hash join，那麼先嘗試一下，順便收集一下SQL執行過程的實際情況，先加兩段hint：
SELECT /*+ leading(k m n q) use_hash(m) use_hash(n) use_hash(q) */
DISTINCT N.ID_NBDL_TCIMS_PCFC_ORDER, M.POLICY_NO, N.POLICY_NO
FROM (SELECT /*+ no_merge leading(r c d) use_hash(c) use_hash(d)*/
C.ID_BSE_TCIMS_PCFC_ORDER,
C.PRODUCT_CODE,
D.INSURANT_CERTIFICATE_NUMBER,
C.DATE_BEGIN,
C.POLICY_NO
FROM N_SM_PCFC_ORDER_MERGE_02_TMP R,
BSE_TCIMS_PCFC_ORDER_BM_TMP C,
BSE_TCIMS_PCFC_INS_BM_TMP D
WHERE R.TCIMS_LIST_ID IS NULL
AND R.RN = 1
AND R.ID_NBDL_TCIMS_PCFC_ORDER = C.ID_BSE_TCIMS_PCFC_ORDER
AND C.ID_BSE_TCIMS_PCFC_ORDER = D.ID_BSE_TCIMS_PCFC_ORDER
AND (C.PARENT_POLICY_NO IS NULL OR INSTR(C.PARENT_POLICY_NO, 'tmp') > 0) --已增加括号修正錯誤邏輯
) M, --14k
NBDL_TCIMS_PCFC_ORDER N, --165w
NBDL_TCIMS_PCFC_INSURANT Q,--1700w
BDL_TCIMS_PCFC_TB_PRODUCT K--75
WHERE M.INSURANT_CERTIFICATE_NUMBER = Q.INSURANT_CERTIFICATE_NUMBER
AND M.PRODUCT_CODE = K.NEW_PRODUCT_CODE
AND N.PRODUCT_CODE = K.LAST_PRODUCT_CODE
AND (M.DATE_BEGIN + 98) >= N.DATE_END AND N.DATE_END + 30 >= M.DATE_BEGIN
AND N.POLICY_TYPE = 'NB' --165w
AND N.ID_NBDL_TCIMS_PCFC_ORDER = Q.ID_NBDL_TCIMS_PCFC_ORDER;
第一次嘗試的結果是687秒，知道了各表經過謂詞條件和join 後傳回的大緻記錄數，同時也發現，并不是全部的表關聯都使用hash才是最優的：
因為NBDL_TCIMS_PCFC_INSURANT表沒有INSURANT_CERTIFICATE_NUMBER字段上的索引，是以第二次給出的優化建議是：
改n表做nested loops，同時用過leading調整表的關聯順序，q表與n表互換位置
SELECT /*+ leading(k m q n) use_hash(m) use_hash(q) use_nl(n) */
DISTINCT N.ID_NBDL_TCIMS_PCFC_ORDER, M.POLICY_NO, N.POLICY_NO
FROM (SELECT /*+ no_merge leading(r c d) use_hash(c) use_hash(d)*/
C.ID_BSE_TCIMS_PCFC_ORDER,
C.PRODUCT_CODE,
D.INSURANT_CERTIFICATE_NUMBER,
C.DATE_BEGIN,
C.POLICY_NO
FROM N_SM_PCFC_ORDER_MERGE_02_TMP R,
BSE_TCIMS_PCFC_ORDER_BM_TMP C,
BSE_TCIMS_PCFC_INS_BM_TMP D
WHERE R.TCIMS_LIST_ID IS NULL
AND R.RN = 1
AND R.ID_NBDL_TCIMS_PCFC_ORDER = C.ID_BSE_TCIMS_PCFC_ORDER
AND C.ID_BSE_TCIMS_PCFC_ORDER = D.ID_BSE_TCIMS_PCFC_ORDER
AND (C.PARENT_POLICY_NO IS NULL OR INSTR(C.PARENT_POLICY_NO, 'tmp') > 0)
) M, --14k
NBDL_TCIMS_PCFC_ORDER N, --165w
NBDL_TCIMS_PCFC_INSURANT Q,--1700w
BDL_TCIMS_PCFC_TB_PRODUCT K--75
WHERE M.INSURANT_CERTIFICATE_NUMBER = Q.INSURANT_CERTIFICATE_NUMBER
AND M.PRODUCT_CODE = K.NEW_PRODUCT_CODE
AND N.PRODUCT_CODE = K.LAST_PRODUCT_CODE
AND (M.DATE_BEGIN + 98) >= N.DATE_END AND N.DATE_END + 30 >= M.DATE_BEGIN
AND N.POLICY_TYPE = 'NB' --165w
AND N.ID_NBDL_TCIMS_PCFC_ORDER = Q.ID_NBDL_TCIMS_PCFC_ORDER;
第二次優化後，SQL的執行時間減少到25秒（并行度為4，與原始SQL使用的并行度一緻）
到了這一步，老虎劉告訴客戶，如果在NBDL_TCIMS_PCFC_INSURANT表的INSURANT_CERTIFICATE_NUMBER字段上建立一個索引，可以在不使用并行的情況下，執行時間減少到5秒左右。但是客戶已經覺得優化效果非常好了，不想再去建什麼索引了。