Optimizer Restrictions for Non-Oracle Access

The following are optimizer restrictions for non-Oracle system access:

• There are no column statistics for remote objects. This can result in poor execution plans. Verify the execution plan and use hints to improve the plan.

• There is no optimizer hint to force a remote join. However, there is a remote query block optimization that can be used to rewrite the query slightly in order to get a remote join.

  The example from the previous section can be rewritten to the following form:

      SELECT v.ename, d.dname, d.deptno FROM dept d,
          (SELECT /*+ NO_MERGE */ 
           e.deptno deptno, e.ename ename emp@remote_db e, emp@remote_db f
               WHERE e.mgr = f.empno
               AND e.empno = f.empno;
          )
        WHERE v.deptno = d.deptno;
  

This example guarantees a remote join because it has been isolated in a nested query with the NO_MERGE hint.