OR+DBLINK的关联SQL优化思路

原创布衣 2025-05-05

849

前言

最近遇到个10来个OR条件关联的复杂SQL再加DBlink，明显感觉到Oracle 都关联懵了，2-3个小时都没出结果。感受一下SQL的魅力吧，此次测试旨在拓宽优化思路。

性能SQL

各表数据量：已做脱敏处理

INFO        -- 4142
EXT         -- 4142
CRM_INFO    -- 4142
ACCT_ORDER  -- 793841
NAME_LIST   -- 159900

OR条件仅展示部份。

select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
FROM (
  SELECT id,unique_id,name,comments,externale,tag
  FROM pay.name_list WHERE SOURCE = '0001' 
  AND TAG in ('card','merchant','RECNAME')
  AND TYPE = '01'
) a
JOIN (
  SELECT 
      INFO.ID INFO_ID, 
      INFO.NAME INFO_NAME,
      CASE
          WHEN SEL.NO_CIPHER IS NOT NULL AND SEL.FACTOR IS NOT NULL
          THEN SM4@twodb(SEL.NO_CIPHER, SEL.FACTOR) 
      ELSE NULL END AS SEL_NO,
      CASE 
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'A' THEN 'A'
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'B' THEN 'C'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'A' THEN 'B'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'B' THEN 'D'
      END AS STATUS, 
      EXT.LICENSE_NO, 
      AAM.ID AAM_ID
  FROM INFO@twodb INFO
  JOIN EXT@twodb EXT ON INFO.ID = EXT.INFO_ID
  JOIN CRM_INFO@twodb SEL ON INFO.ID = SEL.INFO_ID
  JOIN ACCT_ORDER@twodb AAM ON AAM.id = INFO.id 
  WHERE INFO.STATUS IN ('A', 'B')
) b
ON DECODE(a.tag,'merchant',a.unique_id,null) = b.INFO_ID 
OR DECODE(a.tag,'RECNAME',a.unique_id,null) = b.INFO_NAME 
OR decode(a.tag,'card',a.unique_id,null) = b.SEL_NO 
OR DECODE(a.tag,'midno',a.unique_id,null) = b.LICENSE_NO ;

执行计划

性能问题分析

问题1、Elapsed：00:02:53.16
问题2、consistent gets：46822645 （逻辑读）
问题3、NAME_LIST 表全表扫描
问题4、DBLINK干扰无法查看子查询计划

排除 DBLINK 干扰

将表都导入到测试环境，排除dblink的干扰。

select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
FROM (
  SELECT id,unique_id,name,comments,externale,tag
  FROM name_list WHERE SOURCE = '0001' 
  AND TAG in ('card','merchant','RECNAME')
  AND TYPE = '01'
) a
JOIN (
  SELECT 
      INFO.ID INFO_ID, 
      INFO.NAME INFO_NAME,
      CASE
          WHEN SEL.NO_CIPHER IS NOT NULL AND SEL.FACTOR IS NOT NULL
          THEN SM4(SEL.NO_CIPHER, SEL.FACTOR) 
      ELSE NULL END AS SEL_NO,
      CASE 
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'A' THEN 'A'
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'B' THEN 'C'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'A' THEN 'B'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'B' THEN 'D'
      END AS STATUS, 
      EXT.LICENSE_NO, 
      EXT.TAX_NO, 
      EXT.CODE, 
      AAM.ID AAM_ID
  FROM INFO INFO
  JOIN EXT EXT ON INFO.ID = EXT.INFO_ID
  JOIN CRM_INFO SEL ON INFO.ID = SEL.INFO_ID
  JOIN ACCT_ORDER AAM ON AAM.id = INFO.id 
  WHERE INFO.STATUS IN ('A', 'B')
) b
ON DECODE(a.tag,'merchant',a.unique_id,null) = b.INFO_ID 
OR DECODE(a.tag,'RECNAME',a.unique_id,null) = b.INFO_NAME 
OR decode(a.tag,'card',a.unique_id,null) = b.SEL_NO 
OR DECODE(a.tag,'midno',a.unique_id,null) = b.LICENSE_NO ;

