Comment by ttfkam

From parent:

> you're not probably not going to be migrating away from it any time soon.

Oracle has its own optimizations and foot-guns that extend well beyond what you can represent in a database-agnostic API. And once you're on that DB, you can write DB-agnostic and have performance be relatively horrible or require a careful rewrite of your schema and stored procedures when you migrate. There is not door number three.

Writing a common layer for any and all relational databases is like using a Java UI library for all operating systems. Sure, it will work, but it will have obvious shortfalls, be immediately recognizable as such to anyone familiar with the underlying platform, be inconsistent with other apps on that platform, and leave any opportunities for efficiency and performance on the floor.

Say you want a pivot table. In Oracle and MS SQL, it's built in. In Postgres, it's possible but noticeably more annoying. In MySQL, it's simply not possible. How would you represent this in a database-agnostic way? And yet performing in the app layer is very much slower/less efficient.

Did you know Oracle supports parallel DML for enhanced performance and lower multi-query latency? You have to intentionally use though, and neither Postgres nor MySQL support it at all.

What about global temporary tables? Those especially aren't found in Postgres or MySQL and are not easily swapped into the app layer without a massive performance penalty.

Per-user namespaces are yet another Oracle-ism that just doesn't translate to other DB engines, but you definitely should know about.

If you're making a living from Oracle, earn your pay. Make the most of what you've got.