在联接条件下使用OR还是使用多个联接更好？

Question

我试图根据票证是否表明某一事件已经发生而对其进行标记。我遇到的问题是，指示发生这种情况的特定值可能在票证上的任何25个字段中的任何一个中，如果它完成(我们的DBA会因为占用过多的CPU或拥有过高的PJI)，那么我当前的逻辑需要很长时间才能完成(通常>45分钟)。目前，我正在使用下面的代码来标记门票：

CREATE VOLATILE TABLE
--DROP TABLE 
FCT
AS ( 
SELECT
I_FCT.Ticket_Nbr
, I_FCT.Plan_Dim_CK
, I_FCT.S_DATE
, I_FCT.E_DATE

, CASE
    WHEN I_FCT.Event1_CK = AD.Event_CK THEN '1'
    WHEN I_FCT.Event2_CK = AD.Event_CK THEN '2'
    WHEN I_FCT.Event3_CK = AD.Event_CK THEN '3'
    WHEN I_FCT.Event4_CK = AD.Event_CK THEN '4'
    WHEN I_FCT.Event5_CK = AD.Event_CK THEN '5'
    WHEN I_FCT.Event6_CK = AD.Event_CK THEN '6'
    WHEN I_FCT.Event7_CK = AD.Event_CK THEN '7'
    WHEN I_FCT.Event8_CK = AD.Event_CK THEN '8'
    WHEN I_FCT.Event9_CK = AD.Event_CK THEN '9'
    WHEN I_FCT.Event10_CK = AD.Event_CK THEN '10'
    WHEN I_FCT.Event11_CK = AD.Event_CK THEN '11'
    WHEN I_FCT.Event12_CK = AD.Event_CK THEN '12'
    WHEN I_FCT.Event13_CK = AD.Event_CK THEN '13'
    WHEN I_FCT.Event14_CK = AD.Event_CK THEN '14'
    WHEN I_FCT.Event15_CK = AD.Event_CK THEN '15'
    /*WHEN I_FCT.Event16_CK = AD.Event_CK THEN '16'
    WHEN I_FCT.Event17_CK = AD.Event_CK THEN '17'
    WHEN I_FCT.Event18_CK = AD.Event_CK THEN '18'
    WHEN I_FCT.Event19_CK = AD.Event_CK THEN '19'
    WHEN I_FCT.Event20_CK = AD.Event_CK THEN '20'
    WHEN I_FCT.Event21_CK = AD.Event_CK THEN '21'
    WHEN I_FCT.Event22_CK = AD.Event_CK THEN '22'
    WHEN I_FCT.Event23_CK = AD.Event_CK THEN '23'
    WHEN I_FCT.Event24_CK = AD.Event_CK THEN '24'
    WHEN I_FCT.Event25_CK = AD.Event_CK THEN '25'*/
    ELSE NULL
END AS AOD_Event

FROM
I_FCT

LEFT JOIN AOD_CKS AS AD
    ON (AD.Event_CK = I_FCT.Event1_CK
        OR AD.Event_CK = I_FCT.Event2_CK
        OR AD.Event_CK = I_FCT.Event3_CK
        OR AD.Event_CK = I_FCT.Event4_CK
        OR AD.Event_CK = I_FCT.Event5_CK
        OR AD.Event_CK = I_FCT.Event6_CK
        OR AD.Event_CK = I_FCT.Event7_CK
        OR AD.Event_CK = I_FCT.Event8_CK
        OR AD.Event_CK = I_FCT.Event9_CK
        OR AD.Event_CK = I_FCT.Event10_CK
        OR AD.Event_CK = I_FCT.Event11_CK
        OR AD.Event_CK = I_FCT.Event12_CK
        OR AD.Event_CK = I_FCT.Event13_CK
        OR AD.Event_CK = I_FCT.Event14_CK
        OR AD.Event_CK = I_FCT.Event15_CK
        /*OR AD.Event_CK = I_FCT.Event16_CK
        OR AD.Event_CK = I_FCT.Event17_CK
        OR AD.Event_CK = I_FCT.Event18_CK
        OR AD.Event_CK = I_FCT.Event19_CK
        OR AD.Event_CK = I_FCT.Event20_CK
        OR AD.Event_CK = I_FCT.Event21_CK
        OR AD.Event_CK = I_FCT.Event22_CK
        OR AD.Event_CK = I_FCT.Event23_CK
        OR AD.Event_CK = I_FCT.Event24_CK
        OR AD.Event_CK = I_FCT.Event25_CK*/
    )
)
WITH DATA PRIMARY INDEX (Ticket_Nbr) 
ON COMMIT PRESERVE ROWS;