执行计划

优化思路：CTE+MATERIALIZE & UNION ALL

WITH + MATERIALIZE 的核心机制‌

临时表物化‌
添加 /*+ MATERIALIZE */ 提示后，Oracle 会将 WITH 定义的子查询结果写入全局临时表（GTT），后续查询直接读取该临时表而非重复执行子查询。
优势‌：避免多次计算相同逻辑，减少 CPU 和 I/O 消耗;
‌触发条件‌：默认情况下，仅当 WITH 子句被引用 ‌2 次以上‌ 时优化器才自动物化，而 MATERIALIZE 提示可强制单次引用场景下仍物化。
通过物化临时表，可强制优化器选择更优的连接方式（如哈希连接而非嵌套循环），并减少执行计划的不稳定性。

潜在问题与限制

临时表空间压力:物化操作依赖临时表空间，若数据量过大或并发查询较多，可能引发空间不足或 I/O 竞争，导致性能下降
维护复杂性:MATERIALIZE 提示需手动嵌入 SQL，且当数据分布或表结构变化时，可能需重新评估提示的有效性，增加维护成本
版本兼容性风险:未公开的 HINT（如 MATERIALIZE）可能在不同 Oracle 版本中行为不一致，存在潜在兼容性问题

效果如下：

WITH 
   b as (
  SELECT /*+ materialize */
      INFO.ID INFO_ID, 
      INFO.NAME INFO_NAME,
      CASE
          WHEN SEL.NO_CIPHER IS NOT NULL AND SEL.FACTOR IS NOT NULL
          THEN SM4@twodb(SEL.NO_CIPHER, SEL.FACTOR) 
      ELSE NULL END AS SEL_NO,
      CASE 
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'A' THEN 'A'
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'B' THEN 'C'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'A' THEN 'B'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'B' THEN 'D'
      END AS STATUS, 
      EXT.LICENSE_NO, 
      EXT.TAX_NO, 
      EXT.CODE, 
      AAM.ID AAM_ID
  FROM INFO@twodb INFO
  JOIN EXT@twodb EXT ON INFO.ID = EXT.INFO_ID
  JOIN CRM_INFO@twodb SEL ON INFO.ID = SEL.INFO_ID
  JOIN ACCT_ORDER@twodb AAM ON AAM.id = INFO.id 
  WHERE INFO.STATUS IN ('A', 'B')
  ),
   a as (
  SELECT /*+ materialize */ id,unique_id,name,comments,externale,tag
  FROM pay.name_list WHERE SOURCE = '0001' 
  AND TAG in ('card','merchant','RECNAME')
  AND TYPE = '01'
   )  
select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
from a 
join b ON DECODE(a.tag,'merchant',a.unique_id,null) = b.INFO_ID
or DECODE(a.tag,'RECNAME',a.unique_id,null) = b.INFO_NAME 
or decode(a.tag,'card',a.unique_id,null) = b.SEL_NO 
or  DECODE(a.tag,'midno',a.unique_id,null) = b.LICENSE_NO ;

执行计划

+ UNION ALL

WITH 
   b as (
 select ...同上....
  ),
   a as (
 select ....同上....
   )  
select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
from a 
join b ON DECODE(a.tag,'merchant',a.unique_id,null) = b.INFO_ID
union all
select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
from a 
join b ON DECODE(a.tag,'RECNAME',a.unique_id,null) = b.INFO_NAME 
union all
select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
from a 
join b ON decode(a.tag,'card',a.unique_id,null) = b.SEL_NO 
union all
select
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
from a 
join b ON  DECODE(a.tag,'midno',a.unique_id,null) = b.LICENSE_NO ;

