Oracle Text for searchengine across multiple columns/tables with different datastore

[Nikola Pecigos]

Hi,

I have the following problem:

We have an Oracle 9.2 with one table "document" which contains a path
to the filesystem. If I want to index these files (HTML, PDF, World,
Excel, etc.), I have to use the datastore type "FILE_DATASTORE".

Another table "lng_text" stores the titles and descriptions for
multiple languages for each row in table "document".

My goal is to build an index, which I can query with AND or OR
Operator across all three columns in both tables.

I read a lot about USER_DATASTORE and the posibillity to merge
different columns with a stored procedure in one index. But how is
this possible in this case, since one column contains a path to
filesystem and two other columns are VARCHAR(500)?
Thanks in advance!

Greetz,
Nikola Pecigos

PS: As far as I realize it, building two or three indexes does not
solve the problem. If I had more than one searchword like "this &
that" and query multiple indexes,

WHERE CONTAINS(t1.column, 'this & that', 10) > 1 OR
CONTAINS(t2.column, 'this & that', 20) > 1
I would request boths searchwords to appear together in ONE index or
the other. But it has to be possible, that "this" is found in t1 and
"that" in t2.

[Frank van Bortel]

Nikola Pecigos wrote:

Hi,

I have the following problem:

We have an Oracle 9.2 with one table "document" which contains a path
to the filesystem. If I want to index these files (HTML, PDF, World,
Excel, etc.), I have to use the datastore type "FILE_DATASTORE".

Another table "lng_text" stores the titles and descriptions for
multiple languages for each row in table "document".

My goal is to build an index, which I can query with AND or OR
Operator across all three columns in both tables.

I read a lot about USER_DATASTORE and the posibillity to merge
different columns with a stored procedure in one index. But how is
this possible in this case, since one column contains a path to
filesystem and two other columns are VARCHAR(500)?
Thanks in advance!

Greetz,
Nikola Pecigos

PS: As far as I realize it, building two or three indexes does not
solve the problem. If I had more than one searchword like "this &
that" and query multiple indexes,

WHERE CONTAINS(t1.column, 'this & that', 10) > 1 OR
CONTAINS(t2.column, 'this & that', 20) > 1
I would request boths searchwords to appear together in ONE index or
the other. But it has to be possible, that "this" is found in t1 and
"that" in t2.

No - you would use 'this AND that' or 'this OR that' ('this | that')

Frankly, I don't see your problem.
If you build a contatenated index, as you propose, you still don't
know where the word came from - the title, the description, or the
document itself. All you know is the index found a (one or more) hit.

Anyway - you can use a user_datastore to concatenate your
columns-to-be-indexed into a CLOB, and query it. This can be
done over multiple tables, and probably over external files
as well.
Performance wise, I would opt for storage *within* the database,
though. The indexing process will need to read them anyway. ANd
your backups will be consistent!

I'd recommend reading chapters 2 and 3 of
http://otn.oracle.com/pls/db92/db92....emark=docindex

--
Regards,
Frank van Bortel