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 emp@my_db_link remote_e
join dept@my_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 emp@my_db_link remote_e
join dept@my_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 collocated_sqf@my_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 )