Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How to use oracle text to search multiple columns across multiple tables join

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

From: <ningjun.wang_at_lexisnexis.com>
Date: 5 May 2006 09:44:21 -0700
Message-ID: <1146847461.002018.271920@y43g2000cwc.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US