Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Distributed sql query through multiple databases

User_37KZCJul 22 2020 — edited Jul 23 2020

Hello,

In order to optimise a distributed query, I used the "Driving_site" hint

select /*+ DRIVING_SITE (s1) */ * from  Table1 s1 WHERE condition in (select att1 from local_table) ; -- query n°1

select /*+ RULE DRIVING_SITE (s2) */  * from  Table2 s2 where  condition in (select att1 from local_table); --query n°2

These two queries work fine but once I join the to obtain the desired result in this way:

select * from

select /*+ DRIVING_SITE (s1) */ * from  Table1 s1 WHERE condition in (select att1 from local_table) ,

select /*+ RULE DRIVING_SITE (s2) */  * from  Table2 s2 where  condition in (select att1 from local_table)

where att_table_1 = att_table_2

I don't get the desired execution_plan and the query is rewritten using both subqueries in  one of the servers, and the query execution runs in an endless way.

Do anyone know the correct fix for such issue?

Comments
Post Details
Added on Jul 22 2020
5 comments
601 views