Home » Server Options » Text & interMedia » Context Search
Context Search [message #170017] Mon, 01 May 2006 13:49 Go to next message
runnyd
Messages: 4
Registered: May 2006
Junior Member
We are on oracle 10g. I have a table with 10M records. The table has a user_id column and a text (varchar2-4000) column. Each user has approx. 1000 records in the table. I’d like to add context search capabilities to the text column. I’d like to constrain the records first to the user_id column, and then do my context search. For example “Select * from my my_table where user_id = 100”. Then I would like to perform the context search on just those records that come back from the first result set. For example “select score(1), text from my_table where contains(text, ‘mice’, 1) > 0. Is there a way to mix the context indexes with regular indexes? Or is there a way to achieve my go goal through a function based index or some other code?

Since we have 10M records in the table, just running the context search index takes too long. In our application, a user would only be interested in searching their own records. That is why I am trying to reduce the subset to each user’s records before doing the context search. Am I trying to use the context indexing for something it was not designed to do?
Re: Context Search [message #170033 is a reply to message #170017] Mon, 01 May 2006 15:46 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Just combine them - the CBO will take care of the plan (probably a bitmap conversion to ROWIDs using both indexes:

select score(1), text 
  from my_table 
 where user_id = 100
   and contains(text, 'mice', 1) > 0;
Re: Context Search [message #170036 is a reply to message #170033] Mon, 01 May 2006 16:05 Go to previous messageGo to next message
runnyd
Messages: 4
Registered: May 2006
Junior Member
Sorry, I should have mentioned that we tried this query already.

select score(1), text
from my_table
where user_id = 100
and contains(text, 'mice', 1) > 0;

The optimizer was doing a complete table scan. Does anyone know if the above query should work? Maybe it is an issue with the optimzer (table is not analyzed?).
Re: Context Search [message #170214 is a reply to message #170036] Tue, 02 May 2006 11:47 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Are you sure it is doing a FTS? Personally, I have never seen the CBO do a FTS with a query involving CONTAINS.

Yes, the table/indexes should be analyzed via DBMS_STATS.

I can assure you that the query should work - I have hundreds of such queries. The most common plans would be a DOMAIN INDEX by itself, or a DOMAIN INDEX and RANGE SCAN (on user_id) combination.
Re: Context Search [message #170334 is a reply to message #170017] Wed, 03 May 2006 06:39 Go to previous message
runnyd
Messages: 4
Registered: May 2006
Junior Member
After analyzing the table, the query started to pick up the correct index. Thanks for your help!
Previous Topic: oracle markup drsxsopen error
Next Topic: SOUNDEX function in Oracle 10g
Goto Forum:
  


Current Time: Thu Mar 28 03:52:40 CDT 2024