Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Talking Unanswered: LIKE prevent use of index?

    I have the following SQL statement and explain results. This query is taking an excessive amount of time.

    A couple questions:

    1) does the use of LIKE still allow the optimizer to use an index?

    2) the index it is using has policy_num, batch_date, batch_seq and seq_num. Sould I create a new index with just policy_num so this query will utilize it and perform faster?

    Thanks!
    Bob

    SELECT ITEM.BATCH_DATE, ITEM.BATCH_SEQ, ITEM.SEQ_NUM, ITEM.POLICY_NUM,
    ITEM.INSURED_NAME, ITEM.ITEM_TYPE_CODE, ITEM.EFFECTIVE_DATE,
    ITEM.TRAN_EFFECT_DATE, ITEM.ITEM_PREMIUM, ITEM.ITEM_RETURNED, ITEM.ENTRY_SOURCE,
    ITEM.NOTES_EXIST, ITEM.TAGS_EXIST, ITEM.REASONS_EXIST FROM ITEM ITEM WHERE
    POLICY_NUM LIKE ? ESCAPE '+' ORDER BY ITEM.POLICY_NUM,ITEM.INSURED_NAME ASC
    SQL4021 Access plan last saved on 09/22/09 at 15:20:19.
    SQL4020 Estimated query run time is 27 seconds.
    SQL4017 Host variables implemented as reusable ODP.
    SQL4002 Reusable ODP sort used.
    SQL4006 All indexes considered for table 1.
    SQL4008 Index I1A30310 used for table 1.
    SQL4011 Index scan-key row positioning used on table 1.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    use dynamic sql and supply the value at prep time
    do not use '%xxxx' because in that case it can not use the index either
    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
    Nov 2005
    Location
    IL
    Posts
    557
    If policy_num is already in the first position in the current index, there is no need to create a stand alone index on policy_num.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Aug 2009
    Posts
    3
    thank you both!

Posting Permissions

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