| FOR Loop and Driving Site [message #566622] |
Tue, 18 September 2012 02:52  |
wmgonzalbo
Messages: 98
Registered: November 2008
|
Member |
|
|
Hi Experts,
So usually joining a local table and a remote table (much larger table), the best practice is using a /*+ DRIVING_SITE(remote table) */ hint.
*CASE1:
INSERT INTO another_local_table
SELECT /*+ DRIVING_SITE(remote_table) */
FROM local_table
,remote_table@remotedb
WHERE join condition
*CASE2:
However I saw this particular code:
FOR x IN Select id From local_table l
LOOP
INSERT INTO another_local_table
SELECT /*+ DRIVING_SITE(remote_table) */
FROM remote_table@remotedb r
WHERE r.id = l.id
AND join conditions
END LOOP
So far I haven't seen the explain plan of both cases since most of the tables are Global temporary tables. But in terms of the logic of the two cases and the common best practice, logically doesn't CASE1 should have a better performance?
For me, its like taking a trip to a grocery. CASE1 buys all that you need, one time, it will take you a half-day trip perhaps. However CASE2 is like quickly buying a grocery item, one at a time, for several short trips. You'll save on gas on CASE1 right. Please forgive my faulty analogy. Cheers! 
Regards
[Updated on: Tue, 18 September 2012 02:54] Report message to a moderator |
|
|
|
| Re: FOR Loop and Driving Site [message #566634 is a reply to message #566622] |
Tue, 18 September 2012 04:21  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote: the best practice is using a /*+ DRIVING_SITE(remote table) */ hint.
Who say that?
If it was ALWAYS the best practice then this hint would not exist and Oracle will put it directly in its code.
Quote:However I saw this particular code
It is a silly code.
There is no join at all.
How a SQL statement can JOIN with a PL/SQL for loop?
It is just executing the same INSERT statement for all the values returned by the FOR cursor.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
