使用push_subq优化SQL
需要优化的SQL
?
该条SQL执行后的结果集只有2条数据，WF_DOC_GW_SHOUWEN数据量有24万，NDOCID是主键，从执行计划看，走的是全表扫描，和预期结果不符合，应该走的是唯一索引才对，优化的思路是先关联WF_DOC_GW和WF_DOC_GW_INNER，计算出结果集后，再关联WF_DOC_GW_SHOUWEN，这样就会走唯一索引，使用push_subq可以达到想要到想过，修改后的SQL如下
------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 13 | 9984 | 2518 (1)| 00:00:31 ||* 1 | VIEW | | 13 | 9984 | 2518 (1)| 00:00:31 ||* 2 | COUNT STOPKEY | | | | | || 3 | VIEW | | 244 | 179K| 2518 (1)| 00:00:31 ||* 4 | SORT UNIQUE STOPKEY | | 244 | 63928 | 2517 (1)| 00:00:31 || 5 | WINDOW BUFFER | | 244 | 63928 | 2518 (1)| 00:00:31 || 6 | NESTED LOOPS | | 244 | 63928 | 2516 (1)| 00:00:31 ||* 7 | TABLE ACCESS FULL | WF_DOC_GW | 242 | 27588 | 1547 (2)| 00:00:19 ||* 8 | TABLE ACCESS BY INDEX ROWID | WF_DOC_GW_INNER | 1 | 17 | 2 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | IDX_INNER_SRCDOCID | 2 | | 1 (0)| 00:00:01 || 10 | VIEW | | 1 | 148 | 4 (0)| 00:00:01 || 11 | UNION ALL PUSHED PREDICATE | | | | | || 12 | TABLE ACCESS BY INDEX ROWID| WF_DOC_GW_SHOUWEN | 1 | 49 | 2 (0)| 00:00:01 ||* 13 | INDEX UNIQUE SCAN | PK_WF_DOC_GW_SHOUWEN | 1 | | 1 (0)| 00:00:01 || 14 | TABLE ACCESS BY INDEX ROWID| WF_DOC_GW_FAWEN | 1 | 38 | 2 (0)| 00:00:01 ||* 15 | INDEX UNIQUE SCAN | PK_WF_DOC_GW_FAWEN | 1 | | 1 (0)| 00:00:01 |?
优化前的逻辑读是12000，优化后的逻辑读降到了5000，还是蛮有效果的
