Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    10

    Unanswered: How to tuning 'OR' operator

    Hi All,

    I have a query but 'Where OR' consume high cost and not use index ,anyone have solution to change query


    Code:
    SELECT 				
    T1.C_ID				
    FROM T_CUST T1				
    LEFT OUTER JOIN T_NAME T2				
    ON T1.C_ID = T2.C_ID				
    WHERE (T2.NUMBER = '001') OR (T1.NAME_CD = '10')
    Thanks
    Last edited by automona; 01-08-12 at 12:21.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    select c_id				
    from t_name			
    where number = '001'
    union
    select c_id				
    from t_cust			
    where name_cd = '10'
    Last edited by Peter.Vanroose; 01-08-12 at 17:24.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This might help if you have separate indexes on T1.NAME_CD and T2.NUMBER. However, I suspect that the because of the left outer join, the top query may be a problem. Also, the use of an index for either query depends on the number of unique values of the columns in the index columns (if less than about 20 unique values, then the index may not be used).

    Code:
    SELECT 				
    T1.C_ID				
    FROM T_CUST T1				
    LEFT OUTER JOIN T_NAME T2				
    ON T1.C_ID = T2.C_ID				
    WHERE T2.NUMBER = '001'
    UNION
    SELECT 				
    T1.C_ID				
    FROM T_CUST T1				
    LEFT OUTER JOIN T_NAME T2				
    ON T1.C_ID = T2.C_ID				
    WHERE T1.NAME_CD = '10'
    [/QUOTE]
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    I guess because local predicate is the or operator of the two different table.
    db2 can not excute them first 。Maybe DB2 just use them as a fillter after join two tables...
    any way , without an exactly access plan and tables ddl, we can't not give you more advise about it .....

Posting Permissions

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