?
Solved

Using 2 catsearch predicates in the same where clause

Posted on 2005-04-26
6
Medium Priority
?
1,900 Views
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 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
0
Comment
Question by:danielvic
  • 3
  • 3
6 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 13870402
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:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=501030.996

Just in case you don't have access, here's a possible work-adound that was posted:
select where catsearch
INTERSECT
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
http://otn.oracle.com/products/text/x/FAQs/imt_Perf_Faq.html#q07 

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

Author Comment

by:danielvic
ID: 13870556
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.
0
 
LVL 78

Expert Comment

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


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:danielvic
ID: 13982869
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.
0
 
LVL 78

Expert Comment

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

Author Comment

by:danielvic
ID: 13997323
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

850 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