You can't.
The query that supplies data to the insert has to be executed by the database
where the DML is taking place - which means the remote (to you) database. The
plan you see is the plan from the perspective of the remote (to you) database,
which is why it reports the table which you think of as local as being remote
(to the executing session).
If you want a plan which does ALL the work at the local (to you) database you
need to create a local (to you) view for the query (possibly with a no_merge
hint) and the do "insert into remote_table select * from local_view". You
might, however, manage to get away with creating a set of hints on the query
that force a nested loop join all the way through every table, then the plan
would be (just like operation 22 in the current plan) just "REMOTE", with the
join query being reported as the remote SQL.
Alternatively you could create a pipelined function that returns the data you
want and "insert ... select from pipelined function"
https://jonathanlewis.wordpress.com/2010/10/07/distributed-pipelines/
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
Sent: 30 May 2019 20:24
To: oracle-l-freelist
Subject: plan shows insert into remote table using all remote selects
SQL and ALL tables are on the local database. Insert is going to a remote
database. All tables are listed as remote in the below plan even though on the
local database. How can we force all the local tables to actually be local.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3968295312
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
|TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT REMOTE | | 9 | 8901 |
| 73074 (2)| 00:17:16 | | |
| 1 | LOAD TABLE CONVENTIONAL | PAY_TYPE_HRS_EMP | | |
| | | OR14 | |
|* 2 | COUNT STOPKEY | | | |
| | | | |
|* 3 | FILTER | | | |
| | | | |
|* 4 | HASH JOIN | | 571K| 538M|
| 73074 (2)| 00:17:16 | | |
| 5 | REMOTE | COMBHOMEACCT | 32797 | 992K|
| 45 (3)| 00:00:01 | ! | R->S |
|* 6 | HASH JOIN | | 116K| 105M|
| 73026 (2)| 00:17:15 | | |
| 7 | REMOTE | BASEWAGERTHIST | 21288 | 644K|
| 45 (3)| 00:00:01 | ! | R->S |
|* 8 | HASH JOIN | | 36257 | 32M|
| 72981 (2)| 00:17:15 | | |
| 9 | REMOTE | PERSONSTATUSMM | 14743 | 633K|
| 45 (3)| 00:00:01 | ! | R->S |
|* 10 | HASH JOIN | | 16393 | 13M|
| 72936 (2)| 00:17:14 | | |
| 11 | REMOTE | PAYCODE | 135 | 10530 |
| 3 (0)| 00:00:01 | ! | R->S |
|* 12 | HASH JOIN | | 16393 | 12M|
4592K| 72933 (2)| 00:17:14 | | |
| 13 | REMOTE | LABORACCT | 11925 | 4448K|
| 89 (0)| 00:00:02 | ! | R->S |
|* 14 | HASH JOIN | | 16393 | 6771K|
| 72617 (2)| 00:17:10 | | |
|* 15 | HASH JOIN OUTER | | 1423 | 466K|
| 120 (1)| 00:00:02 | | |
|* 16 | HASH JOIN OUTER | | 763 | 227K|
| 100 (1)| 00:00:02 | | |
|* 17 | HASH JOIN | | 763 | 204K|
| 90 (2)| 00:00:02 | | |
|* 18 | HASH JOIN RIGHT OUTER| | 763 | 132K|
| 46 (3)| 00:00:01 | | |
| 19 | REMOTE | PAYRULE | 1 | 31 |
| 3 (0)| 00:00:01 | ! | R->S |
|* 20 | HASH JOIN | | 763 | 109K|
| 42 (0)| 00:00:01 | | |
| 21 | REMOTE | PAYRULEIDS | 14 | 364 |
| 3 (0)| 00:00:01 | ! | R->S |
| 22 | REMOTE | | 1 | 40 |
| 5 (0)| 00:00:01 | ! | R->S |
| 23 | REMOTE | PERSON | 6666 | 624K|
| 44 (0)| 00:00:01 | ! | R->S |
| 24 | REMOTE | ASSIGNFTE | 6664 | 201K|
| 10 (0)| 00:00:01 | ! | R->S |
| 25 | REMOTE | BADGEASSIGN | 12424 | 376K|
| 20 (0)| 00:00:01 | ! | R->S |
| 26 | REMOTE | WFCTOTAL | 30M| 2549M|
| 72379 (2)| 00:17:06 | ! | R->S |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<10)
3 - filter(TO_DATE('4-MAY-19')>=TO_DATE('12-JAN-19'))
4 - access("A13"."EMPLOYEEID"="A8"."EMPLOYEEID")
6 - access("A13"."EMPLOYEEID"="A12"."EMPLOYEEID")
8 - access("A14"."PERSONID"="A11"."PERSONID")
10 - access("A16"."PAYCODEID"="A15"."PAYCODEID")
12 - access("A16"."LABORACCTID"="A4"."LABORACCTID")
14 - access("A16"."EMPLOYEEID"="A13"."EMPLOYEEID")
filter(TRUNC(INTERNAL_FUNCTION("A16"."STARTDTM"))>=TRUNC(INTERNAL_FUNCTION("A2"."START_DT"))
AND
TRUNC(INTERNAL_FUNCTION("A16"."STARTDTM"))<=TRUNC(INTERNAL_FUNCTION("A2"."END_DT")))
15 - access("A14"."PERSONID"="A9"."PERSONID"(+))
16 - access("A14"."PERSONID"="A10"."PERSONID"(+))
17 - access("A14"."PERSONID"="A13"."EMPLOYEEID")
18 - access("A6"."PAYRULEID"="A5"."PAYRULEID"(+))
20 - access("A7"."PAYRULEID"="A6"."PAYRULEID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT "EMPLOYEEID","EFFECTIVEDTM","EXPIRATIONDTM" FROM
"TKCSOWNER"."COMBHOMEACCT" "A8" WHERE
"EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
7 - SELECT "EMPLOYEEID","EFFECTIVEDTM","EXPIRATIONDTM" FROM
"TKCSOWNER"."BASEWAGERTHIST" "A12" WHERE
"EXPIRATIONDTM">=:1 AND "EFFECTIVEDTM"<=:2 (accessing '!' )
9 - SELECT "PERSONID","EMPLOYMENTSTATID","EFFECTIVEDTM","EXPIRATIONDTM" FROM
"TKCSOWNER"."PERSONSTATUSMM" "A11"
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
WHERE "EMPLOYMENTSTATID">0 AND "EXPIRATIONDTM">=:1 AND
"EFFECTIVEDTM"<=:2 (accessing '!' )
11 - SELECT "PAYCODEID","NAME","IS_MONEYCAT" FROM "TKCSOWNER"."PAYCODE" "A15"
WHERE "IS_MONEYCAT"=0 (accessing '!' )
13 - SELECT
"LABORACCTID","LABORLEV1NM","LABORLEV2NM","LABORLEV1DSC","LABORLEV2DSC" FROM
"TKCSOWNER"."LABORACCT"
"A4" (accessing '!' )
19 - SELECT "PAYRULEID","EFFECTIVEDTM","EXPIRATIONDTM" FROM
"TKCSOWNER"."PAYRULE" "A5" WHERE
:1<=NVL("EXPIRATIONDTM",:2+1) AND :3>=NVL("EFFECTIVEDTM",:4-1)
(accessing '!' )
21 - SELECT "PAYRULEID","NAME" FROM "TKCSOWNER"."PAYRULEIDS" "A6" (accessing
'!' )
22 - SELECT
"A1"."PAYRULEID","A1"."START_DT","A1"."END_DT","A1"."END_DATE","A2"."EMPLOYEEID","A2"."PAYRULEID","A3"."
EMPLOYEEID","A4"."EMPLOYEEID","A4"."PAYRULEID","A4"."EFFECTIVEDTM","A4"."EXPIRATIONDTM"
FROM "TKCSOWNER"."PAYPERIOD"
"A1","TKCSOWNER"."ASSIGNPAYRULE" "A2","TKCSOWNER"."WTKEMPLOYEE"
"A3","TKCSOWNER"."ASSIGNPAYRULE" "A4" WHERE
"A4"."EXPIRATIONDTM">=SYSDATE@! AND "A4"."EFFECTIVEDTM"<=SYSDATE@! AND
"A3"."EMPLOYEEID"="A4"."EMPLOYEEID" AND
"A3"."EMPLOYEEID"="A2"."EMPLOYEEID" AND
"A2"."PAYRULEID"="A1"."PAYRULEID" AND TRUNC("A1"."END_DATE")>='12-JAN-19' AND
TRUNC("A1"."END_DATE")<='4-MAY-19' (accessing '!' )
23 - SELECT "PERSONID","PERSONNUM","FULLNM" FROM "TKCSOWNER"."PERSON" "A14"
(accessing '!' )
24 - SELECT "PERSONID","EFFECTIVEDTM","EXPIRATIONDTM" FROM
"TKCSOWNER"."ASSIGNFTE" "A10" WHERE "EXPIRATIONDTM">=:1
AND "EFFECTIVEDTM"<=:2 (accessing '!' )
25 - SELECT "PERSONID","EFFECTIVEDTM","EXPIRATIONDTM" FROM
"TKCSOWNER"."BADGEASSIGN" "A9" WHERE "EXPIRATIONDTM">=:1
AND "EFFECTIVEDTM"<=:2 (accessing '!' )
26 - SELECT
"WFCTOTALID","TIMESHEETITEMID","EMPLOYEEID","DURATIONSECSQTY","WAGEAMT","MONEYAMT","APPLYDTM","LABORACCT
ID","PAYCODEID","STARTDTM","STIMEZONEID","ADJSTARTDTM","ADJAPPLYDTM","ENDDTM","UPDATEDTM","TOTALEDVERSION","ACCTAPPROV
ALNUM" FROM "TKCSOWNER"."WFCTOTAL" "A16" (accessing '!' )
Note
-----
- fully remote statement
98 rows selected.
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
--
//www.freelists.org/webpage/oracle-l
