SQL: Oracle: 优化左外连接查询

Question

SQL: Oracle: 优化左外连接查询

4

我有一个本地表（除了一个列名与远程表不同外，所有列名都不同），还有两个远程表（它们具有相同的列名），我需要合并这些数据。以下是我使用LEFT OUTER JOIN和UNION编写的查询，但性能较慢。请问有人可以帮助优化此查询吗？

select
"CONTROL_M_SERVER",
"HOST",
CASE
WHEN "AGSTAT" = 'V' THEN 'Available'
WHEN "AGSTAT" = 'U' THEN 'Unavailable'
WHEN "AGSTAT" = 'R' THEN 'Discovering'
ELSE 'Not Defined in Control-M'
END as Agent_Status,
T1.VERSION,
"PORTS",
"MANAGEMENT_IP",
"OPERATING_SYSTEM",
"CLUSTER_ALIAS",
"NODEGROUP",
"APPLICATION_ID",
"DATE_CONFIGURED",
"CONFIGURED_BY"
from "CTMAGENTAUDIT" T1
left outer join (select NODEID,AGSTAT from CMR_NODES@SPDB UNION ALL select NODEID,AGSTAT from CMR_NODES@DEVDB) T2 on T2.NODEID = T1.HOST;

- venky_au

左对齐的 SQL 太难阅读了... - jarlh

3个回答

0

我会像这样做：

select
"CONTROL_M_SERVER",
"HOST",
CASE
WHEN "AGSTAT" = 'V' THEN 'Available'
WHEN "AGSTAT" = 'U' THEN 'Unavailable'
WHEN "AGSTAT" = 'R' THEN 'Discovering'
ELSE 'Not Defined in Control-M'
END as Agent_Status,
T1.VERSION,
"PORTS",
"MANAGEMENT_IP",
"OPERATING_SYSTEM",
"CLUSTER_ALIAS",
"NODEGROUP",
"APPLICATION_ID",
"DATE_CONFIGURED",
"CONFIGURED_BY",
(select t2.NODEID, t2.AGSTAT from CMR_NODES@SPDB t2 where t1.host = t2.nodeid),  
(select t3.NODEID, t3.AGSTAT from CMR_NODES@DEVDB t3 where T1.HOST = T3.NODEID) from t1;

- Gauravsa

嗨，Gaurav，感谢您的迅速回复。我会测试一下并告诉您结果如何。 - venky_au

0

您的查询基本上是：

select . . .
from CTMAGENTAUDIT T1 left outer join
     (select NODEID, AGSTAT
      from CMR_NODES@SPDB UNION ALL
      select NODEID, AGSTAT
      from CMR_NODES@DEVDB
     ) T2
     on T2.NODEID = T1.HOST;

假设在每个CMR_NODE表中，NODEID/AGSTAT组合是唯一的，我会这样写：

select . . .,
       coalesce(s1.AGSTAT, s2.AGSTAT) as AGSTAT,
       (case coalesce(s1.AGSTAT, s2.AGSTAT) 
            when 'V' then 'Available'
            when 'U' then 'Unavailable'
            when 'R' then 'Discovering'
            else 'Not Defined in Control-M'
        end) as Agent_Status
from CTMAGENTAUDIT T1 left outer join
     CMR_NODES@SPDB s1
     on s1.NODEID = T1.HOST left outer join
     CMR_NODES@DEVDB s2
     on s1.NODEID = T1.HOST

这样至少可以让每个表单独进行优化，这应该会有所帮助。

显然，如果您有权限和愿望设置跨服务器物化视图，则使用物化视图的解决方案将更快。但是，物化视图存在其他维护问题，特别是如果您有多个这样的视图并且假定它们同时更新。

- Gordon Linoff

嗨，Gordon，感谢您的及时回复。我会测试一下并告诉您结果如何。 - venky_au

我试过了，它运行得很好。谢谢。只有一个问题，如果你看到我的原始查询，我是使用CASE语句来打印AGSTAT的。如何使用COALESCE实现这一点呢？ - venky_au

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Biegeleisen · Accepted Answer

你的查询中存在一个主要问题，即 CTMAGENTAUDIT 和包含 UNION 的子查询之间的最外层左连接。子查询的问题在于，在现有的情况下，Oracle 无法使用任何索引来进行连接。这意味着 Oracle 可能会采用较慢的方法进行连接，可能是全表扫描。

在这里的一种方法是创建一个包含 UNION 查询的物化视图，然后对其进行索引：

CREATE MATERIALIZED VIEW T2 AS
SELECT NODEID, AGSTAT FROM CMR_NODES@SPDB
UNION ALL
SELECT NODEID, AGSTAT FROM CMR_NODES@DEVDB;

CREATE INDEX mv_node_idx ON T2 (NODEID);

有了这个索引化的物化视图，我期望您的查询现在能够更加出色：

SELECT
    CONTROL_M_SERVER,
    HOST,
    CASE WHEN AGSTAT = 'V' THEN 'Available'
         WHEN AGSTAT = 'U' THEN 'Unavailable'
         WHEN AGSTAT = 'R' THEN 'Discovering'
         ELSE 'Not Defined in Control-M' END AS Agent_Status,
    T1.VERSION,
    PORTS,
    MANAGEMENT_IP,
    OPERATING_SYSTEM,
    CLUSTER_ALIAS,
    NODEGROUP,
    APPLICATION_ID,
    DATE_CONFIGURED,
    CONFIGURED_BY
FROM CTMAGENTAUDIT T1
LEFT OUTER JOIN T2
    ON T2.NODEID = T1.HOST;