Re: context intermedia newbie question

Answers:
1. yes it will take also a lot space
2. It is fast because the query is not searching in CLOBS. The architecture is like this:

Table:
col1, col2, resume, ...
A,B,'this is a big CLOB', ...

create index <index> ...

internal table:
select token_text from DR$<index>$I;

row1: this
row2: is
row3: a
row4: big
row5: CLOB

A contains searches now in this internal table and not in your original table.

3. A search '%token%' is bad, even for intermedia text. Because it is a full table scan on DR$<index>$I. It there is a way to search with 'token%' or only 'token' then you will get a fast result.

Tip:
1. use the latest patchset 8.1.7.4
2. when you create the index use a high memory preference (as much as possible)
3. have a look a technet.oracle.com -> products -> Oracle Text for Docu, Samples and Performamce FAQs.

Thomas

Ed Wong wrote:

> I am newbie to context intermedia and am looking into implement it for
> two search purpose we need on our database. First purpose is the
> ablity to search on a large CLOB(30GB) column. Second purpose is
> create an index on varchar2 so that we can use an index for wildcard
> search '%text%' on varchar2.
>
> My questions are:
> 1. If I implement an conext index on a 30GB CLOB column. Will the
> index take as much space?
> 2. What about performace on search such a big CLOB using context
> intermedia? Does it return result immediately for simple search such
> as "CONTAINS(Resume,'engineering')>0;"
> 3. Can I use context to create an index on varchar2 so that an index
> will be use for wildcard search '%text%'?
>
> Thanks,
>
> ewong
> Oracle 8.1.7EE Sun Solaris 2.7
>
Received on Wed Oct 23 2002 - 17:16:10 CDT