HINT 另一种优化思路：/+ no_merge /

用于强制优化器对特定子查询或视图保持独立执行逻辑，避免其被合并到外层查询中。

阻止视图/子查询合并：
no_merge 提示作用于子查询块或视图别名（如 a 和 b），强制优化器将其视为独立单元执行，而非合并到主查询中重写执行计划，若子查询包含复杂过滤条件或聚合操作，合并可能导致执行效率下降，通过 no_merge 可保留其独立计算逻辑。

select /*+ no_merge(a) no_merge(b) */
  a.ID as nameListId,a.UNIQUE_ID,a.NAME,a.COMMENTS,a.externale,a.TAG,b.INFO_ID,b.STATUS,b.AAM_ID
FROM (
  SELECT id,unique_id,name,comments,externale,tag
  FROM pay.name_list WHERE SOURCE = '0001' 
  AND TAG in ('card','merchant','RECNAME')
  AND TYPE = '01'
) a
JOIN (
  SELECT 
      INFO.ID INFO_ID, 
      INFO.NAME INFO_NAME,
      CASE
          WHEN SEL.NO_CIPHER IS NOT NULL AND SEL.FACTOR IS NOT NULL
          THEN SM4@twodb(SEL.NO_CIPHER, SEL.FACTOR) 
      ELSE NULL END AS SEL_NO,
      CASE 
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'A' THEN 'A'
         WHEN INFO.STATUS = 'A' AND INFO.STATUS_OUT = 'B' THEN 'C'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'A' THEN 'B'
         WHEN INFO.STATUS = 'B' AND INFO.STATUS_OUT = 'B' THEN 'D'
      END AS STATUS, 
      EXT.LICENSE_NO, 
      AAM.ID AAM_ID
  FROM INFO@twodb INFO
  JOIN EXT@twodb EXT ON INFO.ID = EXT.INFO_ID
  JOIN CRM_INFO@twodb SEL ON INFO.ID = SEL.INFO_ID
  JOIN ACCT_ORDER@twodb AAM ON AAM.id = INFO.id 
  WHERE INFO.STATUS IN ('A', 'B')
) b
ON DECODE(a.tag,'merchant',a.unique_id,null) = b.INFO_ID 
OR DECODE(a.tag,'RECNAME',a.unique_id,null) = b.INFO_NAME 
OR decode(a.tag,'card',a.unique_id,null) = b.SEL_NO 
OR DECODE(a.tag,'midno',a.unique_id,null) = b.LICENSE_NO ;

执行计划

加索引优化

创建索引

two@two:2186> create index idx_name_list on name_list(SOURCE,TYPE,TAG) tablespace two_ind_dat;
Index created.

原性能SQL写法不变：执行计划，效果显著

总结：

MERGE JOIN CARTESIAN
是 Oracle 中一种结合排序合并连接（Sort Merge Join）和笛卡尔积（Cartesian Product）的连接方式。其特点是要求两个结果集按连接字段排序后，再执行笛卡尔乘积‌。
‌排序合并连接‌：对两个表的过滤结果按连接字段排序，再交替比较合并‌。
‌笛卡尔积‌：两个结果集的每条记录均相互匹配，生成中间结果集‌
TEMP TABLE TRANSFORMATION
‌ 是 Oracle 优化器将子查询或公共表达式（CTE）的结果物化到全局临时表（GTT）中的过程，用于提升复杂查询性能。‌触发方式‌：通过 /*+ MATERIALIZE */ 提示显式强制物化，或在 WITH 子句被多次引用时由优化器自动选择。
LOAD AS SELECT
可通过类似逻辑实现数据加载与查询优化。结合 CREATE TABLE AS SELECT（CTAS）或 INSERT INTO SELECT 等操作，可高效完成数据迁移和批量处理。
各优化思路对比，优化没有唯一性，适合才是最好的，综合考虑还是“加索引”效果显著：