因此，我的问题是，是否最好在每个事件CK上切换一次JOINing (顺便说一句，这是一个关键值&是否是AOD_CKS上的索引)？或者我应该少花点时间&试着把工作分开(例如，字段1-6连接一次，7-13字段的另一个表，等等)&然后将这4个表连接在一起？

任何帮助都是非常感谢的！

Answer 1

最后，我将连接分成3部分，然后按如下方式合并数据：

----------------------------------------------------------------------------
--                                                  FLAG ADDICTION CLAIMS
----------------------------------------------------------------------------    
CREATE VOLATILE TABLE
--DROP TABLE 
I_FCT_1
AS ( 
SELECT
    I_FCT.Claim_Nbr
    , I_FCT.Plan_Dim_CK

    , CASE
        WHEN I_FCT.Diag1_CK = AD.Diag_CK THEN '1'
        WHEN I_FCT.Diag2_CK = AD.Diag_CK THEN '2'
        WHEN I_FCT.Diag3_CK = AD.Diag_CK THEN '3'
        WHEN I_FCT.Diag4_CK = AD.Diag_CK THEN '4'
        WHEN I_FCT.Diag5_CK = AD.Diag_CK THEN '5'
        WHEN I_FCT.Diag6_CK = AD.Diag_CK THEN '6'
        WHEN I_FCT.Diag7_CK = AD.Diag_CK THEN '7'
        WHEN I_FCT.Diag8_CK = AD.Diag_CK THEN '8'
        ELSE NULL
    END AS AOD_Diag

FROM
    I_FCT

    LEFT JOIN ADD_CKS AS AD
        ON (AD.Diag_CK = I_FCT.Diag1_CK
            OR AD.Diag_CK = I_FCT.Diag2_CK
            OR AD.Diag_CK = I_FCT.Diag3_CK
            OR AD.Diag_CK = I_FCT.Diag4_CK
            OR AD.Diag_CK = I_FCT.Diag5_CK
            OR AD.Diag_CK = I_FCT.Diag6_CK
            OR AD.Diag_CK = I_FCT.Diag7_CK
            OR AD.Diag_CK = I_FCT.Diag8_CK
        )
)
WITH DATA PRIMARY INDEX (Claim_Nbr) 
ON COMMIT PRESERVE ROWS;

----------------------------------------------------------------------------
CREATE VOLATILE TABLE
--DROP TABLE 
I_FCT_2
AS ( 
SELECT
    I_FCT.Claim_Nbr
    , I_FCT.Plan_Dim_CK

    , CASE
        WHEN I_FCT.Diag9_CK = AD.Diag_CK THEN '9'
        WHEN I_FCT.Diag10_CK = AD.Diag_CK THEN '10'
        WHEN I_FCT.Diag11_CK = AD.Diag_CK THEN '11'
        WHEN I_FCT.Diag12_CK = AD.Diag_CK THEN '12'
        WHEN I_FCT.Diag13_CK = AD.Diag_CK THEN '13'
        WHEN I_FCT.Diag14_CK = AD.Diag_CK THEN '14'
        WHEN I_FCT.Diag15_CK = AD.Diag_CK THEN '15'
        WHEN I_FCT.Diag16_CK = AD.Diag_CK THEN '16'
        ELSE NULL
    END AS AOD_Diag

FROM
    I_FCT

    LEFT JOIN ADD_CKS AS AD
        ON (AD.Diag_CK = I_FCT.Diag9_CK
            OR AD.Diag_CK = I_FCT.Diag10_CK
            OR AD.Diag_CK = I_FCT.Diag11_CK
            OR AD.Diag_CK = I_FCT.Diag12_CK
            OR AD.Diag_CK = I_FCT.Diag13_CK
            OR AD.Diag_CK = I_FCT.Diag14_CK
            OR AD.Diag_CK = I_FCT.Diag15_CK
            OR AD.Diag_CK = I_FCT.Diag16_CK
        )
)
WITH DATA PRIMARY INDEX (Claim_Nbr) 
ON COMMIT PRESERVE ROWS;

