+ Reply to Thread
Results 1 to 3 of 3

Pattern Matching using LIKE predicate on a DB2 column using INDEX

  1. Pattern Matching using LIKE predicate on a DB2 column using INDEX


    I have a column in db2 with an index. When I search using LIKE
    predicate,
    sometimes it gives results very slow (about 3 to 5 mins). But after
    the initial hit,
    subsequent searches using different expressions return very fast (2
    secs).

    It is almost as-if DB2 decides to cache the whole index when we run
    the
    query first time.

    After couple of hours later (or next day) again the initial query
    takes the hit.

    Any ideas ?


  2. Re: Pattern Matching using LIKE predicate on a DB2 column using INDEX

    wrote in message
    news:1194399689.880857.184960@o80g2000hse.googlegroups.com...
    >
    > I have a column in db2 with an index. When I search using LIKE
    > predicate,
    > sometimes it gives results very slow (about 3 to 5 mins). But after
    > the initial hit,
    > subsequent searches using different expressions return very fast (2
    > secs).
    >
    > It is almost as-if DB2 decides to cache the whole index when we run
    > the
    > query first time.
    >
    > After couple of hours later (or next day) again the initial query
    > takes the hit.
    >
    > Any ideas ?


    All data and index pages are cached in the bufferpool(s). If a needed data
    or index page is not already in the bufferpool, then DB2 puts it in the
    bufferpool, and then processes the query after that. After a while, other
    SQL statements will force old pages out of the bufferpool if the bufferpool
    is smaller than the sum of all the data and indexes. You should make the
    bufferpool(s) as large as possible so long as you have real memory (not
    virtual memory) available on the DB2 server.

    However, I don't think you can account for a difference of 3-5 minutes
    versus 2 seconds based on bufferpool caching.

    In order to DB2 to use a b-tree of an index (the fasted access path), you
    must only use this kind of expression in a LIKE statement:

    'ABC%'

    Notice that you are supplying the leftmost bytes (1-3) of the key, and the
    rest are wildcards.

    If you use '%ABC%' then DB2 cannot use the b-tree and must scan the entire
    index, or do a table scan. So if possible it best to limit the search string
    to always supplying the leftmost bytes if that meets your application
    requirements.



  3. Re: Pattern Matching using LIKE predicate on a DB2 column using INDEX

    On Nov 6, 11:36 pm, "Mark A" wrote:
    > wrote in message
    >
    > news:1194399689.880857.184960@o80g2000hse.googlegroups.com...
    >
    >
    >
    >
    >
    > > I have a column in db2 with an index. When I search using LIKE
    > > predicate,
    > > sometimes it gives results very slow (about 3 to 5 mins). But after
    > > the initial hit,
    > > subsequent searches using different expressions return very fast (2
    > > secs).

    >
    > > It is almost as-if DB2 decides to cache the whole index when we run
    > > the
    > > query first time.

    >
    > > After couple of hours later (or next day) again the initial query
    > > takes the hit.

    >
    > > Any ideas ?

    >
    > All data and index pages are cached in the bufferpool(s). If a needed data
    > or index page is not already in the bufferpool, then DB2 puts it in the
    > bufferpool, and then processes the query after that. After a while, other
    > SQL statements will force old pages out of the bufferpool if the bufferpool
    > is smaller than the sum of all the data and indexes. You should make the
    > bufferpool(s) as large as possible so long as you have real memory (not
    > virtual memory) available on the DB2 server.
    >
    > However, I don't think you can account for a difference of 3-5 minutes
    > versus 2 seconds based on bufferpool caching.
    >
    > In order to DB2 to use a b-tree of an index (the fasted access path), you
    > must only use this kind of expression in a LIKE statement:
    >
    > 'ABC%'
    >
    > Notice that you are supplying the leftmost bytes (1-3) of the key, and the
    > rest are wildcards.
    >
    > If you use '%ABC%' then DB2 cannot use the b-tree and must scan the entire
    > index, or do a table scan. So if possible it best to limit the search string
    > to always supplying the leftmost bytes if that meets your application
    > requirements.


    That is what I thought. I thought the search would depend on how much
    of
    leftmost and rightmost parts of the key I have specified. But my db2
    instance
    is behaving kind of odd. It happened today again. I first ran a query
    like the
    following which took 3 minutes -

    select indexkey from table where indexkey like '_____-
    ______'

    Now subsequent searches take 4 seconds.

    select indexkey from table where indexkey like '__441_____%'
    select indexkey from table where key indexkey '_______ABC%'
    select indexkey from table where indexkey like '%987%'

    Even if I give an expression that is not present, it comes back in 4
    seconds.

    OTOH, I have another table on which the key has not been indexed.
    Pattern
    matching queries on that table always yield results in predictable
    (takes minutes)
    amount of time (even equal-to comparison takes almost same number of
    minutes
    +/- some seconds).



+ Reply to Thread