3 Replies Latest reply: Nov 26, 2012 7:04 AM by Most Wanted!!!! RSS

    Please help me in tuning the query....

    889383
      SELECT   /*+ LEADING (WCL_EXPO) */
               MAX (a.clm_id) clm, a.clm_nbr, b.expo_nbr, b.entps_covg_cd_id,
               'open' status, a.uw_cmpy_map_id, a.rt_st_id, a.src_sys_id, suit_nbr
          FROM wcl_clm a,
               wcl_expo b,
               (SELECT DISTINCT clm_fil_nbr, expo_nbr, suit_nbr
                           FROM wcl_suit
                          WHERE ((litg_opn_dtm BETWEEN TO_DATE ('12/31/2010',
                                                                'MM/DD/YYYY'
                                                               )
                                                   AND TO_DATE ('12/30/2011',
                                                                'MM/DD/YYYY'
                                                               )
                                 )
                                )) c,
               wrd_st st,
               wrd_src_sys s
         WHERE a.clm_nbr = b.clm_nbr
           AND st.st_id = a.rt_st_id
           AND b.src_sys_id = a.src_sys_id
           AND b.clm_nbr = c.clm_fil_nbr
           AND b.expo_nbr = c.expo_nbr
           AND a.lob_map_id IN (18, 19)
           AND a.src_sys_id IN (1, 3, 4, 12, 13)
           AND a.pol_nbr IS NOT NULL
           AND a.row_log_del_ind = 'N'
      GROUP BY a.clm_nbr,
               b.entps_covg_cd_id,
               b.expo_nbr,
               a.uw_cmpy_map_id,
               a.rt_st_id,
               a.src_sys_id,
               suit_nbr
        • 1. Re: Please help me in tuning the query....
          Hoek
          Please add the information we need as described in the two links you'll find here: {message:id=9360003}
          Make sure to read them, tuning is more than looking at a query, we need execution plans/tkprof output, database version, information regarding indexes, data distribution and so on. Start reading the links.
          • 2. Re: Please help me in tuning the query....
            ranit B
            886380 wrote:
            SELECT   /*+ LEADING (WCL_EXPO) */
            MAX (a.clm_id) clm, a.clm_nbr, b.expo_nbr, b.entps_covg_cd_id,
            'open' status, a.uw_cmpy_map_id, a.rt_st_id, a.src_sys_id, suit_nbr
            FROM wcl_clm a,
            wcl_expo b,
            (SELECT DISTINCT clm_fil_nbr, expo_nbr, suit_nbr
            FROM wcl_suit
            WHERE ((litg_opn_dtm BETWEEN TO_DATE ('12/31/2010',
            'MM/DD/YYYY'
            )
            AND TO_DATE ('12/30/2011',
            'MM/DD/YYYY'
            )
            )
            )) c,
            wrd_st st,
            wrd_src_sys s
            WHERE a.clm_nbr = b.clm_nbr
            AND st.st_id = a.rt_st_id
            AND b.src_sys_id = a.src_sys_id
            AND b.clm_nbr = c.clm_fil_nbr
            AND b.expo_nbr = c.expo_nbr
            AND a.lob_map_id IN (18, 19)
            AND a.src_sys_id IN (1, 3, 4, 12, 13)
            AND a.pol_nbr IS NOT NULL
            AND a.row_log_del_ind = 'N'
            GROUP BY a.clm_nbr,
            b.entps_covg_cd_id,
            b.expo_nbr,
            a.uw_cmpy_map_id,
            a.rt_st_id,
            a.src_sys_id,
            suit_nbr
            Hi,

            [1]
            I can see no columns are being selected in outer SELECT from Tables - WRD_ST & WRD_SRC_SYS.
            Please remove it from the FROM clause and use an EXISTS.

            [2]
            Where is the join for Table - WRD_SRC_SYS ???

            This might help. Please try.
            Ranit B.

            Edited by: ranit B on Nov 23, 2012 12:33 PM
            -- added [2]
            • 3. Re: Please help me in tuning the query....
              Most Wanted!!!!
              SELECT   /*+ LEADING (WCL_EXPO) */
                       MAX (a.clm_id) clm, a.clm_nbr, b.expo_nbr, b.entps_covg_cd_id,
                       'open' status, a.uw_cmpy_map_id, a.rt_st_id, a.src_sys_id, suit_nbr
                  FROM wcl_clm a,
                       wcl_expo b,
                       (SELECT DISTINCT clm_fil_nbr, expo_nbr, suit_nbr
                                   FROM wcl_suit
                                  WHERE ((litg_opn_dtm BETWEEN TO_DATE ('12/31/2010',
                                                                        'MM/DD/YYYY'
                                                                       )
                                                           AND TO_DATE ('12/30/2011',
                                                                        'MM/DD/YYYY'
                                                                       )
                                         )
                                        )) c
              --         wrd_st st--,
              ----         wrd_src_sys s --not used any where
              WHERE    a.clm_nbr = b.clm_nbr
              --     AND st.st_id = a.rt_st_id
                   AND b.src_sys_id = a.src_sys_id
                   AND b.clm_nbr = c.clm_fil_nbr
                   AND b.expo_nbr = c.expo_nbr
                   AND a.lob_map_id IN (18, 19)
                   AND a.src_sys_id IN (1, 3, 4, 12, 13)
                   AND a.pol_nbr IS NOT NULL
                   AND a.row_log_del_ind = 'N'
                   AND EXISTS (SELECT 1
                                 FROM wrd_st st
                                WHERE st.st_id = a.rt_st_id)
              GROUP BY a.clm_nbr,
                       b.entps_covg_cd_id,
                       b.expo_nbr,
                       a.uw_cmpy_map_id,
                       a.rt_st_id,
                       a.src_sys_id,
                       suit_nbr
              regards,
              Friend