SQL : Oracle : Optimize LEFT Outer Join Query

Question

我有 1 个本地表(除了一个之外，所有列名称都与远程表不同)和 2 个远程表(具有相同的列名称)，我需要合并数据。

以下是我使用 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;

Answer 1

我在您的查询中看到的主要问题是 CTMAGENTAUDIT 和包含联合的子查询之间的最外层左连接。该子查询的问题在于，正如所写，Oracle 不可能对连接使用任何索引。这意味着 Oracle 在加入时可能不得不采用较慢的方法，可能是全面扫描。

这里的一种方法是创建一个包含联合查询的物化 View ，然后对其进行索引:

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);

有了这个索引物化 View ，我希望您的查询现在表现得更好:

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;