Using 2 catsearch predicates in the same where clause

(If I could I would double the points for someone who provides an answer to this problem???)

This is Oracle and Oracle9i Oracle Text on Windows 2000 SP4. CBO is on.

Help!!! I'm trying to use a query that uses 2 catsearch calls in the same where clause. When I try, I receive the DRG-10849 error.

select *
from tbl_auction
where  CATSEARCH(title, 'CAMERA', NULL) >0
       CATSEARCH(description, 'RIDER', NULL) >0

This results in error message:

ORA-10849: catsearch does not support functional invocation.
DRG-10599: column not indexed.

To explain, I will provide my "prototype" environment, the query and results. My production environment uses table partitionning and CBO so I setup my prototype the same way.

create table tbl_auction(
item_id number,
title varchar2(100),
description varchar2(100),
category_id number,
price number,
bid_close date)
partition by list (category_id) (
      partition motorcycle values (5),
      partition camera values (2),
      partition printer values (1))


('index set tbl_auction_desc_iset');


('index set tbl_auction_title_iset');

insert into tbl_auction values(1, 'Harley Davidson Sportster', '885cc James Dean Sportster. Authentic seat and original chrome', 5, 43000, sysdate);
insert into tbl_auction values(2, 'Harley Davidson Softtail', '1300cc road bike', 5, 13000, sysdate);
insert into tbl_auction values(3, 'Harley Davidson Fatboy', '1300cc black low rider - lots of chrome', 5, 22000, sysdate);
insert into tbl_auction values(4, 'Honda Shadow Classic', '750cc two tone burgandy and tan', 5, 11400, sysdate);
insert into tbl_auction values(5, 'Honda Vision', '500cc affordable beginner cycle', 5, 8500, sysdate);
insert into tbl_auction values(6, 'Cannon Digital Camera', 'Rebel 6.3Mpixel with removable lenses', 2, 800, sysdate);
insert into tbl_auction values(7, 'HP Printer Color', '1200 dbi color printer with individual cartridges', 1, 320, sysdate);
insert into tbl_auction values(8, 'Epson Photo Printer', 'Cutting edge printing of photos directly from your memory stick', 1, 129, sysdate);
insert into tbl_auction values(9, 'Optimax Digital Camera', 'Very affordable beginner camera with automatic everything', 2, 99, sysdate);
insert into tbl_auction values(9, 'Kodak SLR', 'James Dean replica', 2, 899, sysdate);

select *
from tbl_auction
where  CATSEARCH(title, 'CAMERA', NULL) >0
       CATSEARCH(description, 'RIDER', NULL) >0
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I can't emulate your setup 100% since I don't have access to partitioning right now but if memory searves me correctly, indexes are created by default when you set up partitioning.  If so, this is probably the issue.  w/o the partition pieces, I'm unable to reproduce the error on 10g.

I found to following on Metalink:

Just in case you don't have access, here's a possible work-adound that was posted:
select where catsearch
select where catsearch

Also, here's an excerpt explaining the problem:
*Cause: the query plan for your query is invoking a
catsearch operator functionally rather than as
an index rowsource. The catsearch operator does
not support functional invocation
*Action: add optimizer hints so that all catsearch
operators are performed through index scans

"functional invocation" is a row-by-row test. It's a method chosen by
the optimizer when it thinks it'll be cheaper to check the contents of each row rather than doing an index lookup. See 

In the case of the CTXCAT index type, there is simply no implementation of the functional lookup. Thus you will get this error whenever the optimizer decides to do one.

The reason we [Text] don't support functional invocation is because we want to ensure that the rows come out in order if an order by is specified in the third parameter, and we generally cannot do that when functional invocation.

In some cases, creating Context indices instead of catalog (Ctxcat) indices will solve the functional invocation problem.
danielvicAuthor Commented:
Slightwv, thanks for the post. I have access to Metalink and read that article. I'm afraid that the INTERSECT will not perform adequately on my production table of 200 Million+ rows.

I had also tested without partitions on the table and the query worked. That lead me to believe that it should also work with the partitions and I don't understand what's getting in the way with partitioning. Your explanation may point in the right direction regarding the default indexes when partitioning. I'd like someone to second your comment and I'd like someone to absolutely confirm that I can not use the multiple CATSEARCH query with the partitioned table.
slightwv (䄆 Netminder) Commented:
I think you will be able to use the query as it was written if you use the query HINT that was mentioned in Metalink.  As for waiting for a second confirmation here, it might be a long wait.  I haven't seen may other Experts on this site that know Oracle Text.  Then again, this site never ceases to amaze me.  In addition to waiting for a post here and for absolute confirmation, open an iTar (and wait several days......).

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

danielvicAuthor Commented:

It's been a while but I've been doing lots of experimenting to find the answer. I now have a much better understanding of the Oracle text so perhaps I can become the expert ;-)

The problem in this case is that using 2 catsearches in a select on a table that is partitioned does not seem to be supported. I created an identical unpartitioned table and was able to perform the query.

I also found out that if you want to combine your text search with another index (normal or bitmap) that you have to use the proper hint, as mentioned in various tech documents. But it is also noteworthy to mention that you have to use the proper hint based on the type of index combined with the ctxcat type (I did not do any work with the context type index).

I used INDEX_COMBINE (C) NO_EXPAND in my case on a search involving several bitmap indexes in an OR clause including the CATSEARCH and was able to make it work.

What I need to try next is the use of normal indexes combined with CATSEARCH using the AND_EQUAL hint. I suspect it will work, but I have not tested.

One more thing. The "functional invocation" error is pretty generic in that it can happen when an index is missing or the wrong hint is used or the CBO uses the wrong index in the combination. My query stopped working after my colleague added some indexes. I had to delete the extra indexes one at a time until I could make the query work again.

Ok, one last thing! I tried to use the Index Tuning Wizard and it does not fully support queries with text based index clauses, so beware...

Slightwv, thanks for taking the time to help.
slightwv (䄆 Netminder) Commented:
>> perhaps I can become the expert

That would be great!!!  Then you can help me.  I learned it the same way you are:  brute force and a lot of trial and error.

Glad everything is working.  I see you're learning quickly that domain indexes are a bit of a beast to work with.  Mainly due to the fact that the docs are vague at times and there aren't a lot of people using them (if they are, they aren't talking about it).
danielvicAuthor Commented:
It works, but the performance is nowhere near what I need to provide for online searching. I'm looking at other alternatives using partitions and expanding the table definition to eliminate the need for text indexing. Philysophically, text based indexing (Intermedia text specifically) seems to be geared more towards document/web page text indexing in the CONTEXT case and short "description" based column indexing for the CTXCAT index type.  I'm curious as to what type of indexing the Google's of the world use to index their massive text needs. I somehow doubt they are using out of the box text based products from the various database software providers.

....VERY interesting stuff to work on!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.