I have two Solr cores.
Core0 imports data from a Oracle table called items. Each item has a unique id (item_id) and is either a video item or a audio item (item_type). Other fields contain searchable texts (description, comments etc)
Core1 imports data from two tables (from a different database) called video_item_dates and audio_item_dates which record occurrence dates of an item in a specific market. The fields are item_id, item_market and dates. A single row would look like (item_001, 'Europe', '2011/08/15, 2011/08/17,2011/08/20). The unique key in these two database tables here is the combination of item_id and item_market. I have flattened data into a single index for Core1.
My problem now is searching both cores to produce a single result. A typical query would be like 'What are the items that have the word Hurricane in the description field and ran in North American market during the the month of August 2011?'. I could separate this query into two different queries and make them run against a different core and then merge the results. But given the fact each query may produce millions of rows that approach is very inefficient.
I tried the Solr Distributed Search. I created a third core (called Core2) with fields from Core0 and Core1. I added a request handler with shards attribute to the third core like this :
<requestHandler name="shard" class="solr.SearchHandler">
<lst name="defaults">
<str name="shards">localhost/solr/core0/,localhost/solr/core1/</str>
</lst>
</requestHandler>
If I run a query against this third core, it forwards the query to both Core0 and Core1 and since neither of them have all the fields , one of them reports "undefined field" and the response is a bad request error message.
Any help would be greatly appreciated.
Please note I have no control over the structure of the database tables.
This does not seem to be a case for multiple cores. You should look into designing a single schema that supports the desired search.