1.未加hint前
SQL> EXPLAIN PLAN FOR
2 SELECT C.CORRESPONDORGID ORGID,3 A.TYPENAME,
4 B.PORTCODE RMNAME,
5 B.PORTCODE RMENTITYCODE,
6 A.OCFCODE RMOWNENTITYCODE
7 FROM V_OCF_FAC A, OPT_OCFPORT B, BAS_STATION C
8 WHERE A.OCFID = B.OCFID
9 AND A.STATIONID = C.FACID
10 AND B.PORTID = 1950004541119;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 655622816
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1005 | 368 (2)| 00:00:04 |
| 1 | NESTED LOOPS | | 5 | 1005 | 368 (2)| 00:00:04 |
| 2 | NESTED LOOPS | | 5 | 945 | 363 (2)| 00:00:04 |
| 3 | TABLE ACCESS BY INDEX ROWID| OPT_OCFPORT | 1 | 26 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_OPT_OCFPORT | 1 | | 2 (0)| 00:00:01 |
|* 5 | VIEW | V_OCF_FAC | 5 | 815 | 360 (2)| 00:00:04 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS FULL | OPT_OCF | 25992 | 1319K| 331 (2)| 00:00:04 |
| 8 | TABLE ACCESS FULL | CAB_CCF | 739 | 29560 | 8 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | OPT_JOINT | 3768 | 154K| 21 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | BAS_STATION | 1 | 12 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_BAS_STATION | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
4 - access("B"."PORTID"=1950004541119)
5 - filter("A"."OCFID"="B"."OCFID")
11 - access("A"."STATIONID"="C"."FACID")
Note
-----
- 'PLAN_TABLE' is old version
29 rows selected.
2.加了hint后
**********************************************************************************************************************
SQL> explain plan for
2 SELECT /*+ PUSH_PRED(A) */ C.CORRESPONDORGID ORGID,
3 A.TYPENAME,
4 B.PORTCODE RMNAME,
5 B.PORTCODE RMENTITYCODE,
6 A.OCFCODE RMOWNENTITYCODE
7 FROM V_OCF_FAC A, OPT_OCFPORT B, BAS_STATION C
8 WHERE A.OCFID = B.OCFID
9 AND A.STATIONID = C.FACID
10 AND B.PORTID = 1950004541119;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3253457126
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1065 | 16281 (1)| 00:02:30 |
| 1 | NESTED LOOPS | | 5 | 1065 | 16281 (1)| 00:02:30 |
| 2 | NESTED LOOPS | | 2708 | 148K| 19 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | OPT_OCFPORT | 1 | 35 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_OPT_OCFPORT | 1 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | BAS_STATION | 2708 | 56868 | 16 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 6 | VIEW | V_OCF_FAC | 1 | 157 | 6 (0)| 00:00:01 |
| 7 | UNION ALL PUSHED PREDICATE | | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| OPT_OCF | 1 | 52 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_OPT_OCF | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| CAB_CCF | 1 | 40 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_CAB_CCF | 1 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| OPT_JOINT | 1 | 42 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_OPT_JOINT | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
4 - access("B"."PORTID"=1950004541119)
8 - filter("T"."STATIONID"="C"."FACID")
9 - access("T"."OCFID"="B"."OCFID")
10 - filter("C"."STATIONID"="C"."FACID")
11 - access("C"."CCFID"="B"."OCFID")
12 - filter("T"."STATIONID"="C"."FACID")
13 - access("T"."JOINTID"="B"."OCFID")