Using 2 catsearch predicates in the same where clause

Posted on 2005-04-26
Last Modified: 2008-01-09
(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
Question by:danielvic
    LVL 76

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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......).


    Author Comment


    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.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >> 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).

    Author Comment

    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!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle Query 13 58
    Case Statement in SQL Delminted Query 9 48
    Stored proc to create user table via file import 9 39
    oracle query 15 56
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now