Search code examples
sqloracle-databaseoracle12c

driving_site hint for multiple remote tables

I have a query of the following format. It uses two remote tables and a local table.

SELECT *
FROM table1@db2 t1 INNER JOIN table2@db2 t2 -- two large remote tables on the same DB
  ON t1.id = t2.id
WHERE t1.prop = '1'
 AND t2.prop = '2'
 AND t1.prop2 IN (SELECT val FROM tinylocaltable)

I'm wondering how to properly use the DRIVING_SITE query hint to push the bulk of the work to db2 (i.e. ensure the join and conditions are applied on db2). Most of the examples I see of DRIVING_SITE reference only one remote table. Is SELECT /*+DRIVING_SITE(t1)*/ * sufficient or do I need to list both remote tables (t1 and t2) in the hint? If the latter, what is the proper syntax?

(If you're wondering why this isn't being executed on db2 to start with, it's because this is actually one UNION ALL section of a larger query, where the other UNION ALL sections use the local DB).

Solution

  • The DRIVING_SITE hint instructs the optimizer to execute the query at a different site than that selected by the database

    Your query uses

    FROM table1@db2 t1 INNER JOIN table2@db2 t2

    where both tables are on the same "different site", so 

    SELECT /*+ DRIVING_SITE(t1)*/

    should be OK (in my opinion. Can't find anything in documentation that would suggest different).