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.

Nikola Pecigos wrote:
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....ark= docindex

--
Regards,
Frank van Bortel