Oracle 跨db链路绑定变量的查询计划

嗨，汤姆，


我似乎无法理解为什么要遵循这种情况，以及如何避免这种情况。

我有一个巨大的查询，但为了简单起见，我将提供一个假人。

SELECT x, Y, Z
FROM schemaS.table1@Remote a inner join schemaS.table2@remote b on a.x = b.y
WHERE a.c = 'waarde1' OR b.h = 'Waarde2'

UNION ALL

SELECT x, Y, Z
FROM schemaT.table1@Remote a inner join schemaT.table2@remote b on a.x = b.y
WHERE a.c = 'waarde1' OR b.h = 'Waarde2'

如果您为as literal Waarde1和Waarde2提供了一个不存在的值; 您将获得0行的即时结果; => 我的目标!

现在，如果我采用相同的查询并用实际参数替换文字，则qryplan看起来完全不同。
在第一查询中，考虑完整查询以生成计划;
在第二种情况下; 我在远程上得到2个单独的查询，每个计划都像

plan 1
SELECT X,Y 
FROM schemaT.table1@Remote a WHERE a.x :=1

plan 2
SELECT Z
FROM schemaT.table2@Remote a WHERE b.y :=1

结果 ..超过30分钟返回0行。

对我的实例进行的快速测试表明，在两种情况下，12.1都支持完全远程运行计划

SQL>
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>
SQL> explain plan for
  2  select *
  3  from   t1@loopback inner join t1@loopback t1a on t1.object_id = t1a.object_id
  4  where  t1.object_name = 'XXXXX' or t1a.subobject_name = 'YYYY'
  5  union all
  6  select *
  7  from   t2@loopback inner join t2@loopback t2a on t2.data_object_id = t2a.data_object_id
  8  where  t2.object_name = 'XXXXX' or t2a.subobject_name = 'YYYY';

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1911198332

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     5 |  1150 |  2575   (1)| 00:00:01 |        |
|   1 |  UNION-ALL             |      |       |       |            |          |        |
|   2 |   CONCATENATION        |      |       |       |            |          |        |
|*  3 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  4 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|   5 |     TABLE ACCESS FULL  | T1   | 91774 |    10M|   429   (1)| 00:00:01 |   PDB1 |
|*  6 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  7 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|*  8 |     TABLE ACCESS FULL  | T1   | 91772 |    10M|   430   (1)| 00:00:01 |   PDB1 |
|*  9 |   HASH JOIN            |      |     1 |   230 |   858   (1)| 00:00:01 |        |
|* 10 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
|* 11 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   4 - filter("A4"."SUBOBJECT_NAME"='YYYY')
   6 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   7 - filter("A5"."OBJECT_NAME"='XXXXX')
   8 - filter(LNNVL("A4"."SUBOBJECT_NAME"='YYYY'))
   9 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
       filter("A3"."OBJECT_NAME"='XXXXX' OR "A2"."SUBOBJECT_NAME"='YYYY')
  10 - filter("A3"."DATA_OBJECT_ID" IS NOT NULL)
  11 - filter("A2"."DATA_OBJECT_ID" IS NOT NULL)

Note
-----
   - fully remote statement

35 rows selected.

SQL>
SQL> variable b1 varchar2(20)
SQL> variable b2 varchar2(20)
SQL> exec :b1 := 'XXXXX'; :b2 := 'YYYY';

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan for
  2  select *
  3  from   t1@loopback inner join t1@loopback t1a on t1.object_id = t1a.object_id
  4  where  t1.object_name = :b1 or t1a.subobject_name = :b2
  5  union all
  6  select *
  7  from   t2@loopback inner join t2@loopback t2a on t2.data_object_id = t2a.data_object_id
  8  where  t2.object_name = :b1 or t2a.subobject_name = :b2;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1911198332

----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     5 |  1150 |  2575   (1)| 00:00:01 |        |
|   1 |  UNION-ALL             |      |       |       |            |          |        |
|   2 |   CONCATENATION        |      |       |       |            |          |        |
|*  3 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  4 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|   5 |     TABLE ACCESS FULL  | T1   | 91774 |    10M|   429   (1)| 00:00:01 |   PDB1 |
|*  6 |    HASH JOIN           |      |     2 |   460 |   859   (1)| 00:00:01 |        |
|*  7 |     TABLE ACCESS FULL  | T1   |     2 |   230 |   429   (1)| 00:00:01 |   PDB1 |
|*  8 |     TABLE ACCESS FULL  | T1   | 91772 |    10M|   430   (1)| 00:00:01 |   PDB1 |
|*  9 |   HASH JOIN            |      |     1 |   230 |   858   (1)| 00:00:01 |        |
|* 10 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
|* 11 |    TABLE ACCESS FULL   | T2   |  8267 |   928K|   429   (1)| 00:00:01 |   PDB1 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   4 - filter("A4"."SUBOBJECT_NAME"=:B2)
   6 - access("A5"."OBJECT_ID"="A4"."OBJECT_ID")
   7 - filter("A5"."OBJECT_NAME"=:B1)
   8 - filter(LNNVL("A4"."SUBOBJECT_NAME"=:B2))
   9 - access("A3"."DATA_OBJECT_ID"="A2"."DATA_OBJECT_ID")
       filter("A3"."OBJECT_NAME"=:B1 OR "A2"."SUBOBJECT_NAME"=:B2)
  10 - filter("A3"."DATA_OBJECT_ID" IS NOT NULL)
  11 - filter("A2"."DATA_OBJECT_ID" IS NOT NULL)

Note
-----
   - fully remote statement

35 rows selected.

所以我怀疑这可能与环境有关。

数据库之间的字符集，版本等是否都相同？