Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: performance related

    Hi Tony Andrews....

    Iam sending the qry which needs to be optimized ....



    select
    d.DECISION_ID,
    d.LAST_MODIFIED_DATE, d.TRACKING_ID, d.HQ_DUNS, d.COMPANY_NAME,
    d.AMOUNT_REQUESTED, d.OUTSTANDING_BALANCE, d.CREDIT_AMOUNT_COMPUTED,
    d.DECISION, d.RISK_LEVEL,d.APP_ID, d.UPDATED_BY, d.SIC_CODE, d.GUID,
    d.RULE_ID , d.ORIG_DECISION, d.ORIG_CREDIT, d.PRODUCT_NAME, d.FILE_ID
    from
    DE2_DECISION_HISTORY d
    where
    d.CREATED_BY='ruby' and
    d.LAST_MODIFIED_DATE between to_date('2003-01-01','YYYY-MM-DD') and to_date('2003-03-31','YYYY-MM-DD')
    union
    select
    d.DECISION_ID,
    d.LAST_MODIFIED_DATE, d.TRACKING_ID, d.HQ_DUNS, d.COMPANY_NAME,
    d.AMOUNT_REQUESTED, d.OUTSTANDING_BALANCE, d.CREDIT_AMOUNT_COMPUTED,
    d.DECISION, d.RISK_LEVEL,d.APP_ID, d.UPDATED_BY, d.SIC_CODE, d.GUID,
    d.RULE_ID , d.ORIG_DECISION, d.ORIG_CREDIT, d.PRODUCT_NAME, d.FILE_ID
    from
    DE2_DECISION_HISTORY d, DM2_RULE_HDR h
    where
    d.RULE_ID = h.RULE_ID and
    (h.rule_name in (
    select rule_name from dm2_rule_users r, de2_user u
    where u.ga_user = r.ga_user and r.ga_user='ruby' and r.created_by != 'D\&B'
    and r.ga_contract in ( '263721574' , '0')
    and (substr(r.permissions, 1, 1)='1' or substr(r.permissions, 2, 1)='1')
    and substr(u.permissions, 3, 1) = '1'
    union
    select rule_name from dm2_rule_users r, de2_user u where u.ga_user = r.ga_user and r.ga_user='ruby'
    and r.ga_contract in ( '263721574' , '0')
    and r.created_by = 'D\&B' and substr(u.permissions, 3, 1) = '1' ))
    and d.updated_by in (select ga_user from de2_user where contract = '263721574' )
    and d.last_modified_date between to_date('2003-01-01','YYYY-MM-DD') and to_date('2003-03-31','YYYY-MM-DD')


    Thanks & Regards

    Bujji

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: performance related

    Originally posted by vadlamanibujji
    Hi Tony Andrews....

    Iam sending the qry which needs to be optimized ....



    select
    d.DECISION_ID,
    d.LAST_MODIFIED_DATE, d.TRACKING_ID, d.HQ_DUNS, d.COMPANY_NAME,
    d.AMOUNT_REQUESTED, d.OUTSTANDING_BALANCE, d.CREDIT_AMOUNT_COMPUTED,
    d.DECISION, d.RISK_LEVEL,d.APP_ID, d.UPDATED_BY, d.SIC_CODE, d.GUID,
    d.RULE_ID , d.ORIG_DECISION, d.ORIG_CREDIT, d.PRODUCT_NAME, d.FILE_ID
    from
    DE2_DECISION_HISTORY d
    where
    d.CREATED_BY='ruby' and
    d.LAST_MODIFIED_DATE between to_date('2003-01-01','YYYY-MM-DD') and to_date('2003-03-31','YYYY-MM-DD')
    union
    select
    d.DECISION_ID,
    d.LAST_MODIFIED_DATE, d.TRACKING_ID, d.HQ_DUNS, d.COMPANY_NAME,
    d.AMOUNT_REQUESTED, d.OUTSTANDING_BALANCE, d.CREDIT_AMOUNT_COMPUTED,
    d.DECISION, d.RISK_LEVEL,d.APP_ID, d.UPDATED_BY, d.SIC_CODE, d.GUID,
    d.RULE_ID , d.ORIG_DECISION, d.ORIG_CREDIT, d.PRODUCT_NAME, d.FILE_ID
    from
    DE2_DECISION_HISTORY d, DM2_RULE_HDR h
    where
    d.RULE_ID = h.RULE_ID and
    (h.rule_name in (
    select rule_name from dm2_rule_users r, de2_user u
    where u.ga_user = r.ga_user and r.ga_user='ruby' and r.created_by != 'D\&B'
    and r.ga_contract in ( '263721574' , '0')
    and (substr(r.permissions, 1, 1)='1' or substr(r.permissions, 2, 1)='1')
    and substr(u.permissions, 3, 1) = '1'
    union
    select rule_name from dm2_rule_users r, de2_user u where u.ga_user = r.ga_user and r.ga_user='ruby'
    and r.ga_contract in ( '263721574' , '0')
    and r.created_by = 'D\&B' and substr(u.permissions, 3, 1) = '1' ))
    and d.updated_by in (select ga_user from de2_user where contract = '263721574' )
    and d.last_modified_date between to_date('2003-01-01','YYYY-MM-DD') and to_date('2003-03-31','YYYY-MM-DD')


    Thanks & Regards

    Bujji
    So what does the output from EXPLAIN PLAN look like?
    And how big is each table (rows)?

Posting Permissions

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