none
Performance Tuning of Query - (Tables coming from different Sources)

    Question

  • Team,

    I have a query which need to be Tuned, can someone help me on this. Thanks!

    When I ran individually the WITH ACT Query it is running in 4 secs and remaining individual queries "DIM_DEF","DIM_CUST","DIM_LC","DIM_EMP" are running fast in 2  to 3secs each. Only when I join these tables with ACT the performance is slow. Can someone help me on this.

    Points to Remember

    1) ACT Tables are ODS Tables (Ex Schema Name ODSSCH)

    2) "DIM_DEF","DIM_CUST","DIM_LC","DIM_EMP" are from EDW (Ex Schema Name - EDWSCH)

    Note:  We can change the query by creating temp tables / store procedure / anything else to fine tune this query.

    Here is the query and Explain Plan attached.

    WITH ACT AS (
     SELECT /*+ NO_MERGE(A) NO_MERGE(B) PARALLEL(A,4) PARALLEL(B,4) */
    A.ABC_ID, A.XYZ_ID, A.XYZ_DESCRIPTION, A.OR_ID, A.KIT, A.TR_DATE, A.PR_ID, A.PRICE, 
    CASE 
    WHEN B.Status IS NOT NULL 
    THEN B.STATUS
    ELSE A.Status
    END 
    STATUS , 
    CASE 
    WHEN B.Status IS NOT NULL 
    THEN B.EMP_ID
    ELSE A.EMP_ID
    END EMP_ID, 
    CASE 
    WHEN B.Status IS NOT NULL 
    THEN B.LN_ID
    ELSE A.LN_ID
    END LN_ID, 
    CASE 
    WHEN B.Status IS NOT NULL 
    THEN B.LC_ID
    ELSE A.LC_ID
    END LC_ID
    FROM ( 
    SELECT /*+ DRIVING_SITE(cass) PARALLEL(cass) */ABC_ID, XYZ_ID, XYZ_DESCRIPTION, OR_ID, 
    STATUS , KIT, trunc(
    TR_DATE
    ) AS TR_DATE, EMP_ID, LN_ID, LC_ID, PR_ID, PRICE /100 AS PRICE, BR_SEQ_NO, BR_ID, ODS_LPD
    FROM AS.CAT
    WHERE TO_CHAR(
    trunc(
    TR_DATE, 'MM'
    ) ) = TO_CHAR(
    ADD_MONTHS(
    TRUNC(
    SYSDATE, 'MM'
    ), -1))
    AND STATUS = 'ACTIVE')A
    LEFT JOIN (
    
    SELECT /*+ DRIVING_SITE(cass) PARALLEL(cass) */ABC_ID, XYZ_ID, XYZ_DESCRIPTION, OR_ID, 
    STATUS , KIT, trunc(
    TR_DATE
    ) AS TR_DATE, EMP_ID, LN_ID, LC_ID, PR_ID, PRICE /100 AS PRICE, BR_SEQ_NO, BR_ID, ODS_LPD
    FROM AS.CAT
    WHERE TO_CHAR(
    trunc(
    TR_DATE, 'MM'
    )) = TO_CHAR(
    ADD_MONTHS(
    TRUNC(
    SYSDATE, 'MM'
    ), -1))
    AND STATUS = 'ACTIVE')B ON A.ABC_ID = B.ABC_ID)
    
     
    
    -- With Ended ---------
    
    SELECT /*+ DRIVING_SITE(Cst) NO_MERGE(B) PARALLEL(ACT) PARALLEL(B) PARALLEL(Cst) */
    ACT . * , B.CUST_ID, 
    CASE 
    WHEN CST.BUS_NM = '{NA}'
    THEN CST.CUST_FIRST_NM || ' ' || CST.CUST_LAST_NM
    ELSE CST.BUS_NM
    END AS CUST_NAME, Cst.CUST_TYPE_DESC AS CUST_TYP_DESC, Cst.CUST_SUB_TYPE_DESC AS CUST_SUB_TYPE_DESC, to_char(
    ACT.TR_DATE, 'Month-yyyy'
    ) AS REPORT_DATE, assc.EMP_FIRST_NM AS ASOC_FST_NM, assc.EMP_LAST_NM AS ASOC_LAST_NM, assc.JOB_TITLE_NM AS POSN_NM, assc.EMP_TYPE_DESC, fac.CURR_LC_NM AS FAC_NM, -- fac.FAC_DESC,
    fac.CURR_COMPANY_CD AS BUS_ENTT_CD, fac.CURR_COMPANY_NM AS BUS_ENTT_NM, fac.LC_TYPE_DESC AS FAC_TYP_DESC, fac.CITY_NM, fac.CURR_MARKET_NM AS MKT_NM, fac.CURR_REGION_NM AS RGN_NM, fac.CURR_SALE_CHNL_LVL_1_DESC AS SALE_CHNL_LVL_1_DESC, fac.CURR_SALE_CHNL_LVL_2_DESC AS SALE_CHNL_LVL_2_DESC, fac.CURR_SALE_CHNL_LVL_3_DESC AS SALE_CHNL_LVL_3_DESC, fac.STATE_CD AS ST_CD, fac.LINE_1_ADDR AS STRT_LINE_1_ADDR, fac.LINE_2_ADDR AS STRT_LINE_2_ADDR, fac.CURR_TERRITORY_NM AS TERR_NM, fac.ZIP5_CD AS ZIP_5_ZIP_CD-- ,fac.FAC_MGR_ASOC_FST_NM, 
    -- fac.FAC_MGR_ASOC_LAST_NM
    
    FROM ACT
    LEFT JOIN (
    
    SELECT /*+ DRIVING_SITE(S) PARALLEL(S) */S.DEF_ID, MASTER_DEF_ID, DEF_MDN, CUST_ID, ROW_EFF_DT, ROW_EXP_DT, TRUNC(
    NVL( DEF_AVT_DT, ROW_UPDT_DT ) 
    )SUB_ACT_BEG, TRUNC(
    NVL( LEAD(
    DEF_AVT_DT
    )OVER(
     PARTITION 
    BY DEF_MDN
    OR BY DEF_AVT_DT
    ) -1, TO_DATE(
     '12/31/9999', 'MM/DD/YYYY'
    ) ) ) AS SUB_ACT_END
    FROM DIM_DEF S
    WHERE DEF_ID >0)B ON TO_CHAR(
    ACT.KIT
    ) = TO_CHAR(
    B.DEF_MDN
    )
    AND ACT.TR_DATE
    BETWEEN SUB_ACT_BEG
    AND SUB_ACT_END
    LEFT JOIN DIM_CUST Cst ON Cst.CUST_ID = B.CUST_ID
    AND ACT.TR_DATE
    BETWEEN Cst.ROW_EFF_DT
    AND Cst.ROW_EXP_DT
    LEFT JOIN DWDPL.DIM_LC fac ON fac.LC_ID = ACT.LC_ID
    AND trunc(
    fac.ROW_EXP_DT
    ) = to_date(
     'DEC-31-9999', 'MM/DD/YYYY'
    )
    LEFT JOIN DWDPL.DIM_EMP assc ON assc.EMP_ID = ACT.EMP_ID
    AND trunc(
    assc.ROW_EXP_DT
    ) = to_date(
     'DEC-31-9999', 'MM/DD/YYYY'
    )
    LEFT JOIN (
    
    SELECT ABC_ID, XYZ_ID, XYZ_DESCRIPTION, OR_ID, 
    STATUS , KIT, TR_DATE, EMP_ID, LN_ID, LC_ID, PR_ID, PRICE /100 AS PRICE, BR_SEQ_NO, BR_ID, ODS_LPD
    FROM AS.CAT cass
    WHERE TO_CHAR(
    trunc(
    Cass.TR_DATE, 'MM'
    )) = TO_CHAR(
    ADD_MONTHS(
    TRUNC(
    SYSDATE, 'MM'
    ), -1))
    AND STATUS = 'CANCEL')C ON ACT.ABC_ID = C.ABC_ID
    WHERE C.status IS NULL 
     
    
    

    Explain Plan


    K.Kiran

    Friday, May 25, 2018 4:07 AM

