Database Software

Hi,

How can i do search on multiple tables using Oracles text's CTXCAT indexing?

For example, I have the following tables.

Table Book {
book_id number(8),
title varchar2(1000),
description varchar2(1000),
meta1_id number(8),
meta2_id number(8),
auth_id number(8),
pub_id number(8),
}

Table Author {
auth_id number(8),
auth_name varchar2(100),
auth_address varchar2(1000)
}

Table Publisher {
pub_id number(8),
pub_name varchar2(100),
pub_address varchar2(1000),
pub_country varchar2(100)
}

Table BookMeta1 {
meta1_id number(8),
meta1_desc varchar2(1000)
}

Table BookMeta2 {
meta2_id number(8),
meta2_desc varchar2(1000)
}

From the above tables, i have a requirement to do a keyword search from any of the columns.

As a solution, I planned to create another table like below in which column search_text will have concatenated string of all the above columns so that i can index and search on that column alone and get the book_id's and then join with remaining columns to get the exact info to show to the user.
Book_Search {
book_id number(8),
search_text varchar(4000)
}

So, I have created index by

CREATE INDEX BOOK_IDX ON Book_Search(search_text) INDEXTYPE IS CTXSYS.CTXCAT

I know that this approach works. But Is this efficient?

Now, want result set of "books written by author 100 and which satisfy the keywords 'earth and fire'.

SELECT b.title,
b.description,
a.au_name,
a.au_address,
p.pub_name,
p.pub_country,
bm1.meta1_desc,
bm2.meta2_desc
FROM book b,
author a,
publisher p,
book_meta1 bm1,
book_search bs
WHERE b.author_id = a.auth_id
AND b.pub_id = a.pub_id
AND b.meta1_id = bm1.meta1_id
AND b.meta2_id = bm2.meta2_id
AND a.auth_id = 100
AND CATSEARCH(search_text, "earth fire") > 0;
order by b.title

Now the question is, Can i join indexed table i.e. book_search with other regular tables? Is this efficient approach? (or) Do i need to index Id's of other tables as well?