Choosing the best strategy for Oracle and MySQL

Hibernate ORM
1

After some days of researching and looking for information and according to my info, you only can choose a generation strategy using annotations: for example GenerationType.SEQUENCE or GenerationType.IDENTITY. In case of MySQL the best seems to be GenerationType.IDENTITY, but in case of Oracle it’s GenerationType.SEQUENCE.

So, what happen if you want the application to run with Oracle and MySQL? I’ve only found two ways: since Oracle 12c you can use GenerationType.IDENTITY (but anyway the sequences seems to be a better strategy), and the other way is using persistence.xml and externalize orm.xml to overrride the strategy annotation configuration.

I’ve not tested this last option because I think it’s dangerous and error proning. Why it’s not simply possible to have a sort of GenerationType.COMPATIBLE which would decide the best strategy for every database(sequences for Oracle and autoincrements for MySQL? Am I missing something?

2

Well, the “best” strategy is not necessarily what you think it is. If you tune/configure it correctly, I’d argue that using a sequence table is “best”, but the tuning/config depends on your requirements. I know that there are some articles out there which imply IDENTITY is best for MySQL, but this is simply not true. The benchmarks which are used to support that claim are simply not representative of a real world scenario and in most cases the sequence strategy is even wrongly tuned for the benchmark.

Did you try the sequence strategy (which uses a table on MySQL) and ran into a problem? Or are you just assuming it will be performing worse? Unless you can show me a reproducible test case that shows an issue with the sequence strategy, you could also just believe me when I say that the sequence strategy is the best.