using interMedia Text to search across M-M relationship

We have an image table and a keyword table and a xref table that supports the many-to-many relationship between them (each image has many keywords associated with it, and each keyword is associated with many keywords). The keyword table has a keyword_term which is a 1, 2, or 3 word search term.

The tables look like the following:

KEYWORD
id number primary key,
keyword_term varchar2(100)

IMAGE
id number primary key,
license_type varchar2(10),
photog_id number,
rank number

KEYWORD_IMAGE_XREF
image_id number,
keyword_id number,
primary key (image_id, keyword_id)

I have never used interMedia Text or ConText before, but I would like to use iMT to get use of the thesaurus and stemming capabilities, such that a search for images with keyword_term of CAR would return images with the keyword_terms CARS, AUTOMOBILE, CAR RACING, etc. Also, I want to be able to allow a user to enter searches like "CAR OR HORSE", "CAR AND HORSE", etc. and get all images which have those combinations of keyword_terms tied to them. Currently, our application is merging or intersecting the individual result sets returned from Oracle instead of letting the database do all the work.

We already have applications in place using this table structure, so I don't want to delve into replacing them with ORDImage. So, what would be the best approach to indexing the keyword_terms?

USER_DATASTORE looks promising, so one thought is to index the image table using a stored procedure that returns a CLOB of all the associated keyword_terms strung together with some sort of delimiter between the different terms, or maybe a nested table with each keyword_term in a separate row. It seems like that will give the proper results for booleans in the CONTAINS clause.

However, it seems to me that this approach doesn't easily lend itself to index maintenance. Image table entries are rarely deleted, but the keyword and xref tables are maintained regularly by an administrator, and changes would need to be reflected fairly quickly to the website. A regularly scheduled SYNC wouldn't catch changes to the keyword or xref table to cause the procedure to be re-run. Would I constantly have to do full rebuilds (say every 30 minutes)?

Is there a better way to handle indexing across a many-to-many relationship than the USER_DATASTORE method? Received on Tue Jun 05 2001 - 19:40:31 CDT