Walking a CLOB

Friends:

I'm in the unenviable position of having to retrieve rows based on a
deep-dive into a CLOB, searching for a particular constant.

I've tried different options of predicates and their placement, and
listed below--in the order of best cost to worst--are the ones I've
tried:

1. LIKE predicate, e.g., CLOB_FIELD LIKE '%characterstring%' in the
WHERE clause
2. LOCATE predicate, e.g., LOCATE('characterstring', CLOB_FIELD) > 0
in the WHERE clause
3. POSSTR predicate, e.g., POSSTR (CLOB_FIELD, 'characterstring') > 0
in the WHERE clause
4. Same as 1, but in a CASE statement in the SELECT clause, e.g. CASE
WHEN CLOB_FIELD LIKE...
5. Same as 2, but in a CASE statement in the SELECT clause, e.g. CASE
WHEN 0 < LOCATE...
6. Same as 3, but in a CASE statement in the SELECT clause, e.g., CASE
WHEN 0 < POSSTR...

Let me add that the table with the CLOB is also being joined-to, and,
as one might expect, it doesn't matter if I move predicates 1-3 from
the WHERE to the ON clause.

Finally, note that my platform is DB2 8.2 LUW on AIX 5.2, so I don't
have the relevant XML functions in 9.x available to me.

My question is this: focusing just on queries 1 and 4, despite what
the EXPLAIN tells me, the query with the LIKE in a CASE statement in
the SELECT clause is executing much faster than the one with the LIKE
in the WHERE. In other words, 4 costs more than 1, but performs
considerably better.

Why might this be? Is there an optimizer-level difference in the way
predicates are handled in the WHERE clause versus the SELECT clause?
Another wrinkle is that CLOBs are "handled" outside of the buffer
cache, so looking under the hood only shows so much. Feel free also to
let me know if there's another v8-compatible CLOB-crawling technique
I'm overlooking.

Thanks,

--Jeff

"jefftyzzer" <jefftyzzer DeleteThis @sbcglobal.net> wrote in message
news:4089c0da-f790-4944-b732-c224f2703e6d@e17g2000hsg.googlegroups.com...
> Friends:
>
> I'm in the unenviable position of having to retrieve rows based on a
> deep-dive into a CLOB, searching for a particular constant.
>
> I've tried different options of predicates and their placement, and
> listed below--in the order of best cost to worst--are the ones I've
> tried:
>
> 1. LIKE predicate, e.g., CLOB_FIELD LIKE '%characterstring%' in the
> WHERE clause
> 2. LOCATE predicate, e.g., LOCATE('characterstring', CLOB_FIELD) > 0
> in the WHERE clause
> 3. POSSTR predicate, e.g., POSSTR (CLOB_FIELD, 'characterstring') > 0
> in the WHERE clause
> 4. Same as 1, but in a CASE statement in the SELECT clause, e.g. CASE
> WHEN CLOB_FIELD LIKE...
> 5. Same as 2, but in a CASE statement in the SELECT clause, e.g. CASE
> WHEN 0 < LOCATE...
> 6. Same as 3, but in a CASE statement in the SELECT clause, e.g., CASE
> WHEN 0 < POSSTR...
>
> Let me add that the table with the CLOB is also being joined-to, and,
> as one might expect, it doesn't matter if I move predicates 1-3 from
> the WHERE to the ON clause.
>
> Finally, note that my platform is DB2 8.2 LUW on AIX 5.2, so I don't
> have the relevant XML functions in 9.x available to me.
>
> My question is this: focusing just on queries 1 and 4, despite what
> the EXPLAIN tells me, the query with the LIKE in a CASE statement in
> the SELECT clause is executing much faster than the one with the LIKE
> in the WHERE. In other words, 4 costs more than 1, but performs
> considerably better.
>
> Why might this be? Is there an optimizer-level difference in the way
> predicates are handled in the WHERE clause versus the SELECT clause?
> Another wrinkle is that CLOBs are "handled" outside of the buffer
> cache, so looking under the hood only shows so much. Feel free also to
> let me know if there's another v8-compatible CLOB-crawling technique
> I'm overlooking.
>
> Thanks,
>
> --Jeff

Anytime you use a function on a predicate, chances are very poor that DB2
can use b-tree index access on that predicate.

DB2 V8.2 has XML Extender, which does provide some additional functions that
can be used to search XML.

If you CLOB is less than 32K (or the max varchar length) then you could try
casting the CLOB to a varchar and then applying the functions against the
varchar to see if that gives you more flexibility (probably not any better
performance. If your CLOB is less than the max varchar, then you should make
it varchar to begin with so you will get much fast access through the
bufferpools (LOB's bypass the bufferpools and require synchronous I/O for
read and write).