Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    27

    Unanswered: DB2 not using index on timestamp column

    Hi all,

    I am running this query:

    WITH Q1 AS (
    SELECT
    a.REP,
    b.LETTER_NAME,
    CASE COALESCE(RTRIM(a.ADDRESS1),'') WHEN '' THEN 0 ELSE 1 END +
    CASE COALESCE(RTRIM(a.ADDRESS2),'') WHEN '' THEN 0 ELSE 1 END +
    CASE COALESCE(RTRIM(a.ADDRESS3),'') WHEN '' THEN 0 ELSE 1 END +
    CASE COALESCE(RTRIM(a.ADDRESS4),'') WHEN '' THEN 0 ELSE 1 END +
    CASE COALESCE(RTRIM(a.ADDRESS5),'') WHEN '' THEN 0 ELSE 1 END AS CNT
    FROM
    CORTRAK.MASTER a,
    LETTERS.TEMPLATE b
    WHERE
    a.LETTER_NAME = b.LETTER_NAME AND
    b.LETTER_CATEGORY = 'HTML_Email' AND
    DATE(a.DATE_TIME) BETWEEN '2013-04-01' AND '2013-06-11'
    )
    SELECT
    LETTER_NAME,
    SUM(CNT) AS SENT
    FROM
    Q1
    GROUP BY
    LETTER_NAME
    UNION
    SELECT
    LETTER_NAME, 0 AS SENT
    FROM
    LETTERS.TEMPLATE
    WHERE
    LETTER_CATEGORY = 'HTML_Email' AND
    LETTER_NAME NOT IN (SELECT LETTER_NAME FROM Q1)
    ;

    CORTRAK.MASTER has an index defined on DATE_TIME which is a timestamp column. But the access plan shows a full table scan rather than an index scan and the query takes over 5 min to run. Can you please suggest any modifications to this query to improve performance? Any help is greatly appreciated.

    Thanks,
    DB

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are getting the tablescan because of the DATE function.

    Try this:

    a.DATE_TIME BETWEEN timestamp('2013-04-01','00:00:00') AND timestamp('2013-06-11','24:00:00')

    Andy

  3. #3
    Join Date
    Sep 2007
    Posts
    27
    Hey ARWinner,

    Thanks a lot for pointing that out. Now I am doing index scan.

    -DB

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Example 1:
    (1) ARWinner's suggestion.
    (2) Replace "CASE COALESCE(RTRIM(a.ADDRESSn),'') WHEN '' THEN 0 ELSE 1 END"
    with "CASE WHEN a.ADDRESSn <> '' THEN 1 ELSE 0 END".
    where n is 1 to 5.
    (3) Use LEFT OUTER JOIN.
    Code:
    SELECT b.LETTER_NAME
         , SUM(
              CASE WHEN a.ADDRESS1 <> '' THEN 1 ELSE 0 END
            + CASE WHEN a.ADDRESS2 <> '' THEN 1 ELSE 0 END
            + CASE WHEN a.ADDRESS3 <> '' THEN 1 ELSE 0 END
            + CASE WHEN a.ADDRESS4 <> '' THEN 1 ELSE 0 END
            + CASE WHEN a.ADDRESS5 <> '' THEN 1 ELSE 0 END
           ) AS SENT
     FROM  LETTERS.TEMPLATE b
     LEFT  OUTER JOIN
           CORTRAK.MASTER   a
      ON   a.LETTER_NAME = b.LETTER_NAME
       AND a.DATE_TIME
             BETWEEN timestamp('2013-04-01','00:00:00')
                 AND timestamp('2013-06-11','24:00:00')
     WHERE b.LETTER_CATEGORY = 'HTML_Email'
     GROUP BY
           b.LETTER_NAME
     ORDER BY
           LETTER_NAME
    ;
    Last edited by tonkuma; 06-13-13 at 21:29.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    How about this?

    (1) ARWinner's suggestion.
    (2) Replace "CASE COALESCE(RTRIM(a.ADDRESSn),'') WHEN '' THEN 0 ELSE 1 END"
    with "CASE WHEN a.ADDRESSn <> '' THEN 1 ELSE 0 END".
    where n is 1 to 5.
    (3) Use LEFT OUTER JOIN.
    I could be wrong about this, or DB2 may have changed recently for Oracle compatibility, but my recollection is that the <> '' (two single tick marks) is not a test for NOT NULL in DB2, rather a test for blanks.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 2:
    Modified Example 1, like...
    (2a) Replace "SUM( CASE ... END + CASE ... END + ... )"
    with "COUNT( ... ) + COUNT( ... ) + ... "
    Code:
    SELECT b.LETTER_NAME
         , COUNT( NULLIF(a.ADDRESS1 , '') )
         + COUNT( NULLIF(a.ADDRESS2 , '') )
         + COUNT( NULLIF(a.ADDRESS3 , '') )
         + COUNT( NULLIF(a.ADDRESS4 , '') )
         + COUNT( NULLIF(a.ADDRESS5 , '') )
           AS SENT
     FROM  LETTERS.TEMPLATE b
    ...
    ...
    ...
    ;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... the <> '' (two single tick marks) is not a test for NOT NULL in DB2, rather a test for blanks.
    If a.ADDRESSn was NULL
    then " a.ADDRESSn <> '' " would be UNKNOWN and NOT satisfy " WHEN a.ADDRESSn <> '' ".
    (To satisfy WHEN clause, the condition should be TRUE.)
    Conseuence would go to ELSE clause.
    Last edited by tonkuma; 06-13-13 at 21:52.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 2:
    ...
    ...
    Code:
    SELECT ...
         , COUNT( NULLIF(a.ADDRESS1 , '') )
         + COUNT( NULLIF(a.ADDRESS2 , '') )
         + COUNT( NULLIF(a.ADDRESS3 , '') )
         + COUNT( NULLIF(a.ADDRESS4 , '') )
         + COUNT( NULLIF(a.ADDRESS5 , '') )
           AS SENT
    ...
    COUNT( expression ) counts non-NULL values of the expression.
    So, if a.ADDRESSn was NULL or a.ADDRESSn = '',
    then COUNT( NULLIF(a.ADDRESSn , '') ) doesn't count it.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For better performance,
    INDEX (LETTER_NAME , DATE_TIME) on CORTRAK.MASTER
    and/or
    INDEX (LETTER_CATEGORY , LETTER_NAME) on LETTERS.TEMPLATE
    might be usefull.

Posting Permissions

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