Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: Better index for a query in db2 9.7

    Hola colleagues,
    I am using DB2 9.7 LUW, Operative Sisem AIX 6.1,

    I have a table with 3 million records and have the following query.
    Select * from schema.tabla where field1 = 'ABCD' and field2 'WXYZ' or field3 = 'HIJK'

    Can someone say to me or recommend myself that indexes are more optimal for this querys?
    An index composed by the fields field1 and field2 or for the fields field1, field2 and field3?

    Thank you very much in advance
    Regards.
    DBA DB2 for LUW

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You have a logic error with your OR condition.

    Should the query really be:
    Code:
    Select * from schema.tabla 
    where (field1 = 'ABCD' 
       and  field2 'WXYZ')
          or field3 = 'HIJK'
    If so, then you need 2 indexes and rewrite the query a bit. Index one on field1 and field2, Index two on field3. The query should be rewritten as:
    Code:
    Select * from schema.tabla 
    where field1 = 'ABCD'
       and field2 'WXYZ' 
            union 
    Select * from schema.tabla 
    where field3 = 'HIJK'
    If your original query should be correctly written as:
    Code:
    Select * from schema.tabla 
    where field1 = 'ABCD' 
       and (field2 'WXYZ'
          or field3 = 'HIJK')
    Then you need one index on field1, and you can include fields 2 and 3 for index only access if you wish.
    Dave

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Thank you Dav1mo,
    You can say me what is the systax for create the index than you recommended for my query original?

    Greetings.
    DBA DB2 for LUW

  4. #4
    Join Date
    Apr 2012
    Posts
    156
    google :-)

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    My systax for creating an index would be $75.00 US
    Dave

  6. #6
    Join Date
    Apr 2012
    Posts
    156
    Wow your cheap :-)

Posting Permissions

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