Home » RDBMS Server » Performance Tuning » help me tune this (oracle 11g)
help me tune this [message #498768] Fri, 11 March 2011 00:58 Go to next message
newbie69
Messages: 2
Registered: March 2011
Location: manila
Junior Member
   SELECT a.polno, parsename_temp (f.namestr, 'LFM', 'FML') insured,
          parsename_temp (h.namestr, 'LFM', 'FML') owner, d.poldate,
          fngetrefdesc (b.paymode) paymode,
            fngetmodalpremptd (a.polno, i.duedate)
          + fngetmodaldstptd (a.polno, i.duedate) modalprem,
          fngetpolicyyr (i.duedate, d.poldate) polyr,
          fngetrefdesc (c.planmstdesc) PLAN,
          fngetrefdesc (c.currency) currency, i.duedate, i.valuedate,
          i.premamt, fngetrefdesc (a.polstat) polstat,
          CASE a.polstat
             WHEN fngetrefseqno ('POLICYSTAT_D_DEATH_CLAIM')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_ASSURED_DEATH_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_E_EXTENDED')
                THEN fngetpoldate (a.polno, NULL,
                                   'POLICYDATE_ETI_EFFECTIVITY')
             WHEN fngetrefseqno ('POLICYSTAT_I_INFORCE')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_POLICY_EFFECTIVITY_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_L_LAPSED')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_POLICY_LAPSE_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_M_MATURED')
                THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_MATURITY_DATE')
             WHEN fngetrefseqno ('POLICYSTAT_P_PAID_UP')
                THEN fngetpoldate (a.polno, NULL, 'POLICYSTAT_P_PAID_UP')
             WHEN fngetrefseqno ('POLICYSTAT_R_REDUCE_PAID_UP')
                THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_RPU_DATE')
             WHEN fngetrefseqno ('POLICYSTAT_S_SURRENDERED')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_POLICY_SURRENDER_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_T_POLICY_STATUS')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_TERMINATION_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_W_POLICY_STATUS')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_WAIVED_PREMIUM_EFFDATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_C_CANCELLED')
                THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_DATE_CANCELLED')
             WHEN fngetrefseqno ('POLICYSTAT_V_CONVERTED')
                THEN fngetpoldate (a.polno, NULL,
                                   'POLICYDATE_CONVERSION_DATE')
             WHEN fngetrefseqno ('POLICYSTAT_R_RESCINDED')
                THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_RESCIND_DATE')
             WHEN fngetrefseqno ('POLICYSTAT_CB_AVAILING')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_CB_AVAILING_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_AS_AUTO_SURRENDERED')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_POLICY_SURRENDER_DATE'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_E_EXPIRED')
                THEN fngetpoldate (a.polno, NULL, 'POLICYDATE_EXPIRED')
             WHEN fngetrefseqno ('POLICYSTAT_D_DEATH_CLAIM_PENDING')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_DEATH_CLAIM_PENDING'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_D_DEATH_CLAIM_DECLINED')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_DEATH_CLAIM_DECLINED'
                                  )
             WHEN fngetrefseqno ('POLICYSTAT_DP_DEATH_CLAIM_PROCESSED')
                THEN fngetpoldate (a.polno,
                                   NULL,
                                   'POLICYDATE_DEATH_CLAIM_PROCESSED'
                                  )
             ELSE NULL
          END statdate,
          parsename_temp (l.namestr, 'LFM', 'FML') AGENT,
          fngetagentbasebranchname (k.agtno) branch,
          CASE
             WHEN INSTR (i.remarks, 'APL ') > 0
                THEN 'Thru PDO - APL'
             WHEN INSTR (i.remarks, 'APD ') > 0
                THEN 'Thru PDO - APD'
             WHEN INSTR (i.remarks, 'RPU') > 0
                THEN 'Thru PDO - RPU'
             WHEN INSTR (i.remarks, 'ETI') > 0
                THEN 'Thru PDO - ETI'
             WHEN i.remarks IN
                            ('LAPSED POLICY', 'DAILYMODE COMPLETED')
                THEN 'LAPSED'
             WHEN TRUNC (i.duedate) = TRUNC (i.valuedate)
                THEN 'ON DUE DATE'
             WHEN TRUNC (i.valuedate) - TRUNC (i.duedate) <= 31
                THEN 'WITHIN CONTRACT GRACE'
             WHEN TRUNC (i.valuedate) - TRUNC (i.duedate) <= 45
                THEN 'WITHIN EXTENDED GRACE'
             WHEN TRUNC (i.valuedate) - TRUNC (i.duedate) > 45
                THEN 'BEYOND 45 DAYS'
             ELSE 'OTHERS'
          END reptype
     FROM inb_policymst a,
          inb_polcov_trn b,
          cnb_planmst c,
          inb_poldate_trn d,
          inb_polrole_trn e,
          cnb_namelst_trn f,
          inb_polrole_trn g,
          cnb_namelst_trn h,
          (SELECT   polno, valuedate duedate,            --trandate valuedate,
                    (CASE a1.reftype
                        WHEN 61700
                           THEN TRUNC (a1.trandate)
                        WHEN 61701
                           THEN TRUNC (a1.trandate)
                        WHEN 60893
                           THEN TRUNC (a1.trandate)
                        WHEN 60894
                           THEN (CASE
                                    WHEN TRUNC (a1.timestmp) < '11APR2007'
                                       THEN TRUNC (a1.trandate)
                                    ELSE (CASE TO_CHAR
                                                     ((SELECT opno
                                                         FROM xac_opmst
                                                        WHERE opno =
                                                                  a1.REFERENCE
                                                          AND timestmp >=
                                                                   '11APR2007')
                                                     )
                                             WHEN TO_CHAR (a1.REFERENCE)
                                                THEN TRUNC
                                                       ((SELECT valuedate
                                                           FROM xac_opmst
                                                          WHERE opno =
                                                                   a1.REFERENCE
                                                            AND timestmp >=
                                                                   '11APR2007')
                                                       )
                                             ELSE TRUNC (a1.trandate)
                                          END
                                         )
                                 END
                                )
                        WHEN 70520
                           THEN (CASE
                                    WHEN TRUNC (a1.timestmp) < '11APR2007'
                                       THEN TRUNC (a1.trandate)
                                    ELSE (CASE TO_CHAR
                                                     ((SELECT DISTINCT orno
                                                                  FROM xbc_ormst
                                                                 WHERE orno =
                                                                          a1.REFERENCE
                                                                   AND timestmp >=
                                                                          '11APR2007')
                                                     )
                                             WHEN TO_CHAR (a1.REFERENCE)
                                                THEN TRUNC
                                                       ((SELECT MAX (paydate)
                                                           FROM xbc_ormst
                                                          WHERE orno =
                                                                   a1.REFERENCE
                                                            AND timestmp >=
                                                                   '11APR2007')
                                                       )
                                             ELSE TRUNC (a1.trandate)
                                          END
                                         )
                                 END
                                )
                        WHEN 70521
                           THEN (CASE
                                    WHEN TRUNC (a1.timestmp) < '11APR2007'
                                       THEN TRUNC (a1.trandate)
                                    ELSE (CASE a1.source_type
                                             WHEN 60893
                                                THEN TRUNC (a1.trandate)
                                             WHEN 70521
                                                THEN (CASE a1.REFERENCE
                                                         WHEN TO_CHAR (0)
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'MM'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'RA'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'EA'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'AB'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'RE'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'LO'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         WHEN 'JP'
                                                            THEN TRUNC
                                                                   (a1.trandate
                                                                   )
                                                         ELSE (CASE TO_CHAR
                                                                      ((SELECT jvno
                                                                          FROM xac_jvmst
                                                                         WHERE jvno =
                                                                                  a1.REFERENCE
                                                                           AND status IN
                                                                                  (60088,
                                                                                   60087)
                                                                           AND timestmp >=
                                                                                  '11APR2007')
                                                                      )
                                                                  WHEN TO_CHAR
                                                                         (a1.REFERENCE
                                                                         )
                                                                     THEN TRUNC
                                                                            ((SELECT valuedate
                                                                                FROM xac_jvmst
                                                                               WHERE jvno =
                                                                                        a1.REFERENCE
                                                                                 AND status IN
                                                                                        (60088,
                                                                                         60087)
                                                                                 AND timestmp >=
                                                                                        '11APR2007')
                                                                            )
                                                                  ELSE TRUNC
                                                                         (a1.trandate
                                                                         )
                                                               END
                                                              )
                                                      END
                                                     )
                                             ELSE TRUNC (a1.trandate)
                                          END
                                         )
                                 END
                                )
                     END
                    ) valuedate,
                    CASE reftype
                       WHEN 60893
                          THEN (SELECT a2.remarks
                                  FROM cxx_autoproc_jobreport a2
                                 WHERE a2.polno = a1.polno
                                   AND a2.jobhist_seqno = a1.REFERENCE
                                   AND a2.ptd = a1.valuedate)
                       WHEN 70521
                          THEN CASE a1.source_type
                                 WHEN 60893
                                    THEN (SELECT a4.remarks
                                            FROM cxx_autoproc_jobreport a4
                                           WHERE a4.polno = a1.polno
                                             AND TO_CHAR (a4.jobhist_seqno) =
                                                                  a1.source_no
                                             AND a4.ptd = a1.valuedate)
                                 ELSE (SELECT a3.explanation
                                         FROM xac_jvmst a3
                                        WHERE a3.jvno = a1.REFERENCE
                                          AND ROWNUM = 1)
                              END
                       ELSE 'REGULAR PREMIUM PAYMENT'
                    END remarks,
                    reftype, source_type,
                    SUM (NVL (premamt, 0) + NVL (dst, 0)) premamt
               FROM xbc_prempay_trn a1
              WHERE NVL (reverse_flag, 'N') = 'N' AND reftype <> '60894'
           GROUP BY polno,
                    reftype,
                    REFERENCE,
                    valuedate,
                    trandate,
                    source_type,
                    source_no,
                    TRUNC (timestmp)
           UNION ALL
           SELECT b1.polno, b1.poldate duedate, b2.valuedate,
                  NVL
                     (b2.remarks,
                      CASE
                         WHEN fngetpolicystatus (b1.polno) = 101
                            THEN 'FULLY-PAID POLICY'
                         ELSE 'NOT YET DUE'
                      END
                     ) remarks,
                  NULL reftype, NULL source_type,
                    fngetmodalpremium (b1.polno)
                  + fngetmodaldst (b1.polno) premamt
             FROM inb_poldate_trn b1 LEFT OUTER JOIN cxx_autoproc_jobreport b2
                  ON b1.polno = b2.polno
                AND b1.poldate = b2.ptd
                AND b2.remarks IN
                       ('LAPSED POLICY', 'ETI PROCESS COMPLETED',
                        'RPU PROCESS COMPLETED')
            WHERE b1.datetype = 148
              AND b1.poldate NOT IN (SELECT b3.valuedate
                                       FROM xbc_prempay_trn b3
                                      WHERE b3.polno = b1.polno)) i,
          inb_polrole_trn j,
          xag_profile k,
          cnb_namelst_trn l
    WHERE a.polno = b.polno
      AND b.enddate IS NULL
      AND b.plancode = c.plancode
      AND c.typecode = 367
      AND a.polno = d.polno
      AND d.datetype = 147
      AND a.polno = e.polno
      AND e.enddate IS NULL
      AND e.pertype = 180
      AND e.nameid = f.nameid
      AND a.polno = g.polno
      AND g.enddate IS NULL
      AND g.pertype = 180
      AND g.nameid = h.nameid
      AND a.polno = i.polno
      AND a.polno = j.polno
      AND  i.duedate  between '01jul2010' and '15jul2010' 
      AND j.pertype = 174
      AND j.enddate IS NULL
      AND j.nameid =
             CASE (SELECT DISTINCT COUNT (*)
                              FROM inb_polrole_trn
                             WHERE polno = a.polno
                               AND pertype = 174
                               AND enddate IS NULL)
                WHEN 1
                   THEN j.nameid
                ELSE CASE (SELECT COUNT (DISTINCT dist)
                             FROM inb_polrole_trn
                            WHERE polno = a.polno
                              AND pertype = 174
                              AND enddate IS NULL)
                WHEN 1
                   THEN j.nameid
                ELSE (SELECT nameid
                        FROM inb_polrole_trn
                       WHERE polno = a.polno
                         AND pertype = 174
                         AND enddate IS NULL
                         AND dist = 100)
             END
             END
      AND j.nameid = k.nameid
      AND j.nameid = l.nameid
      AND fnagentstattodate (k.agtno, SYSDATE) IN (78, 79, 85, 607)
      AND (d.poldate > i.duedate or d.poldate < i.duedate)
      AND i.remarks NOT IN
             ('NOT YET DUE', 'FULLY-PAID POLICY',
              'Change in Mode ' || a.polno)
      AND (     (  fngetmodalpremptd (a.polno, i.duedate)
                 + fngetmodaldstptd (a.polno, i.duedate)
                )
              - i.premamt <= .01
           OR i.reftype = 70520
           OR i.source_type = 60893
          );


Re: help me tune this [message #498799 is a reply to message #498768] Fri, 11 March 2011 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
You want us to help tune a 374 line sql statement, with lots of function calls and complicated case statemnts, while we know nothing of the tables, data or indexes involved?
Read the sticky at the top of this forum and post the required information and we'll see what we can do. One thing I did notice:
SELECT DISTINCT COUNT(*)
The distinct is pointless.
Re: help me tune this [message #498806 is a reply to message #498799] Fri, 11 March 2011 04:30 Go to previous message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
Stop relying on implicit conversion.

Or get your date/number fields sorted out.
Previous Topic: query issue
Next Topic: Update in a Loop
Goto Forum:
  


Current Time: Fri Dec 19 16:10:20 CST 2014

Total time taken to generate the page: 0.21808 seconds