Re: How to use oracle text to search multiple columns across multiple tables join

You might want to research the following:

Subject: 	CDSTORE - Concatenated Datastore utility for interMedia Text
Doc ID: 	Note:122255.1

Introduction

The Concatenated Datastore is an additional datastore for interMedia Text. It provides for extremely fast searching over multiple columns.

It does this by building a user datastore for you, hiding the complexity of creating a PL/SQL procedure to concatenate the data, and creates the appropriate section groups.

Numeric columns are encoded in such a way that you can do range searches on them. Operators supported are greater than, less than and between.

An update trigger is automatically added so that the concatenated datastore index is automatically updated when any column changes.

I'm not sure if it will work across multiple tables or whether you can create a (materialized) view and put the text index on that?

ningjun.wang_at_lexisnexis.com wrote:
> How can I use ORACLE TEXT to perform full text search on multiple
> columns across multiple tables join? For example consdier the following
> 3 tables
>
> Table Book {
> bookid number(8),
> title varchar2(2000),
> description varchar2(4000)
> }
>
> Table Author {
> authorid number(8),
> authorDescription varchar2(4000),
> age number(3)
> }
>
> Table AuthorToBook {
> bookid number(8),
> authorid number(8)
> }
>
> I will join the above 3 tables and then perform search with the
> following constraint
>
> 1. The word "network" must appear in Author.authorDescription and
> (Book.description or Book.Title)
> 2. author's age must be > 20
>
> One way to achieve this is create a CTXSYS.CONTEXT type index for each
> of the varchar2 columns in the 3 table and then use the following query
>
>
> select Book.*, Author.* from Book, AuthorToBook, Author
> where Book.bookid = AuthorToBook.bookid and AuthorToBook.authorid =
> Author.id
> and Author.age > 20
> and contains(Author.authorDescription, 'network', 1) > 0
> and (contains(Book.title, 'network', 1) > 0 or
> contains(Book.description, 'network', 1) > 0)
>
> This query is very inefficient. Is there anyway to create one index
> across all the varchar2 columns to achive the same purpose?
>
> Please give code snippet if you can.
> Thanks
>
Received on Sun May 07 2006 - 19:10:52 CDT