Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    21

    Question Unanswered: SQL tuning question

    All the columns in the where clause are indexed.
    I was wondering if there was a more efficient way to write this and perform index only scans since the NOT IN predicate does a tablescan. Maybe a LEFT JOIN? Any input will be of great value.

    SELECT ' ' FIRST_NAME,
    ' ' LAST_NAME,
    A1.AGE,
    A1.BUR,
    A1.ACCT,
    A1.ACCTRS,
    A1.ACCTTL,
    ' ' EXAMID,
    A1.OMBBRN,
    A1.ACCTSRC
    FROM SCHEMA_1.TABLE_1 A1
    WHERE
    ACCTSRC IN ('APPENDIX', 'TREASURY')
    AND ACCTAI = 'A'
    AND EXAMID
    NOT IN

    (SELECT NET_ID
    FROM SCHEMA_2.TABLE_2
    WHERE ALT_AGENCY = '854' AND NET_ID BETWEEN 'A%' AND 'ZZ%')

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Change:
    Code:
    AND EXAMID 
    NOT IN
    
    (SELECT NET_ID
    FROM SCHEMA_2.TABLE_2
    WHERE ALT_AGENCY = '854' AND NET_ID BETWEEN 'A%' AND 'ZZ%')
    To:
    Code:
    AND NOT exists
    
    (SELECT 1 FROM SCHEMA_2.TABLE_2
    WHERE ALT_AGENCY = '854' AND NET_ID BETWEEN 'A%' AND 'ZZ%'
    and a1. EXAMID = net_id)
    Lastly, you say the columns are in your index, but you do not say which order. Hopefully, on table1 the leading columns are ACCTSRC and ACCTAI and EXAMID is trailing. To go along with that the more unique those first 2 are, the faster this will perform.

    Dave

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Additional to using NOT EXISTS and an index suggeted by Dave,
    an index ( NET_ID , ALT_AGENCY ) or ( ALT_AGENCY , NET_ID ) on SCHEMA_2.TABLE_2 shuld be tried.

  4. #4
    Join Date
    Oct 2012
    Posts
    21
    All, thanks. I'm seeing a significant improvement in throughput.

  5. #5
    Join Date
    Oct 2012
    Posts
    21
    Quote Originally Posted by tonkuma View Post
    Additional to using NOT EXISTS and an index suggeted by Dave,
    an index ( NET_ID , ALT_AGENCY ) or ( ALT_AGENCY , NET_ID ) on SCHEMA_2.TABLE_2 shuld be tried.
    In regards to indexes and columns. I have 2 indexes on these tables
    One on "NET_ID" and a combination index on "ALT_AGENCY and <another_column>" and all allow reverse scans. My question here is does the definition of the index affect performance? eg Say if you are querying a table where
    NET_ID = X
    AND ALT_AGENCY = Y
    does db2 smart enogh to use both defined indexes? Or does the index structure matter

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    In some cases DB2 can use multi-index access. Though, since you have an index with net_ID as the only column, if you add alt_agency to it, it will most likely improve your performance.

Posting Permissions

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