Re: using interMedia Text to search across M-M relationship

Why not Create a context index on the KEYWORD table and do a join with XREF at query time?

You would have to do a SYNC on some interval. CTXSRV SYNCS automatically, but I read
that it is not the preferred method of maintaining indexes.

"Craig Battis" <cbattis_at_pragmatyxs.com> wrote in message news:b1e63043.0106051640.53a2eb9e_at_posting.google.com...
> 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 Wed Jun 06 2001 - 07:33:14 CDT