Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Index related help needed

    Hello Gurus
    My requirement is

    to quickly select records based on either the ID column or the LAST_NAME or both columns together from my table say 'T'

    For this
    I want to create index on these two columns

    Should I be creating


    1 index specifying both columns, or

    2 indexes (one on each), or

    3 indexes (one for ID + one for last name + both)

    Can you please advice ??

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It depends. Is ID unique? Will last_name and ID always match? (whenever ID = X the last_name will always be Y)

    Andy

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Yes ID will be unique

    But I would also be interested to know how the answer depends if

    For One ID we have One Last Name


    from


    For one ID we have two to three last names ( max 5 )


    Please advice
    Last edited by dbaindi; 10-13-11 at 13:45. Reason: typo

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    For your requirements, you need 2 indexes. One on ID and one on Last_Name.

    If an ID had more than one last_name (It is no longer unique) and a last_name could have more than one ID, you would still need only 2 indexes and one would still be on ID only, but the second one would be on Last_name,ID.

    Andy

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    Thanks Andy

    I have around 4 million records in a table with around 25 rows

    and yes I have multiple last names for an ID .
    I think you recommended that

    "If an ID had more than one last_name (It is no longer unique) and a last_name could have more than one ID, you would still need only 2 indexes and one would still be on ID only, but the second one would be on Last_name,ID."


    In this I am thinking the sequence for second index matters and you mentioned it has to be last_name and then ID .

    Is that right ?

    Secondly , i also wanted to educate myself by knowing

    What would be the over head of having a third index on only ID ?

    Also does the create index sequence matters
    Which means
    1 Create index on ID , LAST NAME
    2 Create index on last name
    3 create index on id

    will this be different than
    1 create index on id
    2 Create index on last name
    3 Create index on ID , LAST NAME

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dbaindi View Post
    Also does the create index sequence matters
    Which means
    1 Create index on ID , LAST NAME
    2 Create index on last name
    3 create index on id
    I don't understand your table design (especially the part about one ID having multiple last names), but in DB2 you don't need Index 3 since it is redundant (DB2 can use Index 1 even if only ID is known).

    Note that if only LAST NAME is known, Index 2 is still needed and is not redundant, since your predicate needs to match the first column of the index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The order that indexes are created in do not matter. Having one index that is a subset of another is a redundant index and should be eliminated as it is not necessary. Having an index on ID and having an index on ID,Last_Name, the first index is redundant. What the indexes need to have depends on how you will access the data. If you will only use equivalent checks then you only need these indexes-- 1) ID, 2) Last_name, ID. If you use range checks then it depends on what columns are ranged.



    Andy

Posting Permissions

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