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

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 Fri May 05 2006 - 11:44:21 CDT