Forum Stats

  • 3,782,324 Users
  • 2,254,637 Discussions
  • 7,880,045 Comments

Discussions

CATSEARCH only works with AND not with Or

438019
438019 Member Posts: 29
edited Jul 4, 2008 8:43AM in Text
Hello,

If I run the following query against my database it runs absolutely fine
SELECT * FROM
LOAD
WHERE ((catsearch (WILDCARD_SURNAMES, 'BRUCE', '') > 0)) AND PARSED_FORENAME1='ANTHONY'

But if I replace the "AND" with an "OR"
SELECT * FROM
LOAD
WHERE ((catsearch (WILDCARD_SURNAMES, 'BRUCE', '') > 0)) OR PARSED_FORENAME1='ANTHONY'
I get an Oracle error
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
DRG-10599: column is not indexed

I don't understand this, why can't I use OR?
Thanks for any help
Mark

Comments

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    The optimizer has chosen to drive the query from an index on parsed_forename1 or by doing a full table scan.

    This causes it to try to invoke the ctxcat index in "functional lookup" mode - effectively saying "does rowid XXX match have the word "BRUCE" in WILDCARD_SURNAMES?"

    However, CTXCAT indexes do not support functional lookups, so this is impossible.

    The solution is to use a hint which persuades the optimizer to always drive the query from the CTXCAT index.

    I know this is kind of messy, but our developers tell me there is no simple way to tell the optimizer that such as call is impossible (or infinitely expensive).
  • 438019
    438019 Member Posts: 29
    Thanks very much for your reply.

    I've had a look on the net to try to find the syntax for the hints but I can't find it sorry.

    What's the syntax for the hint to tell optimiser to use the CTXCAT index? (Sorry for any thickness, I'm not a DBA just a developer stumbling my way through it :)

    Thanks again!
    Mark
  • 12324-Oracle
    12324-Oracle Member Posts: 129
    Hi Mark,

    This is how you can do it

    select /*+ index(t2 t2x) */ * from t2 where catsearch(text,'row2','')>0;

    t2 - name of the table
    t2x is the index
    text - is the column on which index has been created
  • 438019
    438019 Member Posts: 29
    Thanks lots for your help.

    All the best
    Mark
This discussion has been closed.