----------------------------------------------------------------------------
CREATE VOLATILE TABLE
--DROP TABLE 
I_FCT_3
AS ( 
SELECT
    I_FCT.Claim_Nbr
    , I_FCT.Plan_Dim_CK

    , CASE
        WHEN I_FCT.Diag17_CK = AD.Diag_CK THEN '17'
        WHEN I_FCT.Diag18_CK = AD.Diag_CK THEN '18'
        WHEN I_FCT.Diag19_CK = AD.Diag_CK THEN '19'
        WHEN I_FCT.Diag20_CK = AD.Diag_CK THEN '20'
        WHEN I_FCT.Diag21_CK = AD.Diag_CK THEN '21'
        WHEN I_FCT.Diag22_CK = AD.Diag_CK THEN '22'
        WHEN I_FCT.Diag23_CK = AD.Diag_CK THEN '23'
        WHEN I_FCT.Diag24_CK = AD.Diag_CK THEN '24'
        WHEN I_FCT.Diag25_CK = AD.Diag_CK THEN '25'
        ELSE NULL
    END AS AOD_Diag

FROM
    I_FCT

    LEFT JOIN ADD_CKS AS AD
        ON (AD.Diag_CK = I_FCT.Diag17_CK
            OR AD.Diag_CK = I_FCT.Diag18_CK
            OR AD.Diag_CK = I_FCT.Diag19_CK
            OR AD.Diag_CK = I_FCT.Diag20_CK
            OR AD.Diag_CK = I_FCT.Diag21_CK
            OR AD.Diag_CK = I_FCT.Diag22_CK
            OR AD.Diag_CK = I_FCT.Diag23_CK
            OR AD.Diag_CK = I_FCT.Diag24_CK
            OR AD.Diag_CK = I_FCT.Diag25_CK
        )
)
WITH DATA PRIMARY INDEX (Claim_Nbr) 
ON COMMIT PRESERVE ROWS;

---------------------------------------------------------------------------
CREATE VOLATILE TABLE
--DROP TABLE 
FCT
AS ( 
SELECT
    I_FCT.Claim_Nbr
    , I_FCT.Plan_Dim_CK
    , I_FCT.ADMISSION_DATE

    , I_FCT.DISCHARGE_DATE

    , I_FCT.ADMISSION_DATE_DIM_CK
    , I_FCT.Proc3_CK
    , I_FCT.Proc4_CK
    , I_FCT.Proc5_CK
    , I_FCT.Proc6_CK
    , I_FCT.Proc7_CK
    , I_FCT.Bill_Type
    , CASE
        WHEN C1.AOD_Diag IS NOT NULL THEN C1.AOD_Diag
        WHEN C2.AOD_Diag IS NOT NULL THEN C2.AOD_Diag
        WHEN C3.AOD_Diag IS NOT NULL THEN C3.AOD_Diag
        ELSE NULL
    END AS AOD_Diag

FROM
    I_FCT

    JOIN I_FCT_1 AS C1
        ON C1.Claim_Nbr = I_FCT.Claim_Nbr
            AND C1.Plan_Dim_CK = I_FCT.Plan_Dim_CK

    JOIN I_FCT_2 AS C2
        ON I_FCT.Claim_Nbr = C2.Claim_Nbr
            AND I_FCT.Plan_Dim_CK = C2.Plan_Dim_CK

    JOIN I_FCT_3 AS C3
        ON I_FCT.Claim_Nbr = C3.Claim_Nbr
            AND I_FCT.Plan_Dim_CK = C3.Plan_Dim_CK
)
WITH DATA PRIMARY INDEX (Claim_Nbr)
ON COMMIT PRESERVE ROWS;