Is there a way to force Hibernate to use literal values ​​rather than bind variables?

Question

In Oracle, I have a partitioned table. Sections have different sizes and have different data distribution.

I would like to have hibernate SQL statements containing a literal value for a section key column, not a binding variable. It should use bind variables for any other values, of course.

Using a literal for the partition key will allow Oracle to develop a plan specific to the known partition and collected statistics. It can also be useful for histogram columns for distorted data.

It would be preferable to indicate this in essence, otherwise we will need to do this in every request. Is there any way to do this in sleep mode?

We are in sleep mode 3.6.1 using the Oracle 10g dialog.

If there is no way in Hibernate to do this initially, can I create a user type or dialect or something similar for this to happen?

Answer 1

No, literal values ​​are not supported in Hibernate. I doubt you can make a workaround, but I think you are looking for a different solution.

Answer 2

You can use namedNativeQuery Here is an example implementation.

Entity class

@Entity @Table(catalog = DBCatalog) @org.hibernate.annotations.NamedNativeQuery(name="partitionTR1",query ="SELECT * FROM DATAMARTTRANSACTIONHISTORY PARTITION (tr1) where id=?",resultClass=DataMartTable.class) public class DataMartTransactionHistory implements TransactionHistory { @Id @Column @GeneratedValue(strategy = GenerationType.AUTO) private Long id; @Enumerated(EnumType.ORDINAL) private TransactionStatus transactionStatus; ... other props... } 

and here is the dao implementation.

 public DataMartTransactionHistory findDataMartTransactionHistoryTR1(Long id) { Query namedQuery = getSessionFactory().getCurrentSession().getNamedQuery("partitionTR1"); namedQuery.setLong(0, id); return (DataMartTransactionHistory)namedQuery.list().get(0); } 

Answer 3

As a workaround, you can set a unique comment on each "interesting" call site so that you can control the viewing of the Oracle bind binding.

 ... query = session.createQuery("..."); ... query.setString("param1", "FOO"); query.setInteger("param2", param2Value); ... query.setComment("param1 = \"FOO\""); ... 

Thus, the optimizer will see "FOO" with strong parsing time (as usual). In future calls, Oracle will look for an exact copy of SQL to reuse the execution plan. Since the comment makes the request unique, it will give you the same execution plan that was calculated using "FOO" , and not any other param1 value.

You have to be careful because the optimizer will also use the param2Value value to calculate the execution plan so that it can intervene. But I think that at least it’s worth a try.