470,591 Members | 2,162 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

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

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.
Jul 19 '05 #1
1 7209
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by JWM | last post: by
8 posts views Thread by Jan van Veldhuizen | last post: by
6 posts views Thread by shaun.mostashari | last post: by
1 post views Thread by Andrew Arace | last post: by
1 post views Thread by Evan M. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.