Link to home
Start Free TrialLog in
Avatar of danielvic
danielvic

asked on

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 9.2.0.6.0 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
       AND
       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))

Begin
ctx_ddl.create_index_set('tbl_auction_desc_iset');
end;

CREATE INDEX tbl_auction_descx ON TBL_AUCTION(description) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS
('index set tbl_auction_desc_iset');

Begin
ctx_ddl.create_index_set('tbl_auction_title_iset');
end;

CREATE INDEX tbl_auction_titlex ON TBL_AUCTION(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS
('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
       and
       CATSEARCH(description, 'RIDER', NULL) >0
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of danielvic
danielvic

ASKER

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.
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......).


Hi,

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.
>> 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).
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!

Regards,
D.