I'll second Chris's comment and ask whether you've considered denormalizing your data into Lucene and sticking exclusively with Lucene?
Contrariwise, look for anything by Marcelo Ochoa on the user list about embedding Lucene in Oracle (which I confess I haven't looked into at all, but seems interesting). Best Erick On Sat, Feb 28, 2009 at 2:07 PM, <spr...@gmx.eu> wrote: > Hi, > > what is the best approach to merge a database index with a lucene fulltext > index? Both databases store a unique ID per doc. This is the join criteria. > > requirements: > > * both resultsets may be very big (100.000 and much more) > * the merged resultset must be sorted by database index and/or relevance > * optional paging the merged resultset, a page has a size of 1000 docs max. > > example: > > select a, b from dbtable where c = 'foo' and content='bar' order by > relevance, a desc, d > > I would split this into: > > database: select ID, a, b from dbtable where c = 'foo' order by a desc, d > lucene: content:bar (sort:relevance) > merge: loop over the lucene resultset and add the db record into a new list > if the ID matches. > > If the resultset must be paged: > > database: select ID from dbtable where c = 'foo' order by a desc, d > lucene: content:bar (sort:relevance) > merge: loop over the lucene resultset and add the db record into a new list > if the ID matches. > page 1: select a,b from dbtable where ID IN (list of the ID's of page 1) > page 2: select a,b from dbtable where ID IN (list of the ID's of page 2) > ... > > > Is there a better way? > > Thank you. > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: java-user-unsubscr...@lucene.apache.org > For additional commands, e-mail: java-user-h...@lucene.apache.org > >
