Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Tune dates from DATE to INT type

    I am having a hard time automating a DB2 SQL report without the execution time jumping from 1min to 30 min.

    My table has a fields named date and dateint. dateint has an index and runs very fast when using it. however when I trying to automate it my query runs for over 30min. does anyone have any advice?

    <this runs in 1 min>
    select .... from ...
    where dateint between 20121005 and 20121012

    <this runs over 30 min>
    select .... from ...
    where dateint between int(current_date - 7 days) and int(current_date)

    has anyone encountered this problem? how do I work around it?
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is there any difference in the access plan between the 2 queries?

    Andy

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    there have been many discussions about this:
    the difference in access path taken by the optimizer when
    a literal is used in the WHERE clause
    Code:
    between 20121005 and 20121012
    and a 'run-time calculated' variable.
    Code:
    between int(current_date - 7 days) and int(current_date)
    you are going to get a better/faster/fastest path with literal
    because if db2 has any statistics to work with
    db2 know exactly where it is going and how to get there.

    there may have also been some hints/suggestions on how to fool the
    optimizer,
    but i don't remember or just ignored them.
    Dick Brenholtz, Ami in Deutschland

  4. #4
    Join Date
    Aug 2011
    Posts
    34
    I found a solution... thanks guys for the help.

    I used the dateint to get the bulk of the transactions and then used this to get the current week.

    floor((days(date(purchasedate)) - days(' <start of date range> '))/7)+1
    - Using Oracle 11g with Toad v12

Posting Permissions

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