优化完整的外部联接查询

以下外部联接查询需要十多分钟才能产生结果,如何进一步优化,是否可以避免完全外部联接?

任何帮助将非常感激,

谢谢

    WITH CMD_DEP AS (
SELECT
    DEPLOYMENT_ID
FROM
    CMD_DEPLOYMENT
WHERE
    ORG_ID = 'xyz'
    AND DEPLOYMENT_DATE BETWEEN TIMESTAMP '2013-02-11 13:03:36.928000' AND TIMESTAMP '2020-02-11 13:03:36.928000'
    AND TYPE NOT IN ('PROOF',
    'TEST') )
SELECT
    COALESCE(opens.UA_DEVICE,
    clicks.UA_DEVICE) deviceType,
    COALESCE(opens.OPENS,
    0) opens,
    COALESCE(clicks.CLICKS,
    0) clicks
FROM
    (
    SELECT
        o.UA_DEVICE,
        SUM(o.HTTP_OPEN_CNT) OPENS
    FROM
        AGG_DPLYMNT_OPEN_DEVICE_TOTALS o,
        CMD_DEP cmd_dep
    WHERE
        cmd_dep.DEPLOYMENT_ID = o.DEPLOYMENT_ID
    GROUP BY
        o.UA_DEVICE) opens
FULL OUTER JOIN (
    SELECT
        c.UA_DEVICE,
        SUM(c.HTTP_REDIRECT_CNT) CLICKS
    FROM
        AGG_DPLYMNT_CLICK_DEVICE_TOTAL c,
        CMD_DEP cmd_dep
    WHERE
        cmd_dep.DEPLOYMENT_ID = c.DEPLOYMENT_ID
    GROUP BY
        c.UA_DEVICE) clicks ON
    opens.UA_DEVICE = clicks.UA_DEVICE;
评论