一位上海的朋友说以下 SQL 执行不出结果。

    1. with tab as 
    2. （
    3. select bb.card_no cashier_shop_no, aa.card_open_owner merch_id,aa.card_no ,bb.txn_date,bb.last_txn_date,bb.merch_loc_name
    4.  from tb_card aa 
    5.  join 
    6. (select t.card_no,a.merch_id,a.merch_loc_name ,t.cust_id txn_date, 
    7. to_char(last_day( add_months(to_date(t.cust_id,'yyyymmdd'),1)) ,'yyyymmdd') last_txn_date
    8. from tb_bill_test t join tb_merch a on t.card_no=a.cashier_shop_no 
    9. where t.nbr_group='161' and a.merch_id not like '0%' )bb 
    11. on aa.card_open_owner=bb.merch_id
    12. where aa.mbr_reg_date between bb.txn_date and  bb.last_txn_date 
    13. ）
    15. select  bb.cashier_shop_no， bb.merch_loc_name，aa.txn_date，
    16. case 
    17.     when aa.merch_id=bb.merch_id and aa.card_no=bb.card_no 
    18.         then '本店会员'     
    19.         else '他店会员' 
    20. end shop_no，
    21.   count（distinct aa.card_no） card_num，
    22.   sum（ case when aa.p_code in ('7646','7686','7208') then -1 else 1 end ） count_num，
    23.   sum（case 
    24.               when  aa.p_code in ('7646','7686','7208') then 0-
    25.               case 
    26.                 when aa.txn_amt>aa.earning_amt 
    27.                   then aa.txn_amt 
    28.                   else aa.earning_amt 
    29.         end 
    30.           else  
    31.                   case 
    32.                       when aa.txn_amt>aa.earning_amt 
    33.                               then aa.txn_amt 
    34.                       else aa.earning_amt 
    35.                   end 
    36.           end） txn_amt 
    37.  from tb_trans aa join tab bb on aa.merch_id=bb.merch_id
    38. where  aa.txn_date between bb.txn_date and bb.last_txn_date and 
    39. aa.p_code in （'7647','7687','7207','7646','7686','7208'）
    40. and aa.status in （'1','R'）
    41. group by bb.cashier_shop_no， bb.merch_loc_name，aa.txn_date，aa.merch_id，
    42. case when aa.merch_id=bb.merch_id and aa.card_no=bb.card_no then '本店会员' else '他店会员' end 
    43. order by aa.merch_id， aa.txn_date；

    执行计划如下。

    1. Plan hash value: 4271695044
    3. -------------------------------------------------------------------------------------
    4. | Id |Operation                             |Name              |Rows|Bytes|Cost(%CPU)|
    5. -------------------------------------------------------------------------------------
    6. |  0 |SELECT STATEMENT                      |                  |  15|1650|96737  (1)|
    7. |  1 | SORT GROUP BY                        |                  |  15|1650|96737  (1)|
    8. |  2 |  VIEW                                |VW_DAG_0          |  15|1650|96735  (1)|
    9. |  3 |   HASH GROUP BY                      |                  |  15|2430|96735  (1)|
    10. |  4 |    NESTED LOOPS                      |                  |  15| 430|96734  (1)|
    11. |  5 |     NESTED LOOPS                     |                  |2213|2430|96734  (1)|
    12. |  6 |      NESTED LOOPS                    |                  |   1| 103|  542  (1)|
    13. |  7 |       NESTED LOOPS                   |                  |   1|  65|   58  (0)|
    14. |  8 |        TABLE ACCESS BY INDEX ROWID   |TB_BILL_TEST      |  36| 864|   20  (0)|
    15. |* 9 |         INDEX RANGE SCAN             |TMP_INDEX_BILL_01 |  37|    |    3  (0)|
    16. |*10 |        TABLE ACCESS BY INDEX ROWID   |TB_MERCH          |   1|  41|    3  (0)|
    17. |*11 |         INDEX RANGE SCAN             |I1_MERCH          |   1|    |    1  (0)|
    18. |*12 |       TABLE ACCESS BY INDEX ROWID    |TB_CARD           |   2|  76|  484  (1)|
    19. |*13 |        INDEX RANGE SCAN              |I1_CARD_OPEN_OWNER|3855|    |   24  (0)|
    20. |*14 |      INDEX RANGE SCAN                |I2_TRANS          |2213|    |95972  (1)|
    21. |*15 |    TABLE ACCESS BY GLOBAL INDEX ROWID|TB_TRANS          |  56|3304|96193  (1)|
    22. -------------------------------------------------------------------------------------
    24. Predicate Information (identified by operation id):
    25. ---------------------------------------------------
    27.    9 - access("T"."NBR_GROUP"='161')
    28.        filter("T"."CARD_NO" IS NOT NULL)
    29.   10 - filter("A"."MERCH_ID" NOT LIKE '0%')
    30.   11 - access("T"."CARD_NO"="A"."CASHIER_SHOP_NO")
    31.   12 - filter("AA"."MBR_REG_DATE">="T"."CUST_ID" AND "AA"."MBR_REG_DATE"<=TO_CHAR(LAST_DAY(ADD_MONTHS
    32.        (TO_DATE("T"."CUST_ID",'yyyymmdd'),1)),'yyyymmdd'))
    33.   13 - access("AA"."CARD_OPEN_OWNER"="A"."MERCH_ID")
    34.        filter("AA"."CARD_OPEN_OWNER" IS NOT NULL)
    35.   14 - access("AA"."TXN_DATE">="T"."CUST_ID" AND "AA"."MERCH_ID"="AA"."CARD_OPEN_OWNER" AND     "AA"."TXN_DATE"<=TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE("T"."CUST_ID",'yyyymmdd'),1)),'yyyymmdd'))
    36.        filter("AA"."MERCH_ID"="AA"."CARD_OPEN_OWNER")
    37.   15 - filter(("AA"."P_CODE"='7207' OR "AA"."P_CODE"='7208' OR "AA"."P_CODE"='7646' OR "AA"."P_CODE"='7647' 
    38.        OR "AA"."P_CODE"='7686' OR "AA"."P_CODE"='7687') AND ("AA"."STATUS"='1' OR "AA"."STATUS"='R'))

    我们拿到一条需要优化的 SQL 语句，怎么入手呢？首先要看 SQL 写法。可以利用 SQL 三段分拆方法，先观察 SQL 语句。该 SQL 语句有个 with as 子句取名为 tab，主查询中就是 tb_trans 与 tab 进行关联。with as 子句一共返回 6 000 多行数据，可以 1 秒内出结果，tb_trans 有两亿条数据。执行计划中，with as 子查询作为一个整体并且作为嵌套循环驱动表，tb_trans 作为嵌套循环被驱动表，乍一看，这也符合嵌套循环关联原则，小表驱动大表，大表走索引。但是该SQL执行不出结果，最大的可能就是 tab 与 tb_trans 关联之后返回数据量太多，因为返回结果集太多，被驱动表走索引，也就是说该 SQL 可能是被驱动表走索引返回数据量太多导致性能问题。于是检查被驱动表连接列 merch_id 基数，基数很低，tab:tb_trans 是 1 比几十万关系。

    因为被驱动表 tb_trans 与 tab 是几十万比 1 的关系，这时就不能走嵌套循环了，只能走 HASH 连接，于是使用 HINT：use_hash（aa,bb）优化 SQL，最终该 SQL 可以在 1 小时左右执行完毕。如果开启并行查询可以更快。