有一个语句,使用了hint /*+rule+*/ 目前的执行计划如下:
SQL_ID 2227h5k32vr2b, child number 0
-------------------------------------
SELECT * FROM V_WSSB_YWSJ_ZJB A WHERE A.dwshbzh ='00000919'
Plan hash value: 3729893328
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7178 |00:00:00.21 | 55803 |
| 1 | NESTED LOOPS OUTER | | 1 | 7178 |00:00:00.21 | 55803 |
| 2 | NESTED LOOPS OUTER | | 1 | 7174 |00:00:00.14 | 39243 |
| 3 | NESTED LOOPS OUTER | | 1 | 7174 |00:00:00.09 | 24122 |
| 4 | NESTED LOOPS | | 1 | 7174 |00:00:00.02 | 2123 |
| 5 | TABLE ACCESS BY INDEX ROWID| AE01 | 1 | 1 |00:00:00.01 | 4 |
|* 6 | INDEX UNIQUE SCAN | INDEX_AE01_UNIQUE | 1 | 1 |00:00:00.01 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID| SJCJ_YWJL | 1 | 7174 |00:00:00.02 | 2119 |
|* 8 | INDEX RANGE SCAN | IND_SJCJ_YWJL_SHBXDJZBM | 1 | 7174 |00:00:00.01 | 502 |
| 9 | TABLE ACCESS BY INDEX ROWID | SJCJ_TJJL | 7174 | 7173 |00:00:00.07 | 21999 |
|* 10 | INDEX UNIQUE SCAN | TJJL_PK | 7174 | 7173 |00:00:00.04 | 14826 |
| 11 | TABLE ACCESS BY INDEX ROWID | AC01 | 7174 | 335 |00:00:00.04 | 15121 |
|* 12 | INDEX UNIQUE SCAN | PK_AC01 | 7174 | 335 |00:00:00.03 | 14786 |
| 13 | TABLE ACCESS BY INDEX ROWID | SJCJ_GRZL | 7174 | 1620 |00:00:00.06 | 16560 |
|* 14 | INDEX RANGE SCAN | IND_SJCJ_GRZL_YWSXH | 7174 | 1620 |00:00:00.05 | 14940 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AAB999"='00000919')
8 - access("AA"."SHBXDJZBM"="AAB999")
10 - access("AA"."TJSXH"="E"."TJSXH")
12 - access("AA"."GRSXH"="AAC001")
14 - access("AA"."YWSXH"="C"."YWSXH")
Note
-----
- rule based optimizer used (consider using cbo)
可以看出都走到了索引;
我重新建立视图--只是去除了 hint;之后执行计划如下;
SQL_ID 4pchna2wwcfqr, child number 0
-------------------------------------
SELECT * FROM V_WSSB_YWSJ_ZJB_TEST A WHERE A.dwshbzh ='00000919'
Plan hash value: 2571714066
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7178 |00:00:00.50 | 46026 | | | |
| 1 | NESTED LOOPS OUTER | | 1 | 1891 | 7178 |00:00:00.50 | 46026 | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 1891 | 7174 |00:00:00.42 | 29470 | | | |
|* 3 | HASH JOIN OUTER | | 1 | 1891 | 7174 |00:00:00.33 | 7471 | 1632K| 1004K| 1602K (0)|
| 4 | NESTED LOOPS | | 1 | 1891 | 7174 |00:00:00.01 | 1247 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| AE01 | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 6 | INDEX UNIQUE SCAN | INDEX_AE01_UNIQUE | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| SJCJ_YWJL | 1 | 1891 | 7174 |00:00:00.01 | 1243 | | | |
|* 8 | INDEX RANGE SCAN | IND_SJCJ_YWJL_SHBXDJZBM | 1 | 1891 | 7174 |00:00:00.01 | 24 | | | |
| 9 | TABLE ACCESS FULL | AC01 | 1 | 346K| 346K|00:00:00.01 | 6224 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | SJCJ_TJJL | 7174 | 1 | 7173 |00:00:00.08 | 21999 | | | |
|* 11 | INDEX UNIQUE SCAN | TJJL_PK | 7174 | 1 | 7173 |00:00:00.05 | 14826 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | SJCJ_GRZL | 7174 | 1 | 1620 |00:00:00.08 | 16556 | | | |
|* 13 | INDEX RANGE SCAN | IND_SJCJ_GRZL_YWSXH | 7174 | 1 | 1620 |00:00:00.06 | 14936 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."GMSFHM"="B"."AAC002")
6 - access("D"."AAB999"='00000919')
8 - access("A"."SHBXDJZBM"='00000919')
11 - access("A"."TJSXH"="E"."TJSXH")
13 - access("A"."YWSXH"="C"."YWSXH")
看出 这行| 9 | TABLE ACCESS FULL | AC01 | 1 | 346K| 346K|00:00:00.01 | 6224 | | | |
与之前结果使用了hash join连接并使用了全表扫描;
表 AC01 有索引 aac002和之前的AAC001 依然走了全表扫描,因此比rbo速度慢了一些,
请问此处如何做优化;
非常感谢;