Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Question Unanswered: tuning sql query

    hi

    i have a query which takes 106 seconds to execute 6 rows in pl/sql developer

    i m using dblink in this query.

    can u help me to tune this query?????

    SELECT
    /*+DRIVING_SITE index(clrrpf_pk) index(clntpf_pk) index(ind_chdrpf_chdrnum) index(clntpf_pk) index(hpadpf_pk) index(payrpf_pk)*/
    clnt.CLIENTID,policy.policyid,clnt.GIVENNAME AS NAME,clnt.SURNAME AS CLINTSURNAME,CLI.SALUTL AS SALUTATION ,CLI.GIVNAME AS GIVENNAME,CLI.SURNAME,
    CLI.CLTADDR01 AS ADDRESSLINE1,
    CLI.CLTADDR02 AS ADDRESSLINE2,
    CLI.CLTADDR03 AS ADDRESSLINE3,
    CLI.CLTADDR04 AS ADDRESSLINE4,
    CLI.CLTADDR05 AS ADDRESSLINE5,
    (select DESCRIPTION from m_ps_letterinfo
    where m_ps_letterinfo.CLIENTID=CLI.CLNTNUM AND CREATEDDATE IN
    (SELECT MAX(CREATEDDATE) FROM M_PS_LETTERINFO) ) as "DESCRIPTION",
    CLI.CLTPCODE AS PINCODE,
    CLI.CLTPHONE01 AS PHONENO1,
    (select NVL(max(CREATEDDATE),SYSDATE) from m_ps_letterinfo
    where m_ps_letterinfo.CLIENTID=CLI.CLNTNUM and SERREQSTATUS='M' ) as printdate,
    trim(CLI.SALUTL)||' '||CLI.GIVNAME||' '||CLI.SURNAME AS ASIGNEENAME,
    DECODE(C1.BILLCHNL,'D','YES','NO') AS ECSSTATUS,
    TO_CHAR(C2.HISSDTE,'DD/MM/YYYY') AS HISSDATE,
    (select DESCRIPTION from M_PS_MODOFPAYMENT where trim(M_PS_MODOFPAYMENT.ITEM)=trim(C1.BILLCHNL)) AS MOD_OF_PAY,
    trim(CLI.SALUTL)||' '||CLI.GIVNAME||' '||CLI.SURNAME as OWNERNAME,
    C1.CNTTYPE AS PLANNAME,
    C1.CHDRNUM as POLICYNO,
    (select SHORTDESCRIPTION from M_PS_PREMIUNSTATUS where trim(M_PS_PREMIUNSTATUS.ITEM)=trim(C1.STATCODE)) AS POLICYSTATUS,
    C4.OUTSTAMT AS PREMIUMAMOUNT,
    (select SHORTDESCRIPTION from M_PS_PREMIUNSTATUS where trim(M_PS_PREMIUNSTATUS.ITEM)=trim(C1.PSTATCODE)) AS PREMIUMSTATUS,
    to_char(C1.PTDATE,'DD/MM/YYYY') AS PTDDATE,
    TO_CHAR(C1.BTDATE,'DD/MM/YYYY') AS DUEDATE

    FROM
    M_ONLINE_CLIENTINFO clnt,M_ONLINE_CLNT_ADD_DETL detl,M_PS_POLICYINFO policy,
    CLNTPF@ODS_UAT_PS CLI,CHDRPF@ODS_UAT_PS C1,
    HPADPF@ODS_UAT_PS C2 ,
    PAYRPF@ODS_UAT_PS C4,CLRRPF@ODS_UAT_PS C5
    WHERE
    trim(C5.CLNTNUM)=trim(CLI.CLNTNUM) AND
    C5.CLNTPFX =CLI.CLNTPFX AND
    C5.CLNTCOY=CLI.CLNTCOY AND
    c5.forepfx=C1.chdrpfx AND
    c5.forecoy=C1.chdrcoy AND
    trim(c5.forenum)=trim(C1.chdrnum) AND
    C1.CHDRNUM=C2.CHDRNUM AND
    C1.CHDRNUM=C4.CHDRNUM and
    clnt.CLIENTID=detl.CLIENTID AND
    clnt.CLIENTID=policy.OWNERNAME and
    C1.CHDRNUM=policy.policyid AND
    C1.VALIDFLAG =1 AND
    C5.CLRRROLE='DA' AND
    C1.chdrnum NOT IN (SELECT C3.chdrnum FROM ASGNPF@ODS_UAT_PS C3 )

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    either trim up your actual data so you can remove the functions from
    the where clause or create a function based index on the columns.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •