How to use oracle text to search multiple columns across multiple tables join
ningju...@lexisnexis.com
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
Vladimir M. Zakharychev
One way to achieve this could be DETAIL_DATASTORE, where
indexed text is in detail table while the index is created on
master table. Unfortunately, in your case there is more than
one detail table, the case not covered by DETAIL_DATASTORE.
However, not all is lost: you can use USER_DATASTORE
for the index. You need to create your own text feeding
procedure that will feed the Text indexing engine with text
to be indexed. Assuming you're on 10g, you could do
it like this:
create or replace procedure my_text_feeder
( r in rowid
,c in out nocopy clob)
-- names of the arguments can be any,
-- but types MUST be ROWID, CLOB
as
begin
for x in (select B.title, B.description, A.authordescription
from Book B, AuthorToBook AB, Author A
where AB.rowid = r
and B.bookid = AB.bookid
and A.authorid = AB.authorid)
loop
-- we will generate XML document from our data to
-- take advantage of automatic sectioning
dbms_lob.writeappend(c, 19, '<root><book><title>');
dbms_lob.writeappend(c, length(x.title), x.title);
dbms_lob.writeappend(c, 21, '</title><description>');
dbms_lob.writeappend(c, length(x.description), x.description);
dbms_lob.writeappend(c, 40,
'</description></book><authordescription>');
dbms_lob.writeappend(c, length(x.authordescription),
x.authordescription);
dbms_lob.writeappend(c, 27, '</authordescription></root>');
end loop;
end;
/
Then, you create a preference for indexing:
begin
ctx_ddl.create_preference('bookinfodatastore', 'user_datastore');
ctx_ddl.set_attribute('bookinfodatastore', 'procedure',
'my_text_feeder');
ctx_ddl.set_attribute('bookinfodatastore', 'output_type', 'CLOB');
end;
/
Then you add a fake text column to the AuthorToBook table that
the index will be built on (Text indexes can only be created on
text data types, you can't create Text index on a number column
even with user datastore):
alter table AuthorToBook add (text varchar2(1))
/
And then you create the index:
create index ctx_books on AuthorToBook(text) indextype is
ctxsys.context
parameters ('datastore bookinfodatastore section group
ctxsys.auto_section_group')
/
You can then query the index like this:
select Book.*, Author.* from Book, AuthorToBook, Author
where Book.bookid = AuthorToBook.bookid
and AuthorToBook.authorid = Author.authorid
and Author.age > 20
and contains(authortobook.text,
'network within authordescription and (network within title or network
within description)') > 0
Note that there's single CONTAINS operator in the query
and the text query takes advantage of automatic sectioning
and uses WITHIN keyword to restrict term search to particular
section corresponding to source column (that's why we
created XML in feeder procedure.)
If you're on 9i or before, things are a bit more complex as the
feeder procedure must be owned by CTXSYS, but needs to be
able to read data it aggregates and index owner should be
able to execute it, so a few extra grants are due (and the
procedure must use fully qualified table names.) This restriction
had been lifted in 10g.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Tasm
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?