I have an abstract class which is extended by some subclass. I persist this structure with hibernate. The Inheritance type is set to TABLE_PER_CLASS so every child class has it's own table.
I have a @NamedQuery on the abstract class and it has Where clause. I checked the generated SQL and it is not the best. At first it queries the whole table of every child classes then it uses UNION to merge them into one. Finally the where clause filters the merged table. So this SQL SELECT queries the whole TABLES with lot of records and it makes the application very slow. I think it would be more optimized to use the where part on the query of the child tables and not on the merged table.
How can I do that?
I have tried to find any example or tutorial about it, but I couldn't find!
I can't change the annotation TABLE_PER_CLASS because many user use this application. The tables contains a few hundred thousand record. If I change that annotation, the hibernate will refactor the table structure in the database. Unfortunately I can't allow myself to lost every data in the database. Although I think it could solve my problem.
If I use SQL, I will have to change the query on the anchestor class every time when I create a new child class.
That's why I try to find an other way