--------------------------原始SQL语句如下
SELECT COUNT(ID)
FROM apollo.ord_order_item
WHERE ID IN
(SELECT order_item_id
FROM apollo.rpt_order_item_match i
WHERE i.sign_sales_org_id IN
(SELECT
ID
FROM apollo.app_org b
WHERE full_id_path LIKE :1 || '%'
AND is_deleted = 'n')
AND i.payment_status = 'y'
AND i.gmt_payment_remit <= to_date(:2, 'yyyy-MM-dd hh24:MI:ss')
AND i.gmt_payment_remit > to_date(:3, 'yyyy-MM-dd hh24:MI:ss')
AND gmt_actual_begin <= to_date(:4, 'yyyy-MM-dd')
and gmt_actual_begin >= to_date(:5, 'yyyy-MM-dd')
AND product_code IN
(:6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46))
AND is_deleted = 'n'
AND biz_status = 'service'
AND payment_status = 'payment_success';
----------------------------默认的执行计划
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 20549 (2)| 00:04:07 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 5 | 195 | 20549 (2)| 00:04:07 |
| 4 | VIEW | VW_NSO_1 | 5 | 65 | 20538 (2)| 00:04:07 |
| 5 | HASH UNIQUE | | 5 | 520 | | |
|* 6 | FILTER | | | | | |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 5 | 520 | 20538 (2)| 00:04:07 |
|* 9 | TABLE ACCESS FULL | RPT_ORDER_ITEM_MATCH | 5 | 190 | 20533 (2)| 00:04:07 |
|* 10 | INDEX RANGE SCAN | APP_ORG_PK | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| APP_ORG | 1 | 66 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ORD_ORDER_ITEM_PK | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | ORD_ORDER_ITEM | 1 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
------------------------------分析
由于full_id_path LIKE :1采用了绑定变量，ORACLE按照5%的选择率去评估，对于这个查询来说，评估的过大了，实际只会返回几条记录。
本来应该是app_org作为驱动跟rpt_order_item_match来做nest loop，这个执行计划才最优秀。
-----------------------------解决办法一，通过hint cardinality(b 1)来解决
SELECT COUNT(ID)
FROM apollo.ord_order_item
WHERE ID IN
(SELECT order_item_id
FROM apollo.rpt_order_item_match i
WHERE i.sign_sales_org_id IN
(SELECT /*+ cardinality(b 1) */
ID
FROM apollo.app_org b
WHERE full_id_path LIKE :1 || '%'
AND is_deleted = 'n')
AND i.payment_status = 'y'
AND i.gmt_payment_remit <= to_date(:2, 'yyyy-MM-dd hh24:MI:ss')
AND i.gmt_payment_remit > to_date(:3, 'yyyy-MM-dd hh24:MI:ss')
AND gmt_actual_begin <= to_date(:4, 'yyyy-MM-dd')
and gmt_actual_begin >= to_date(:5, 'yyyy-MM-dd')
AND product_code IN
(:6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46))
AND is_deleted = 'n'
AND biz_status = 'service'
AND payment_status = 'payment_success';
----------------------------解决办法二，通过qb_name leading use_nl来解决
SELECT COUNT(ID)
FROM apollo.ord_order_item
WHERE ID IN
(SELECT /*+ leading(b@m i) use_nl(b@m i) */order_item_id
FROM apollo.rpt_order_item_match i
WHERE i.sign_sales_org_id IN
(SELECT /*+ qb_name(m) */
ID
FROM apollo.app_org b
WHERE full_id_path LIKE :1 || '%'
AND is_deleted = 'n')
AND i.payment_status = 'y'
AND i.gmt_payment_remit <= to_date(:2, 'yyyy-MM-dd hh24:MI:ss')
AND i.gmt_payment_remit > to_date(:3, 'yyyy-MM-dd hh24:MI:ss')
AND gmt_actual_begin <= to_date(:4, 'yyyy-MM-dd')
and gmt_actual_begin >= to_date(:5, 'yyyy-MM-dd')
AND product_code IN
(:6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46))
AND is_deleted = 'n'
AND biz_status = 'service'
AND payment_status = 'payment_success';
---------------------------增加hint后的执行计划
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 48368 (1)| 00:09:41 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 5 | 195 | 48368 (1)| 00:09:41 |
| 4 | VIEW | VW_NSO_1 | 5 | 65 | 48357 (1)| 00:09:41 |
| 5 | HASH UNIQUE | | 5 | 520 | | |
|* 6 | FILTER | | | | | |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 5 | 520 | 48357 (1)| 00:09:41 |
|* 9 | TABLE ACCESS BY INDEX ROWID| APP_ORG | 196 | 12936 | 71 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | APP_ORG_FIDP_IND | 71 | | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | RPT_ORDER_ITEM_MATCH_SSOID_IND | 1178 | | 4 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | RPT_ORDER_ITEM_MATCH | 1 | 38 | 865 (1)| 00:00:11 |
|* 13 | INDEX RANGE SCAN | ORD_ORDER_ITEM_PK | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | ORD_ORDER_ITEM | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 939 (1)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 39 | 939 (1)| 00:00:12 |
| 4 | VIEW | VW_NSO_1 | 1 | 13 | 936 (1)| 00:00:12 |
| 5 | HASH UNIQUE | | 1 | 104 | | |
|* 6 | FILTER | | | | | |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 1 | 104 | 936 (1)| 00:00:12 |
|* 9 | TABLE ACCESS BY INDEX ROWID| APP_ORG | 1 | 66 | 71 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | APP_ORG_FIDP_IND | 71 | | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | RPT_ORDER_ITEM_MATCH_SSOID_IND | 1178 | | 4 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | RPT_ORDER_ITEM_MATCH | 1 | 38 | 865 (1)| 00:00:11 |
|* 13 | INDEX RANGE SCAN | ORD_ORDER_ITEM_PK | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | ORD_ORDER_ITEM | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------