Ximing Yu

Karthik, I read the documentation of Hibernate Shard and found it to be a viable solution to my problem. However, I also noticed that it does not support HQL very well and I may need a lot sorting and other aggregation in my query. So I guess it might lead to some trouble.
It's also a pity that Hibernate Shard does not support JPA...

Thanks Karthik. I'll take look at Hibernate Shards.

Karthik Shiraly wrote:Hi Ximing,

Welcome to the Ranch!

Just a few suggestions....

Does the legacy database support database views, so you can construct one virtual table and then optimize performance through indexes or other database mechanisms?

Other than that, perhaps you can have a look at the Hibernate Shards framework - your tables are like shards, except that the term "shards" usually implies partitioning across databases. I'm not sure if Hibernate Shards supports your use case - you may have to experiment.

I'll be very grateful to you if you can please let me know your findings when you've eventually settled on an approach, since I find the problem interesting.

I'm working with a legacy database and have about 30 tables with same columns. There will be 13 million rows in total in the 30 tables. Is it possible to map a class to all 30 tables and have an attribute in class that map to different table name?

Using SQL to do query in any one table is already quite slow, so I doubt combining all 30 tables to one big table will result in unbearable low efficiency. Anyone have any idea about solving this kind of problem? Thanks.