Hi Michel,
You can do all of this with Lucene however not with a standard index/query operators. At Attivio we have a custom Lucene index structure + custom query operators that support relational joins across records in an index. You can write the queries in our standard query language or run actual SQL. All of this is done without pre-computing or flattening records as that prevents you from having query flexibility at runtime, ie what happens when you want to know something that isn’t pre-computed/pre-flattened? If you look at the demo at the bottom of this page http://www.attivio.com/active-intelligence/aie-demo.html you can see how we index and query against both news articles and baseball statistics from a relational database. For example you can do something like this with the baseball data: select sum(RBI), teamID, yearID from master m join batting b on m."playerID" = b.playerID where b.yearID > '2004' group by yearID,teamID order by yearID,teamID We support min, max, avg and a number of other aggregate functions along with true full-text search. Another article you might check out is here: http://www.attivio.com/blog/55-industry-insights/507-can-a-search-engine-replace-a-relational-database.html . So far we're getting some pretty good results competing with databases and data warehouses on raw performance (at customer sites) even without the full text search capabilities mixed in. Once you start adding in 'fuzzy' joins, relevancy, proximity and all the other boolean query logic, we start to pull ahead even further. If you want to learn more drop me a line. We'll be demonstrating all this stuff (and more) at Enterprise Search Summit (ESS) in New York this coming May. - w...@attivio.com
