JPA or Hibernates With Oracle Table Partitions?

Question

Hi, I need to use an Entity framework with my application, and I have used table - partitions in Oracle database. With simple JDBC, I am able to select data from a specific partition. But I don't know whether I can do the same with hibernate or Eclipse link (JPA). If someone knows how to do that, please do let me know.

usually the select statement in JDBC - SQL is,

select * from TABLE_NAME partiton(PARTITON_NAME) where FIELD_NAME='PARAMETER_VALUE';

How can I do the same with Hibernates or JPA?

Please share at least a link for learning sources.

Thanks!!!

Answer 1

JPA or any other ORM framework does not support Oracle partition tables natively (atleast in my knowledge).

There are different possible solutions though, depending on the nature of your problem:

• Refactor your classes so that data that needs to be treated differently in real-life, belongs in a separate class. Sometimes this is called vertical partitioning (partitions are not obtained across rows, rather across columns).
• Use Oracle partition tables underneath and use native SQL queries or stored procedures from JPA. This is just a possibile solution (I haven't attempted this).
• Use Hibernate Shards. Although the typical use case for Hibernate Shards is not for a single database, it presents a singular view of distributed databases to an application developer.

Related:

1. JPA Performance, Don't Ignore the Database

Answer 2

Table partitioning is data organization on physical level. In a word, partitioning is a poor man index. Like the later, it is supposed to be entirely transparent to the user. A SQL query is allowed to refer to the entire table, but not partition. Then, it is query optimizer job to decide if it can leverage a certain partition, or index.