Answers

  • Hi Kiran,

    Based on your suggestion I have made the following changes. Hopefully it will work. Rgds,

    Soumen


    S.SUB_ID, MASTER_SUB_ID, SUB_MDN, CUST_ID, ROW_EFF_DT, ROW_EXP_DT,
            TRUNC(NVL(SUB_AT_DT,ROW_UPDT_DT)) SUB_ACT_BEG,

    max(nvl(SUB_AT_DT,ROW_UPDT_DT)) over (partition by SUB_MDN) AS SUB_ACT_END

            /* TRUNC(NVL(LEAD(SUB_AT_DT) OVER (PARTITION BY SUB_MDN ORDER BY SUB_AT_DT)-1,TO_DATE('12/31/9999','MM/DD/YYYY'))) AS SUB_ACT_END */
        FROM DIM_SUB S
        WHERE SUB_ID>0
    ) B
    ON TO_CHAR(ACT.CTN)=TO_CHAR(B.SUB_MDN)
        AND ACT.TR_DATE BETWEEN SUB_ACT_BEG AND SUB_ACT_END 
    Sunday, May 27, 2018 6:07 AM

All replies

  • Hi,

    There are several steps you can do to fine tune this query. Break down all your sub selects into temp tables and join them together to form a final query. As for first cte convert it into a function call returning a table and join it with your final select.  Wrap this all up into a procedure call. Good luck.

    Friday, May 25, 2018 4:57 AM
  • Hello,

    That's Oracle P/SQL and this here is a Microsoft SQL Server Forum; you should post your question to a more related Forum => https://community.oracle.com/welcome


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Friday, May 25, 2018 5:50 AM
    Friday, May 25, 2018 5:46 AM
  • Hi Soumen,

    I have a basic knowledge in SQL, can you please help me in writing the query. We are switching our database to SQL server, and the team asked me to tune the query in Oracle and promote to SQL server.

    Thanks!

    Kiran


    Friday, May 25, 2018 12:51 PM
  • Hi Kiran,

    My Oracle knowledge is limited but I dont think its the main issue here. This query is not formatted correctly with indentation etc and I cant tell which starts where and ends where. These queries are quite portable and almost 70% can be transported to sql server with no change. Most of the major databases are now becoming very similar structurally. Anyway, once your query is formatted maybe we can tell how to split it up. Good luck.


    Friday, May 25, 2018 1:25 PM
  • WITH ACT AS (
     SELECT /*+ NO_MERGE(A) NO_MERGE(B) PARALLEL(A,4) PARALLEL(B,4) */
    
    But that's really very Oracle specific stuff. For example the bold mark part, in SQL Server it's just a comment and will ignored by the engine, but in Oracle that are query query hints, eg. not to use merge joins, max 4 parallel processes etc.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 25, 2018 1:26 PM
  • I have formatted the SQL to some extent, please let me know if you need more details.

    -- To get Active (or) COR-Active Records.
    -- Priority a) if a particular MD_ID, SR_ID, ORDER_ID has 'COR-ACTIVE' and 'ACTIVE' Status then we will show 'COR-ACTIVE' b) if a particular MD_ID, SR_ID, ORDER_ID has only has 'ACTIVE' Status then we will show 'ACTIVE', thats the reason we have used left Join for ACTIVE.
    
    with ACT as 
       (
       select 
        A.MD_ID, A.SR_ID, A.SR_DESC, A.ORDER_ID, A.CTN, A.TR_DATE, A.PT_ID,A.PRICE,
        case
            when B.Status is not null then B.STATUS
            else A.Status
        end STATUS, 
        case
            when B.Status is not null then B.EMP_ID
            else A.EMP_ID
        end EMP_ID, 
        case
            when B.Status is not null then B.LAN_ID
            else A.LAN_ID
        end
            LAN_ID, 
        case
            when B.Status is not null then B.LOC_ID
            else A.LOC_ID
        end
            LOC_ID 
       
       from (
         
        -- ************ To Get Active Records ************ ---
           
        select
        MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, trunc(TR_DATE) as TR_DATE, EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID,ODS_LUD
        from 
        APP_SHARE.CAT
        where 
        TO_CHAR (trunc(TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1))
        and status = 'ACTIVE'
        ) A
    
       left join
        (
    
        -- ********* To Get COR_ACTIVE RECORDS ************ -- 
    
        select /*+ DRIVING_SITE(cass) PARALLEL(cass) */ 
        MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, trunc(TR_DATE) as TR_DATE, 
        EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID, 
        ODS_LUD
    
        from 
        APP_SHARE.CAT
        where 
        TO_CHAR (trunc(TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1))
        and status = 'COR-ACTIVE'
        ) B
    
        on A.MD_ID=B.MD_ID
    )
    
    -- ************** NEED TO JOIN ACT TABLE to Other DataSource Tables  ************** ---------------
    
    -- **** The Problem is when I ran the ACT it is running fast and also when I ran individual queries below are running fine. Only problem with the Joins I believe *******
    
    SELECT   
        ACT.*,
        B.CUST_ID,
        case
        when CST.BUSINESS_NM = '{NA}' then CST.CUST_FIRST_NM||' '||CST.CUST_LAST_NM
        else CST.BUSINESS_NM
        end as CUST_NAME,
        Cst.CUST_TYPE_DESC as CUST_TYP_DESC, 
        Cst.CUST_SUB_TYPE_DESC as CUST_SUB_TYPE_DESC,
        to_char(ACT.TR_DATE,'Month-yyyy') as REPORT_DATE ,
        assc.EMP_FIRST_NM as ASOC_FST_NM, 
        assc.EMP_LAST_NM as ASOC_LAST_NM, 
        assc.JOB_TITLE_NM as POSN_NM,
        assc.EMP_TYPE_DESC,
        fac.CURR_LOC_NM as FAC_NM,
       -- fac.FAC_DESC,
        fac.CUR_COMP_CD as BE_CD,
        fac.CUR_COMP_NM as BE_NM,
        fac.LOC_TYPE_DESC as FAC_TYP_DESC,
        fac.CITY_NM, 
        fac.CURR_MARKET_NM as MKT_NM, 
        fac.CURR_REGION_NM as RGN_NM, 
        fac.CURR_SC_LVL_1_DESC as SC_LVL_1_DESC, 
        fac.CURR_SC_LVL_2_DESC as SC_LVL_2_DESC, 
        fac.CURR_SC_LVL_3_DESC as SC_LVL_3_DESC, 
        fac.STATE_CD as ST_CD, 
        fac.LINE_1_ADDR as STRT_LINE_1_ADDR, 
        fac.LINE_2_ADDR as STRT_LINE_2_ADDR, 
        fac.CURR_TR_NM as TERR_NM, 
        fac.ZIP5_CD as ZIP_5_ZIP_CD
    
    
    FROM ACT 
      LEFT JOIN
        (
        SELECT
            S.SUB_ID, MASTER_SUB_ID, SUB_MDN, CUST_ID, ROW_EFF_DT, ROW_EXP_DT,
            TRUNC(NVL(SUB_AT_DT,ROW_UPDT_DT)) SUB_ACT_BEG,
            TRUNC(NVL(LEAD(SUB_AT_DT) OVER (PARTITION BY SUB_MDN ORDER BY SUB_AT_DT)-1,TO_DATE('12/31/9999','MM/DD/YYYY'))) AS SUB_ACT_END
        FROM DIM_SUB S
        WHERE SUB_ID>0
        ) B
    
       --  ********* Joining ACT Table with DIM_SUB  ************ --
    
        ON TO_CHAR(ACT.CTN)=TO_CHAR(B.SUB_MDN)
        AND ACT.TR_DATE BETWEEN SUB_ACT_BEG AND SUB_ACT_END
    
    LEFT JOIN 
        DIM_CUST Cst
    
            --  ********* Joining CUST Table with DIM_SUB and indirectly it joins to ACT  ************ --
    
            ON Cst.CUST_ID=B.CUST_ID
            AND ACT.TR_DATE BETWEEN Cst.ROW_EFF_DT AND Cst.ROW_EXP_DT
    
    
            --  ********* Joining DIM_LOC Table with ACT  ************ --
    
    left join
        DWDPL.DIM_LOC fac
            on fac.LOC_ID = ACT.LOC_ID
            and  trunc(fac.ROW_EXP_DT) = to_date('DEC-31-9999','MM/DD/YYYY')
    
    
     --  ********* Joining DIM_EMP Table with ACT  ************ --
    
    left join
        DWDPL.DIM_EMP assc
            on assc.EMP_ID=ACT.EMP_ID
            and trunc(assc.ROW_EXP_DT) = to_date('DEC-31-9999','MM/DD/YYYY')
    
    
         --  ********* This Join is used not to show the Cancel Status Records in ACT  ************ --
    
    left join
        (select 
            MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, TR_DATE, 
            EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID, 
            ODS_LUD
        from APP_SHARE.CAT
        where 
        TO_CHAR (trunc(Cass.TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1))
        and status = 'CANCEL'
       ) C
    on ACT.MD_ID=C.MD_ID
    where C.status is null
    
    


    K.Kiran

    Friday, May 25, 2018 2:02 PM
  • Hi Kiran,

    That helped a lot. Try this:

    select A.MD_ID, A.SR_ID, A.SR_DESC, A.ORDER_ID, A.CTN, A.TR_DATE, A.PT_ID,A.PRICE, case when B.Status is not null then B.STATUS else A.Status end STATUS, case when B.Status is not null then B.EMP_ID else A.EMP_ID end EMP_ID, case when B.Status is not null then B.LAN_ID else A.LAN_ID end LAN_ID, case when B.Status is not null then B.LOC_ID else A.LOC_ID end LOC_ID

    into Act_1 /*** CREATING A TABLE TO BE USED LATER ***/ from ( -- ************ To Get Active Records ************ --- select MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, trunc(TR_DATE) as TR_DATE, EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID,ODS_LUD from APP_SHARE.CAT where TO_CHAR (trunc(TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1)) and status = 'ACTIVE' ) A left join ( -- ********* To Get COR_ACTIVE RECORDS ************ -- select /*+ DRIVING_SITE(cass) PARALLEL(cass) */ MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, trunc(TR_DATE) as TR_DATE, EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID, ODS_LUD from APP_SHARE.CAT where TO_CHAR (trunc(TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1)) and status = 'COR-ACTIVE' ) B on A.MD_ID=B.MD_ID )

    I have removed the cte and instead stored the result into a table. Now use it to join with main query. Your rest of the query looks fine and I dont think needs to be broken down into similar tables. Drop Act_1 once you are done I dont think Oracle supports creating temp tables like sql server unless something changed. Good luck.

    Soumen




    Friday, May 25, 2018 2:25 PM
  • Hi Soumen,

    Thank you so much for your reply,

    I have done some diagnose in Oracle and the lines which are in the BOLD are causing the issue. If I comment those rows - I can see the output in 15 secs.

    If I don't comment - the query execution is 7 minutes.

    I need to change these lines in different way and see if its works fine, I can see push this query from Oracle to SQL server. Please let me know if there is any alternate way to implement those two line.

    -- To get Active (or) COR-Active Records.
    -- Priority a) if a particular MD_ID, SR_ID, ORDER_ID has 'COR-ACTIVE' and 'ACTIVE' Status then we will show 'COR-ACTIVE' b) if a particular MD_ID, SR_ID, ORDER_ID has only has 'ACTIVE' Status then we will show 'ACTIVE', thats the reason we have used left Join for ACTIVE.
    
    with ACT as 
       (
       select 
        A.MD_ID, A.SR_ID, A.SR_DESC, A.ORDER_ID, A.CTN, A.TR_DATE, A.PT_ID,A.PRICE,
        case
            when B.Status is not null then B.STATUS
            else A.Status
        end STATUS, 
        case
            when B.Status is not null then B.EMP_ID
            else A.EMP_ID
        end EMP_ID, 
        case
            when B.Status is not null then B.LAN_ID
            else A.LAN_ID
        end
            LAN_ID, 
        case
            when B.Status is not null then B.LOC_ID
            else A.LOC_ID
        end
            LOC_ID 
       
       from (
         
        -- ************ To Get Active Records ************ ---
           
        select
        MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, trunc(TR_DATE) as TR_DATE, EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID,ODS_LUD
        from 
        APP_SHARE.CAT
        where 
        TO_CHAR (trunc(TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1))
        and status = 'ACTIVE'
        ) A
    
       left join
        (
    
        -- ********* To Get COR_ACTIVE RECORDS ************ -- 
    
        select /*+ DRIVING_SITE(cass) PARALLEL(cass) */ 
        MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, trunc(TR_DATE) as TR_DATE, 
        EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID, 
        ODS_LUD
    
        from 
        APP_SHARE.CAT
        where 
        TO_CHAR (trunc(TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1))
        and status = 'COR-ACTIVE'
        ) B
    
        on A.MD_ID=B.MD_ID
    )
    
    -- ************** NEED TO JOIN ACT TABLE to Other DataSource Tables  ************** ---------------
    
    -- **** The Problem is when I ran the ACT it is running fast and also when I ran individual queries below are running fine. Only problem with the Joins I believe *******
    
    SELECT   
        ACT.*,
        B.CUST_ID,
        case
        when CST.BUSINESS_NM = '{NA}' then CST.CUST_FIRST_NM||' '||CST.CUST_LAST_NM
        else CST.BUSINESS_NM
        end as CUST_NAME,
        Cst.CUST_TYPE_DESC as CUST_TYP_DESC, 
        Cst.CUST_SUB_TYPE_DESC as CUST_SUB_TYPE_DESC,
        to_char(ACT.TR_DATE,'Month-yyyy') as REPORT_DATE ,
        assc.EMP_FIRST_NM as ASOC_FST_NM, 
        assc.EMP_LAST_NM as ASOC_LAST_NM, 
        assc.JOB_TITLE_NM as POSN_NM,
        assc.EMP_TYPE_DESC,
        fac.CURR_LOC_NM as FAC_NM,
       -- fac.FAC_DESC,
        fac.CUR_COMP_CD as BE_CD,
        fac.CUR_COMP_NM as BE_NM,
        fac.LOC_TYPE_DESC as FAC_TYP_DESC,
        fac.CITY_NM, 
        fac.CURR_MARKET_NM as MKT_NM, 
        fac.CURR_REGION_NM as RGN_NM, 
        fac.CURR_SC_LVL_1_DESC as SC_LVL_1_DESC, 
        fac.CURR_SC_LVL_2_DESC as SC_LVL_2_DESC, 
        fac.CURR_SC_LVL_3_DESC as SC_LVL_3_DESC, 
        fac.STATE_CD as ST_CD, 
        fac.LINE_1_ADDR as STRT_LINE_1_ADDR, 
        fac.LINE_2_ADDR as STRT_LINE_2_ADDR, 
        fac.CURR_TR_NM as TERR_NM, 
        fac.ZIP5_CD as ZIP_5_ZIP_CD
    
    
    FROM ACT 
      LEFT JOIN
        (
        SELECT
            S.SUB_ID, MASTER_SUB_ID, SUB_MDN, CUST_ID, ROW_EFF_DT, ROW_EXP_DT,
            TRUNC(NVL(SUB_AT_DT,ROW_UPDT_DT)) SUB_ACT_BEG,
            TRUNC(NVL(LEAD(SUB_AT_DT) OVER (PARTITION BY SUB_MDN ORDER BY SUB_AT_DT)-1,TO_DATE('12/31/9999','MM/DD/YYYY'))) AS SUB_ACT_END
        FROM DIM_SUB S
        WHERE SUB_ID>0
        ) B
    
       --  ********* Joining ACT Table with DIM_SUB  ************ --
    
        ON TO_CHAR(ACT.CTN)=TO_CHAR(B.SUB_MDN)
        AND ACT.TR_DATE BETWEEN SUB_ACT_BEG AND SUB_ACT_END
    
    LEFT JOIN 
        DIM_CUST Cst
    
            --  ********* Joining CUST Table with DIM_SUB and indirectly it joins to ACT  ************ --
    
            ON Cst.CUST_ID=B.CUST_ID
            AND ACT.TR_DATE BETWEEN Cst.ROW_EFF_DT AND Cst.ROW_EXP_DT
    
    
            --  ********* Joining DIM_LOC Table with ACT  ************ --
    
    left join
        DWDPL.DIM_LOC fac
            on fac.LOC_ID = ACT.LOC_ID
            and  trunc(fac.ROW_EXP_DT) = to_date('DEC-31-9999','MM/DD/YYYY')
    
    
     --  ********* Joining DIM_EMP Table with ACT  ************ --
    
    left join
        DWDPL.DIM_EMP assc
            on assc.EMP_ID=ACT.EMP_ID
            and trunc(assc.ROW_EXP_DT) = to_date('DEC-31-9999','MM/DD/YYYY')
    
    
         --  ********* This Join is used not to show the Cancel Status Records in ACT  ************ --
    
    left join
        (select 
            MD_ID, SR_ID, SR_DESC, ORDER_ID, STATUS, CTN, TR_DATE, 
            EMP_ID, LAN_ID, LOC_ID, PT_ID, PRICE/100 as PRICE, BT_REC_SEQ_NO, BT_ID, 
            ODS_LUD
        from APP_SHARE.CAT
        where 
        TO_CHAR (trunc(Cass.TR_DATE,'MM')) = TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1))
        and status = 'CANCEL'
       ) C
    on ACT.MD_ID=C.MD_ID
    where C.status is null

    Thanks!

    Kiran


    Saturday, May 26, 2018 8:31 PM
  • Hi Kiran,

    Is there any difference in query output if you comment those out vs not commenting out? If those date ranges don't make any difference in output than you may not need them. Besides did you try my suggestion and run the query joining with table instead of using cte? I would like you to do some performance tests and find out how different queries perform. Rgds,

    Soumen  


    Sunday, May 27, 2018 4:27 AM
  • Hi Soumen,

    I tried based on your suggestion and oracle is throwing an error. Tomorrow I will go to office and send you the screenshot.

    Yes, there is a difference in the output if I comment those lines. It is populating more records for each OR_ID but the query execution is fast.

    Basically, I have to pick only one Record Based on ROW_UPDT_DT (OR) ACT_DT for the below scenario. This will solve my problem.

    1) if ACT_DT is NOT NULL then I have to pick the Record based on MAX(ACT_DT)

    2) if ACT_DT is NULL, then we have to consider the MAX(ROW_UPDT_DT) which should be not be less than any other ACT_DT for the SAME OR_ID.

    SR_ID SR_DESCRIPTION OR_ID CTN TR_DATE PARTNER_ID CUST_ID ROW_UPDT_DT ACT_DT
    CA Bundle A1002 265 4/30/2018   0:00 Soft 214,325,492 10/7/2016 11:34 3/13/2015   17:06
    CA Bundle A1002 265 4/30/2018   0:00 Soft 850,340,283 10/14/2013 17:10 10/14/2013   17:03
    CA Bundle A1002 265 4/30/2018   0:00 Soft 850,340,283 1/8/2015   0:00 10/14/2013   0:00
    CA Bundle A1002 265 4/30/2018   0:00 Soft 201,593,024 2/23/2018   9:34 2/23/2018   9:32

    Thanks!

    Kiran


    K.Kiran

    Sunday, May 27, 2018 4:44 AM
  • Hi Kiran,

    I didn't know about the logic such as "if ACT_DT is NULL, then we have to consider the MAX(ROW_UPDT_DT) which should be not be less than any other ACT_DT for the SAME OR_ID" etc. Obviously there are some logic going on here and you can't delete the lines.

    Basically its looking at the begin date of next row and subtracting 1 day from it (making it end date) and using it as date ranges. Rgds,

    Soumen



    Sunday, May 27, 2018 5:14 AM
  • Hi Soumen, After this Join Condition. ON TO_CHAR(ACT.CTN)=TO_CHAR(B.SUB_MDN) We have to check if ACT_DT is Null then We will have to use max(Row updated Date). Otherwise we have to max(ACT_DT) The business said, We can ignore SUB_ACT_BEG AND SUB_ACT_END Just we need max date row based on ACT_DT / ROW_UPDT_DT based on my Example. Thanks! Kiran

    K.Kiran

    Sunday, May 27, 2018 5:41 AM
  • Hi Kiran,

    Based on your suggestion I have made the following changes. Hopefully it will work. Rgds,

    Soumen


    S.SUB_ID, MASTER_SUB_ID, SUB_MDN, CUST_ID, ROW_EFF_DT, ROW_EXP_DT,
            TRUNC(NVL(SUB_AT_DT,ROW_UPDT_DT)) SUB_ACT_BEG,

    max(nvl(SUB_AT_DT,ROW_UPDT_DT)) over (partition by SUB_MDN) AS SUB_ACT_END

            /* TRUNC(NVL(LEAD(SUB_AT_DT) OVER (PARTITION BY SUB_MDN ORDER BY SUB_AT_DT)-1,TO_DATE('12/31/9999','MM/DD/YYYY'))) AS SUB_ACT_END */
        FROM DIM_SUB S
        WHERE SUB_ID>0
    ) B
    ON TO_CHAR(ACT.CTN)=TO_CHAR(B.SUB_MDN)
        AND ACT.TR_DATE BETWEEN SUB_ACT_BEG AND SUB_ACT_END 
    Sunday, May 27, 2018 6:07 AM
  • It really helped me a lot. Thank you very much Soumen!

    You are a rock star.


    K.Kiran

    Tuesday, May 29, 2018 6:06 PM