Oracle如何通过dblink向远程实例传递数组参数完成跨库查询

问题根因 #

执行计划不符合预期的核心原因有两个：

• 你使用的本地自定义集合类型UDT_TBL_NUMBER未在远程实例dbA上创建，Oracle无法将本地集合作为参数传递到远端执行过滤逻辑，只能先将远程大表拉取到本地后再做关联
• driving_site hint生效的前提是关联的所有对象在指定站点都可访问，你的集合是本地对象，远端无法读取，因此hint直接失效，优化器只能选择本地执行关联逻辑

可行解决方案 #

方案1：动态拼接ID值（最适合当前ID体量极小的场景） #

因为你的l_ids数据量极小，可以直接将集合中的值拼接为逗号分隔的字符串，写入SQL的IN条件中，无需使用绑定变量传递集合，整个查询会直接推送到远端执行，过滤后才返回结果到本地，不会拉取全量远程表数据。
示例写法：

DECLARE
   out_row_data       SYS_REFCURSOR;
   l_ids              UDT_TBL_NUMBER;  -- TABLE OF NUMBER(10)
   l_sql VARCHAR2(18000);
   l_id_str VARCHAR2(4000);
BEGIN
   -- 拼接ID为逗号分隔字符串，例如'1,2,3,10,20'
   SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value) 
   INTO l_id_str FROM TABLE(l_ids);

   l_sql := 'SELECT * FROM tableX@dbA 
   WHERE column IN (' || l_id_str || ')';
   
   OPEN out_row_data FOR l_sql;
END;/

注意：如果ID数量超过Oracle IN条件1000个的上限，可以拆分为column IN (a,b,c) OR column IN (d,e,f)的格式即可。

方案2：远端创建同结构集合类型（适合ID数量较多的场景） #

如果你有远程实例的操作权限，可以按以下步骤实现：

• 在dbA上创建和本地完全一致的UDT_TBL_NUMBER类型，给dblink关联的访问用户授予该类型的使用权限
• 可以在dbA上创建一个接收集合参数、返回查询结果的存储过程或管道函数
• 本地直接调用远端的存储过程，将l_ids作为参数传入，所有过滤、计算逻辑完全在远端执行，仅返回最终结果到本地

方案3：使用DBMS_HS_PASSTHROUGH包（无远端修改权限时适用） #

如果没有权限在远端创建类型，可以使用Oracle内置的DBMS_HS_PASSTHROUGH包直接将SQL和参数发送到远端执行，绕过本地优化器的判断，强制过滤逻辑在远端执行。

验证方式 #

优化后你可以重新查看执行计划，如果生效，你会看到针对dbA的操作只有一个整段的REMOTE节点，不会出现本地集合迭代器和远程表做关联的操作。

内容的提问来源于stack exchange，提问作者Clark Miy