I am pulling large amounts of data across a database link from the production environment to the reporting environment. The views doing the work access several remote tables every time they are executed. I came across the driving site hint in my search for performance improvements, and it appears useful when combining one local table and one remote table. Can this work when accessing multiple local and remote tables?
Very simple example:
SELECT
L1.DELIVERY_NO,
L1.CLIENT,
L1.DOC_TYPE,
L2.DISTRICT,
L2.WAREHOUSE,
R1.SHIPMENTDATE,
R1.SALESORDERNUMBER,
R2.COST,
R2.DISTANCE
FROM
LOCAL_TABLE1 L1,
LOCAL_TABLE2 L2,
REMOTE_TABLE1@REMOTE R1,
REMOTE_TABLE2@REMOTE R2
WHERE
L1.KEY = L2.KEY
AND L1.FOREIGN_KEY = R1.KEY
AND L1.FOREIGN_KEY = R2.KEY
If I referenced only one of the remote tables (SELECT /*DRIVING_SITE (R1)*/ ...), would the driving site hint move all of the work to the remote server? Can you reference 2+ tables in the driving site hint (SELECT /*DRIVING_SITE (R1,R2)*/ ...)?
The end users reference local tables, so their reporting is not affected. However, populating the local tables using views like this one can take from 45 minutes to 2 hours.
Any help is greatly appreciated.