| Context Search [message #170017] |
Mon, 01 May 2006 13:49  |
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  |
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 |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
