Results 1 to 4 of 4

Thread: SQL Tuning

  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: SQL Tuning

    UDB V8.2 OS . AIX


    select
    rh.dun_rules_syntadun
    ,rh.row_id
    ,case when ou.name = 'Global Account' then 1 else 0 end as ACCT_PRIORITY
    from
    paras.s_org_edunt ou
    left join paras.malas_org ro on ro.dun_accnt_id = ou.row_id
    left join paras.malas_header rh on rh.row_id = ro.dun_rules_hdr_id
    where
    current timestamp >= rh.dun_effect_date
    and (current timestamp <= rh.dun_edunpira_date or rh.dun_edunpira_date is null)
    and upper(rh.dun_val_status) = 'Y'
    and (ou.name = 'Promise Checks' or ou.name = 'Global Account')
    and (rh.dun_bus_unit ='HBS' or rh.dun_bus_unit = 'Both')
    and rh.dun_event_cd in (3)
    and rh.dun_order_type in ('All','Both','New')
    and (rh.dun_all_ord_meth = 'Y' or rh.dun_methods_syntadun like '%Phone%')
    order by
    ACCT_PRIORITY, ro.dun_seq_number


    Can some on suggest me the Where i can create indexes
    and do i have to suggest some other options for the predicates in and LIke.

    I have read it some where that they will cause bad access plan.

    thanks

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    index

    use db2advis with input file, containing the sql
    db2 will recommend index if needed
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    consider using

    and rh.dun_val_status in ('y' , 'Y' )

    instead of

    and upper(rh.dun_val_status) = 'Y'


    as a scalar function on a column ist slower than an IN () in most cases

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    As a general rule, try to avoid "OR" constructs.
    Specifically, replace "ou.name = 'Promise Checks' or ou.name = 'Global Account'" by "ou.name IN ('Promise Checks','Global Account')"
    and "rh.dun_bus_unit ='HBS' or rh.dun_bus_unit = 'Both')" by "rh.dun_bus_unit IN ('HBS','Both')"

    B.T.W., does somebody know whether the v8 or v9 optimiser performs this conversion (from "f=v1 or f=v2" to "f IN (v1,v2)") by itself? AFAIK, v7 did not yet do this.
    Last edited by Peter.Vanroose; 09-03-06 at 04:00.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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