Re: Differences in CLOB search returns

I'm no expert but ...

1. maybe it's your search criteria. Intermedia doesn't bother indexing "the" and lots of other simple words. So if you're searching for these using Intermedia, you won't find them. BTW 'Verity' behaves the same way, this is by design.
2. DBMS_LOB.INSTR is case sensitive.

   SQL> select dbms_lob.instr(a,'this') from fred;    

   DBMS_LOB.INSTR(A,'THIS')

                          0

   SQL> select dbms_lob.instr(a,'This') from fred;    

   DBMS_LOB.INSTR(A,'THIS')

                          1

So you might want to try to get them all, case insensitive using DBMS_LOB.INSTR    SQL> select dbms_lob.instr(upper(a),'THIS') from fred;    

   DBMS_LOB.INSTR(UPPER(A),'THIS')

                                 1

Just one thing though, %searchcriteria% search does a fully index table scan (just like LIKE %searchcriteria%). You might want to question whether you should index your CLOB with Intermedia/Context at all if this is the type of query you are regularly performing. Performance will be bad.

Regards
Barry

Petri33_at_hotmail.com (Petri) wrote in message news:<b4803f2d.0308221041.561b4567_at_posting.google.com>...
> I have noticed that if you use dbms_lob.instr() (ex: select * from
> table where dbms_lob.instr(CLOB_COL, 'search criteria')>0) you return
> more hits then if you use CONTAINS() with the Intermedia/Context
> Cartridge (ex: select * from table where CONTAINS(CLOB_COL, '%search
> criteria%')>0).
>
> I would think that the second statement would return more hits. Does
> anyone know why it doesn't?
>
> Thanks.
Received on Tue Aug 26 2003 - 01:45:09 CDT