Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: Use of IN criteria -Performance DB2 SQL's

    Hi All,
    I am using the following sql in our production and the DBA is saying it is doing tablescans instead of indexscan though it has index on where criteria because of IN criteria has 60(atmost) values. Unfortunatly I can't avoid this sql, any suggestions to improve the performance.

    SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
    FROM SAMPLETABLE WHERE ACCOUNTNUMBER IN('1','2','3',...'60');

    I searched on the web, I don't find any links saying that use of IN clause causes tablescans, any thoughts?

    Thanks
    Shri
    p.srinivasarao

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the only tool that can show the access path is explain
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by palchuri
    SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
    FROM SAMPLETABLE WHERE ACCOUNTNUMBER IN('1','2','3',...'60')
    The point is that the optimizer sees 60 individual values, while actually they are consecutive (logically speaking). Apparently, one table scan is cheaper than 60 individual index scans (thinks the optimizer, based on what he sees in the statistics).
    Assuming your "accountnumber" column is indeed textual, and its length is 1 for numbers up to 9, 2 for numbers up to 99, etc., there's not much you can do to improve this since the index on accountnumber will have those values sorted as
    1 10 100 101 ... 109 11 110 111 112 ... 12 121 122 ... 13 ... 14 .... 199 2 20 200 ....
    which would give a completely incorrect result when using "accountnumber between '1' and '60'".
    One option *might* be to make your column numeric (int).
    Second option: creating an index "on expression" which is actually one on "lpad(accountnumber, 10)", then using the condition
    Code:
    where lpad(accountnumber, 10) between lpad('1',10) and lpad('60',10)
    Third option: let your DBA generate column statistics for individual values (using RUNSTATS) so that the optimizer finds out that it's cheaper to do 60 matching index scans than a single table scan.
    Fourth option (not really a useful one, but still, if that would force the optimizer into the optimal access path...)
    Code:
    SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
    FROM SAMPLETABLE WHERE ACCOUNTNUMBER = '1'
    UNION ALL
    SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
    FROM SAMPLETABLE WHERE ACCOUNTNUMBER = '2'
    UNION ALL
    SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
    FROM SAMPLETABLE WHERE ACCOUNTNUMBER = '3'
    UNION ALL
    ...
    UNION ALL
    SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
    FROM SAMPLETABLE WHERE ACCOUNTNUMBER = '60'
    --_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
  •