Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!nx02.iad01.newshosting.com!newshosting.com!216.196.98.140.MISMATCH!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!y43g2000cwc.googlegroups.com!not-for-mail
From: ningjun.wang@lexisnexis.com
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools
Subject: How to use oracle text to search multiple columns across multiple tables join
Date: 5 May 2006 09:44:21 -0700
Organization: http://groups.google.com
Lines: 46
Message-ID: <1146847461.002018.271920@y43g2000cwc.googlegroups.com>
NNTP-Posting-Host: 198.185.18.207
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1146847469 30674 127.0.0.1 (5 May 2006 16:44:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 5 May 2006 16:44:29 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 d112s-fp-eic-b (NetCache NetApp/6.0.2P1)
Complaints-To: groups-abuse@google.com
Injection-Info: y43g2000cwc.googlegroups.com; posting-host=198.185.18.207;
   posting-account=piT8DAwAAABt39rv1G1uxq67gBupV-lI
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:266966 comp.databases.oracle.misc:127152 comp.databases.oracle.tools:70755

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