Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: Turn alpha-numeric range to IN list

    I am wanting to turn an alpha numeric range into an IN list. I have COBOL and SQL code that will do this with a range of numbers or dates, but would like to do the same for an alpha numeric situation. For instance:
    Code:
    and my_alpha_col >= 'X2A'
    and my_alpha_col <= 'X7A'
    Using this type function on numbers and dates has turned some slow queries into rather fast runners by getting the matching index scan on the col and subsequent cols instead of the index screening.

    Thanks for any help.

    Dave Nance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't see how changing that existing expression into an IN list would make the query more efficient... If anything, I would expect that turning an IN list into this expression would allow DB2 to use an appropriate index to improve the performance.

    Can you show an example of poorly performing code along with the better performing code? I'd love to understand what you're seeing!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would also assume that processing IN lists is slower than range predicates (or BETWEEN as is used here). DB2 may actually optimize an IN list without gaps into a range predicate.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    the reason for the better perrformance is allowing us to get more index matching columns with the use of the in list I can get matchcols = 4 rather than matching 1 as I do with the range predicate. Most queries hit this table with an equal predicate and maybe not have all/some of the others in the index, so this is the leading column.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Anyway, any suggestions on creating the range as a list?

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Tonkuma/Lenny? This is right up your guys' alley

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are there six, one hundred and twenty eight, or some other number of elements in your expected list?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Too little information for me to say something.
    For example:
    DB2 version/release/fixpack and platform OS(I guessed z/OS because of COBOL).
    No query example which you want to tune up.(other predicates?, other tables?)
    No table informaion.
    No index informaion.
    No numbers(rows in the table, filter factors(matching ratio), desired query time, actual query time, ...)
    Is your my_alpha_col char or varchar? And how long in table DDL?

    Though, I haven't tried to change range into IN list,
    I thought that to reconsider index(es) would be worth to try first.

  9. #9
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    populate temporary table and use it as In-List.
    Dick Brenholtz, Ami in Deutschland

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I still would like to see an example showing that you get different behavior. As I said, an optimization of DB2 could be to convert an IN list (of literals) into a range predicate. And doing two comparisons is bound to be faster than multiple probes into a list.

    So your claim sounds rather strange to me. How about you provide some details on the plans with the different numbers of matching columns?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Stolze:

    I understand that dav1mo wants to pursue their current line of thinking, I've had more than one occasion where I had to prove that my idea was worgn before I could see other ideas clearly, so I'm willing to let them pursue this one...

    Once we generate an IN list that suits dav1mo so that they can prove that the IN list isn't faster, maybe we can get more information about what they're really seeing.

    I'm still waiting for clarification on which of the potential IN lists they want to generate. I see multiple possibilities from the criteria that has been posted so far.

    dav1mo: Would a sub-select from your table using the criterial from your SQL posted above work, or do you actually need constant values? The constant values imply either dynamic SQL or at least a pre-pass to build the IN list to my mind.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Basically, I agree with you, Pat. However, a look at the access plans used for the query should also give some indication what's happening. That's where I would look first before spending a lot of time rewriting the query. :-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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