Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    69

    Angry Unanswered: DB2 V8 on OS390- Search Optimization

    Hi,
    We are in the process of creating a new data model for the Gift Registry which my company is planning to have a release in next year.
    Would like to get some inputs / thoughts on bellow.

    1. In our current data model we keep all the names a separate table where we do the search. The user has to enter Two char for the first name and two char for the last name. The select query which we use is wild char searching ( we use "Like" to ensure we retrieve all the matching criteria). We have created two more fields called first name search code & last name code which are char type and are indexed for optimization. Going forward Does this look good?. Any other optimization tech. I can use. I heared about the db2 v8 wild char indexing for a varchar. Does that really helpful?.

    Thanks for your inputs!.

    Anto.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you asking the user to input the first two characters on the name, or any two characters of the name? If it is the first two characters, then you should not have any performance problems with LIKE :name (when :name = 'XX%').

    Personally I would make them enter in the first 3 characters of the last name, with first name optional. The other thing that could be done is a Soundex search.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2003
    Posts
    69
    Yes, we ask only the first two char of the User's "First Name" and the last two char's of the users "Last Name". As most of the gift registries today we have built it the same way ( my business user's View!!). Like I said before we have created two more fields for the indexing purpose ( first name search code / last name search code), which some how helps with performance. Does any other optimization tech which would be better than this?

    What is soundex search?

    thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I was thinking you would just use the last_name and first_name columns and index those instead creating new columns with just 2 characters. However, if you create a composite index with the 2 character last_name and 2 character first (one index) then it should be the clustering index.

    Soundex allows one to do a fuzzy search on a name, so exact spelling is not required. Do a google search.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2003
    Posts
    69
    Thanks Marcus. I think I can remove the 2 char field.
    I was reading some article about a wild char indexing in db2 v8.1 which is helpful in this scenario of searching ( Searching with "Like %'FRST_NAME'%). Any pracitical exposure?
    Also I am thinking of partitioning that table also.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    'NAME%' will run much faster than '%NAME%".
    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
    Mar 2003
    Posts
    69
    thanks Marcus.
    But my question is , assume that you have a table which has first name, last name and type_code which has 10 million rows. The user may enter only two char's for the first name text and 2 char's for the last name char.
    The select query looks like this,

    select first_name,last_name from table_name where first_name like 'first name%' and last_name like 'last name%'

    These two fields are indexed. Is it good to create a covering index or normal index for this?.

    Does indexing the type_code and adding that into the where clause will help my query optimized?. So Does the above query is fast or,

    select first_name,last_name from table_name where first_name like 'first name%' and last_name like 'last name%' and type_code = 'IN'

    is fast?.

    What extra I can do to make this search to the maximum optimized.

    Thank you very much. I really need something broder which I can include.

    Thanks!.

Posting Permissions

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