RE: oracle text

Hi,
You are asking some questions I cannot answer, as it has been a while since I worked with Oracle text. I think that you will be better going with the concatenated datastore, I did this with the older InterMedia Text release and it worked very well.

When it comes to the access order, you may well know better than the optimizer in which order to do things, but Oracle will usually do the text index search first and then filter the results from that using other predicates. This is because it see's doing the other way as less efficient, what is referred to as a functional lookup, for each row source being returned fire up the Oracle text code and see if the row should stay in the result set. But like I said you may know better, so gather full statistics (since 9i the text index stats are gathered on the fly by Oracle) and then if need be use hints.

Regards

Pete

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Veres Lajos Sent: 07 September 2005 14:44
To: oracle-l_at_freelists.org
Subject: oracle text

Hello,

I have a table with 2 text columns (varchar2(80),varchar2(4000)) and about 10 numeric colums (and some unreleated to this post). version 10.1.0.4, linux 2.4

Our users can search with the most of combinations of text and numeric rows.

I checked Oracle Text, and I have some questions.

1.
There is 2 text columns, sometimes I search only from the first. (title and body are this columns), and sometimes from all the two. I should set up 2 index? Or I can use multicolumn or other datastore to sometimes search from the first field and sometimes from all the two? Which will be faster?
Or exists any best practice?
Maybe section grouping or similar can help?

2.
The second column (body) can contain html, but not really sure that this html is correct. (users can fill this fields) And I wont search for the html entries.
Can I set up filtering to filter out HTML entries? As I seen INSO filter skips filtering html.

3. Context index vs ctxcat
Manual says i should create a ctxcat index with all available combinations of queried fields.
But there is too much.
Our search form allows our users to create all combinations of queries for this 2+10 fields.

4. filtering, searching
Most queries should be faster if "contains" operator filters only the result.
We have queries like:
where contains(field,'searchstring')>0 and id1 IN (1,2,3) and id2 in (3,4) and id3=5

I think the query should be faster if at first oracle collects data with indexes from the table, and later filters only for searchstring. id{1-10} fields are mostly indexed.
(top used, and some top used combinations at least)

Optimizer can guess similar things? Or It will start gather data everytime with contains and filter later?

Today I found this:
http://www.oracle.com/technology/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html This concatenated datastore seems to be an other solution to the same problem. Have you any experience with this?

Any help would be appreciated.

Thanks.

--

Veres Lajos
vlajos_at_ludens.elte.hu
+36 20 438 5909
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 08 2005 - 06:56:01 CDT