Home » RDBMS Server » Performance Tuning » FOR Loop and Driving Site (Oracle 11g)
FOR Loop and Driving Site [message #566622] Tue, 18 September 2012 02:52 Go to next message
wmgonzalbo
Messages: 95
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! Smile

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 Go to previous message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: inbound connection timed out (ORA-3136)
Next Topic: Remove SQL*Net waitevent from top 5 list
Goto Forum:
  


Current Time: Sun Apr 20 10:56:33 CDT 2014

Total time taken to generate the page: 0.08837 seconds