oracle通过dblink查表卡住,关于通过dblink查询导致查询数据异常问题请教
两个SQL的执行计划确实存在较大的变化，如下为包含本地表的执行计划
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3777546417
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 5 (20)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | REMOTE | | 59 | 1829 | 60 (0)| 00:00:01 | DBLIN~ | R->S |
|* 3 | COUNT STOPKEY | | | | | | | |
| 4 | REMOTE | | 59 | 1829 | 60 (0)| 00:00:01 | DBLIN~ | R->S |
|* 5 | COUNT STOPKEY | | | | | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | REMOTE | | 59 | 1829 | 60 (0)| 00:00:01 | DBLIN~ | R->S |
| 7 | HASH UNIQUE | | 1 | 64 | 5 (20)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 64 | 4 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 38 | 2 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN| PK_DPCPOLICYINFO | 1 | 17 | 0 (0)| 00:00:01 | | |
| 11 | REMOTE | LCCONTPLAN | 1 | 21 | 2 (0)| 00:00:01 | DBLIN~ | R->S |
| 12 | REMOTE | LCCONTPLANDUTYPARAM | 1 | 26 | 2 (0)| 00:00:01 | DBLIN~ | R->S |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
5 - filter(ROWNUM=1)
10 - access("C2"."POLICYNO"='2011000000292099')
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT "A1"."GRPCONTNO","A1"."CONTNO","A1"."CONTPLANCODE","A2"."PREM","A2"."CONTNO","A2"."DUT
YCODE" FROM "LCINSURED" "A1","LCDUTY" "A2" WHERE "A1"."CONTNO"="A2"."CONTNO" AND
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
"A1"."GRPCONTNO"=:1 AND "A1"."CONTPLANCODE"=:2 (accessing 'DBLINKDP' )
4 - SELECT "A1"."GRPCONTNO","A1"."CONTNO","A1"."CONTPLANCODE","A2"."AMNT","A2"."CONTNO","A2"."DUT
YCODE" FROM "LCINSURED" "A1","LCDUTY" "A2" WHERE "A1"."CONTNO"="A2"."CONTNO" AND
"A1"."GRPCONTNO"=:1 AND "A1"."CONTPLANCODE"=:2 (accessing 'DBLINKDP' )
6 - SELECT "A1"."GRPCONTNO","A1"."CONTNO","A1"."CONTPLANCODE","A2"."AMNT","A2"."CONTNO","A2"."DUT
YCODE" FROM "LCINSURED" "A1","LCDUTY" "A2" WHERE "A1"."CONTNO"="A2"."CONTNO" AND
"A1"."GRPCONTNO"=:1 AND "A1"."CONTPLANCODE"=:2 (accessing 'DBLINKDP' )
11 - SELECT "GRPCONTNO","CONTPLANCODE","PLANTYPE" FROM "LCCONTPLAN" "C0" WHERE
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
"GRPCONTNO"='2011000000292099' AND "PLANTYPE"='0' (accessing 'DBLINKDP' )
12 - SELECT "GRPCONTNO","CONTPLANCODE","DUTYCODE" FROM "LCCONTPLANDUTYPARAM" "SYS_ALIAS_9" WHERE
"GRPCONTNO"='2011000000292099' AND "CONTPLANCODE"=:1 (accessing 'DBLINKDP' )
如下为不包含本地表的执行计划
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3116312589
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 1 | 49 | 6 (17)| 00:00:01 | |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | LCDUTY | 1 | 30 | 3 (0)| 00:00:01 | TEST11 |
| 3 | NESTED LOOPS | | 59 | 4012 | 464 (1)| 00:00:06 | |
|* 4 | TABLE ACCESS BY INDEX ROWID| LCINSURED | 59 | 2242 | 302 (1)| 00:00:04 | TEST11 |
|* 5 | INDEX RANGE SCAN | IDX_GRPCONTNO1 | 1584 | | 9 (0)| 00:00:01 | TEST11 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 6 | INDEX RANGE SCAN | LCDUTY_IDX01 | 3 | | 2 (0)| 00:00:01 | TEST11 |
|* 7 | COUNT STOPKEY | | | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | LCDUTY | 1 | 30 | 3 (0)| 00:00:01 | TEST11 |
| 9 | NESTED LOOPS | | 59 | 4012 | 464 (1)| 00:00:06 | |
|* 10 | TABLE ACCESS BY INDEX ROWID| LCINSURED | 59 | 2242 | 302 (1)| 00:00:04 | TEST11 |
|* 11 | INDEX RANGE SCAN | IDX_GRPCONTNO1 | 1584 | | 9 (0)| 00:00:01 | TEST11 |
|* 12 | INDEX RANGE SCAN | LCDUTY_IDX01 | 3 | | 2 (0)| 00:00:01 | TEST11 |
|* 13 | COUNT STOPKEY | | | | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | LCDUTY | 1 | 30 | 3 (0)| 00:00:01 | TEST11 |
| 15 | NESTED LOOPS | | 59 | 4012 | 464 (1)| 00:00:06 | |
|* 16 | TABLE ACCESS BY INDEX ROWID| LCINSURED | 59 | 2242 | 302 (1)| 00:00:04 | TEST11 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 17 | INDEX RANGE SCAN | IDX_GRPCONTNO1 | 1584 | | 9 (0)| 00:00:01 | TEST11 |
|* 18 | INDEX RANGE SCAN | LCDUTY_IDX01 | 3 | | 2 (0)| 00:00:01 | TEST11 |
| 19 | HASH UNIQUE | | 1 | 49 | 6 (17)| 00:00:01 | |
| 20 | NESTED LOOPS | | 1 | 49 | 5 (0)| 00:00:01 | |
|* 21 | INDEX RANGE SCAN | PK_LCCONTPLAN | 1 | 22 | 3 (0)| 00:00:01 | TEST11 |
|* 22 | INDEX RANGE SCAN | PK_LCCONTPLANDUTYPARAM | 6 | 162 | 2 (0)| 00:00:01 | TEST11 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("A7"."DUTYCODE"=:B1)
4 - filter("A8"."CONTPLANCODE"=:B1)
5 - access("A8"."GRPCONTNO"=:B1)
6 - access("A8"."CONTNO"="A7"."CONTNO")
7 - filter(ROWNUM=1)
8 - filter("A5"."DUTYCODE"=:B1)
10 - filter("A6"."CONTPLANCODE"=:B1)
11 - access("A6"."GRPCONTNO"=:B1)
12 - access("A6"."CONTNO"="A5"."CONTNO")
13 - filter(ROWNUM=1)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
14 - filter("A3"."DUTYCODE"=:B1)
16 - filter("A4"."CONTPLANCODE"=:B1)
17 - access("A4"."GRPCONTNO"=:B1)
18 - access("A4"."CONTNO"="A3"."CONTNO")
21 - access("A2"."GRPCONTNO"='2011000000292099' AND "A2"."PLANTYPE"='0')
filter("A2"."PLANTYPE"='0')
22 - access("A1"."GRPCONTNO"='2011000000292099' AND "A1"."CONTPLANCODE"="A2"."CONTPLANCODE")
filter("A1"."CONTPLANCODE"="A2"."CONTPLANCODE")