Allow specifying database link only once for collocated inline view in SQL

Kim Berg Hansen

Given a distributed query like this:

select
  remote_d.loc
 , remote_e.empno
 , remote_e.ename
 , remote_e.job as remote_job
 , local_e.job as local_job
from [email protected]_db_link remote_e
join [email protected]_db_link remote_d
  on remote_d.deptno = remote_e.deptno
left outer join emp local_e
  on local_e.empno = remote_e.empno
where remote_d.dname = 'SALES';

The documentation (https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/developing-applications-for-a-distributed-database-system.html#GUID-BE4D1049-BB3B-4F5F-BB2C-4CC0CD9407E7) recommends using collocated inline views to optimize remote access like this:

select
  collocated_iv.loc
 , collocated_iv.empno
 , collocated_iv.ename
 , collocated_iv.job as remote_job
 , local_e.job    as local_job
from (
  select
   remote_d.loc
  , remote_e.empno
  , remote_e.ename
  , remote_e.job
  from [email protected]_db_link remote_e
  join [email protected]_db_link remote_d
   on remote_d.deptno = remote_e.deptno
  where remote_d.dname = 'SALES'
) collocated_iv
left outer join emp local_e
  on local_e.empno = collocated_iv.empno;

My proposed idea is to allow a syntax for example like this for collocated inline views:

select
  collocated_iv.loc
 , collocated_iv.empno
 , collocated_iv.ename
 , collocated_iv.job as remote_job
 , local_e.job    as local_job
from (
  select
   remote_d.loc
  , remote_e.empno
  , remote_e.ename
  , remote_e.job
  from emp remote_e
  join dept remote_d
   on remote_d.deptno = remote_e.deptno
  where remote_d.dname = 'SALES'
)@my_db_link collocated_iv
left outer join emp local_e
  on local_e.empno = collocated_iv.empno;

The idea being that the database link is specified once for the entire inline view, so that all table/view/object references within the inline view automatically are name resolved as <objname>@<dblink>.

Similar syntax could be used in WITH clause for collocated subquery factoring:

with collocated_sqf as (
  select
   remote_d.loc
  , remote_e.empno
  , remote_e.ename
  , remote_e.job
  from emp remote_e
  join dept remote_d
   on remote_d.deptno = remote_e.deptno
  where remote_d.dname = 'SALES'
)@my_db_link
select
  csqf.loc
 , csqf.empno
 , csqf.ename
 , csqf.job  as remote_job
 , local_e.job as local_job
from collocated_sqf csqf
left outer join emp local_e
  on local_e.empno = csqf.empno;

Or alternatively specified together with the subquery name:

with [email protected]_db_link as (
  select
   remote_d.loc
  , remote_e.empno
  , remote_e.ename
  , remote_e.job
  from emp remote_e
  join dept remote_d
   on remote_d.deptno = remote_e.deptno
  where remote_d.dname = 'SALES'
)
select
  csqf.loc
 , csqf.empno
 , csqf.ename
 , csqf.job  as remote_job
 , local_e.job as local_job
from collocated_sqf csqf
left outer join emp local_e
  on local_e.empno = csqf.empno;

In all cases I suggest that this syntax merely function as a shortcut for name resolution making it easier for developers, but that the optimizer considers the second and third code example in this post to be identical, so the optimizer still does whatever optimizations it does to decide whether to ship the entire inline view to remote site for execution or not.

In other words, to try and influence the optimizer it would still be relevant to use for example NO_MERGE hint for collocated inline views if desired:

select
  collocated_iv.loc
 , collocated_iv.empno
 , collocated_iv.ename
 , collocated_iv.job as remote_job
 , local_e.job    as local_job
from (
  select /*+ no_merge */
   remote_d.loc
  , remote_e.empno
  , remote_e.ename
  , remote_e.job
  from emp remote_e
  join dept remote_d
   on remote_d.deptno = remote_e.deptno
  where remote_d.dname = 'SALES'
)@my_db_link collocated_iv
left outer join emp local_e
  on local_e.empno = collocated_iv.empno;

This way the syntax idea will not require optimization changes, merely name resolution will automatically resolve identifiers within the inline view in the remote database.

(Of course the optimizer may take into consideration that this syntax means that the developer considers collocating the inline view to be a good idea 😉)

Cherif bh

Hello Kim,

Thanks for the idea. I think this would be helpful in case we need to execute the entire bloc with on remote DB and avoid execution plan change at least of The with bloc.

Currently we should put many hint no_merge  driving_site,materialize , ...

May be a hint would be more easy / more felexible to implement for Oracle and Oracle users.

For example

 with collocated_sqf as (

 select /*+ WITH_DB_LINK */

  remote_d.loc

 , remote_e.empno

 , remote_e.ename

 , remote_e.job

 from [email protected]_db_link remote_e

 join [email protected]_db_link remote_d

  on remote_d.deptno = remote_e.deptno

 where remote_d.dname = 'SALES'

)

select

 csqf.loc

 , csqf.empno

 , csqf.ename

 , csqf.job as remote_job

 , local_e.job as local_job

from collocated_sqf csqf

left outer join emp local_e

 on local_e.empno = csqf.empno;

Thanks