| 26 | NESTED LOOPS | | | | | |
|* 27 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | | |
| 28 | TABLE ACCESS BY USER ROWID | T_COMPANY_ORGAN | | | | |
| 29 | NESTED LOOPS | | | | | |
| 30 | BUFFER SORT | | 7 | 70 | | |
| 31 | CONNECT BY PUMP | | | | | |
|* 32 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | | 1 (0)|
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_COMPANY_ORGAN" "T_COMPANY_ORGAN" WHERE
"T_COMPANY_ORGAN"."PARENT_ID"=NULL AND ("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)))
8 - access("SYS_ALIAS_1"."FEE_TYPE"="C"."TYPE_ID")
10 - access("SYS_ALIAS_1"."PAY_MODE"="G"."MODE_ID")
12 - filter("SYS_ALIAS_1"."CHANGE_ID" IS NOT NULL AND "SYS_ALIAS_1"."FEE_STATUS"=1 AND "SYS_ALIAS_1"."PAYMENT_ID"
IS NULL)
13 - access("SYS_ALIAS_1"."RECEIV_STATUS"='1')
15 - access("SYS_ALIAS_1"."POLICY_ID"="D"."POLICY_ID")
17 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))
19 - access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))
21 - access("TPC"."CHANGE_ID"="SYS_ALIAS_1"."CHANGE_ID")
23 - access("TCM"."POLICY_ID"="TPC"."POLICY_ID")
24 - filter("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)
25 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')
27 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')
32 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)
58 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2817 consistent gets
0 physical reads
0 redo size
2268 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
40 sorts (memory)
0 sorts (disk)
9 rows processed
最终这个SQL能在1秒以内跑完，逻辑读下降到2817 ，到此我就没继续优化了，这个时候停止优化吧，别的了强迫优化症
这个优化案例很简单，我都不好意思贴在博客上，通过这个文章你要学到的就是，如果子查询返回数据很少，那么不妨让它走filter
摘自 落落的专栏