开发说下面这个sql语句跑不出结果
SELECT t1.order_id, t2.order_name, t1.order_flow_no, t1.order_type, t1.agent_id, t1.money, t1.order_create_time FROM (SELECT re.id AS order_id, re.serialnumber AS order_flow_no, re.money AS money, '1' AS order_type, re.agent_id AS agent_id, re.create_date AS order_create_time FROM tb_recharge re WHERE create_date >= to_date('2014-07-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - INTERVAL '1' DAY AND create_date < to_date('2014-07-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND re.status != 2) t1 LEFT JOIN (SELECT tt1.orderform_flow_no, tt1.order_name FROM (SELECT tp1.PAYMENT_FLOW_NO, tp1.orderform_flow_no, tp1.orderform_name AS order_name FROM tb_payment tp1 WHERE create_time >= to_date('2014-07-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - INTERVAL '1' DAY AND create_time < to_date('2014-07-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND tp1.orderform_type = 1 AND tp1.id NOT IN (SELECT tr.payment_id FROM tb_refund tr)) tt1 LEFT JOIN (SELECT business_flow_no FROM tb_fund_flow f WHERE f.business_type = 0 AND create_time >= to_date('2014-07-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - INTERVAL '1' DAY AND create_time < to_date('2014-07-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) tt2 ON tt1.PAYMENT_FLOW_NO = tt2.business_flow_no WHERE tt2.business_flow_no IS NOT NULL) t2 ON t1.order_flow_no = t2.orderform_flow_no WHERE t2.orderform_flow_no IS NOT NULL
plan如下
--------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 440 | 158K (75)| 00:31:40 | | ||* 1 | HASH JOIN ANTI | | 1 | 440 | 158K (75)| 00:31:40 | | || 2 | NESTED LOOPS | | 1 | 434 | 156K (75)| 00:31:23 | | || 3 | NESTED LOOPS | | 1 | 434 | 156K (75)| 00:31:23 | | || 4 | MERGE JOIN CARTESIAN | | 1 | 125 | 156K (75)| 00:31:23 | | || 5 | PARTITION RANGE SINGLE | | 1 | 83 | 0 (0)| 00:00:01 | 12 | 12 ||* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_RECHARGE | 1 | 83 | 0 (0)| 00:00:01 | 12 | 12 ||* 7 | INDEX RANGE SCAN | TB_RECHARGE_I5 | 1 | | 0 (0)| 00:00:01 | 12 | 12 || 8 | BUFFER SORT | | 673K| 26M| 156K (75)| 00:31:23 | | || 9 | PARTITION RANGE SINGLE | | 673K| 26M| 156K (75)| 00:31:23 | 8 | 8 ||* 10 | TABLE ACCESS FULL | TB_FUND_FLOW | 673K| 26M| 156K (75)| 00:31:23 | 8 | 8 ||* 11 | INDEX UNIQUE SCAN | UNIQ_FLOW_TYPE1 | 1 | | 2 (0)| 00:00:01 | | ||* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | TB_PAYMENT | 1 | 309 | 2 (0)| 00:00:01 | 8 | 8 || 13 | TABLE ACCESS FULL | TB_REFUND | 150K| 884K| 1402 (1)| 00:00:17 | | |--------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("TP1"."ID"="TR"."PAYMENT_ID") 6 - filter("RE"."STATUS"<>2) 7 - access("CREATE_DATE">=TO_DATE(' 2014-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CREATE_DATE" =TO_DATE(' 2014-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CREATE_TIME" =TO_DATE(' 2014-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CREATE_TIME"
相关表大小说明：
tb_recharge: 6G
tb_fund_flow: 9G
tb_payment: 4G
都为分区表
这个执行计划一眼就可以看出很多表统计信息有问题，先不管统计信息是否正确，定位到id为4 中tb_recharge 和tb_fund_folw 两个大表做MERGE JOIN CARTESIAN 笛卡尔，并且tb_found_flow TABLE FULL SCAN, 发现问题的时候这个sql已经跑了3个小时了。
注意上面sql语句的写法，都是将大表先过滤在相互去join，说明一点这个sql最终join结果只有2条数据。但是最终CBO查询改写了，导致内敛视图合并，出现笛卡尔。看到这里优化就非常简单了，于是我加了一个hint no_merge不允许视图合并。
下面是最后的结果，1s数据就出来了
SELECT /*+ no_merge(t1) no_merge(t2)*/ t1.order_id, t2.order_name, t1.order_flow_no, t1.order_type, t1.agent_id, t1.money, t1.order_create_time FROM (SELECT re.id AS order_id, re.serialnumber AS order_flow_no, re.money AS money, '1' AS order_type, re.agent_id AS agent_id, re.create_date AS order_create_time FROM tb_recharge re WHERE create_date >= to_date('2014-07-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - INTERVAL '1' DAY AND create_date < to_date('2014-07-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND re.status != 2) t1 LEFT JOIN (SELECT tt1.orderform_flow_no, tt1.order_name FROM (SELECT tp1.PAYMENT_FLOW_NO, tp1.orderform_flow_no, tp1.orderform_name AS order_name FROM tb_payment tp1 WHERE create_time >= to_date('2014-07-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - INTERVAL '1' DAY AND create_time < to_date('2014-07-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND tp1.orderform_type = 1 AND tp1.id NOT IN (SELECT tr.payment_id FROM tb_refund tr)) tt1 LEFT JOIN (SELECT /*+ index(f IDX_FUNDFLOW_CRTTIME) */ business_flow_no FROM tb_fund_flow f WHERE f.business_type = 0 AND create_time >= to_date('2014-07-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - INTERVAL '1' DAY AND create_time < to_date('2014-07-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) tt2 ON tt1.PAYMENT_FLOW_NO = tt2.business_flow_no WHERE tt2.business_flow_no IS NOT NULL) t2 ON t1.order_flow_no = t2.orderform_flow_no WHERE t2.orderform_flow_no IS NOT NULL
注意由于tb_fund_flow 是按月分区表，这里查询一天的数据，必然走index比较快，可见统计信息错误，由于系统所有sql语句都在1秒内出结果，我没有去收集统计信息，于是加了这个hint /*+ index(f IDX_FUNDFLOW_CRTTIME) */ ，下面是最终的执行计划
---------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 327 | | 415K (1)| 01:23:07 | | ||* 1 | HASH JOIN | | 1 | 327 | | 415K (1)| 01:23:07 | | || 2 | PARTITION RANGE SINGLE | | 1 | 73 | | 0 (0)| 00:00:01 | 12 | 12 || 3 | VIEW | | 1 | 73 | | 0 (0)| 00:00:01 | | ||* 4 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_RECHARGE | 1 | 83 | | 0 (0)| 00:00:01 | 12 | 12 ||* 5 | INDEX RANGE SCAN | TB_RECHARGE_I5 | 1 | | | 0 (0)| 00:00:01 | 12 | 12 || 6 | VIEW | | 6740 | 1671K| | 415K (1)| 01:23:07 | | ||* 7 | HASH JOIN | | 6740 | 2349K| | 415K (1)| 01:23:07 | | ||* 8 | HASH JOIN RIGHT ANTI | | 990 | 304K| 2656K| 41160 (1)| 00:08:14 | | || 9 | TABLE ACCESS FULL | TB_REFUND | 150K| 884K| | 1402 (1)| 00:00:17 | | || 10 | PARTITION RANGE SINGLE | | 98977 | 29M| | 38125 (1)| 00:07:38 | 8 | 8 ||* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_PAYMENT | 98977 | 29M| | 38125 (1)| 00:07:38 | 8 | 8 ||* 12 | INDEX RANGE SCAN | IDX_PAYMENT_CRTTIME | 98977 | | | 1550 (1)| 00:00:19 | 8 | 8 || 13 | PARTITION RANGE SINGLE | | 673K| 26M| | 374K (1)| 01:14:53 | 8 | 8 ||* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FUND_FLOW | 673K| 26M| | 374K (1)| 01:14:53 | 8 | 8 ||* 15 | INDEX RANGE SCAN | IDX_FUNDFLOW_CRTTIME | 697K| | | 12504 (1)| 00:02:31 | 8 | 8 |---------------------------------------------------------------------------------------------------------------------------------------
添加了no_merge Hint后可以看到id等于3和6出现了view关键字，达到了最后sql写法的目的，先过滤大数据在join，最后这个sql 1s出了结果。开发傻眼了