@philfrei Instead of taking a connection from the pool, executing a single bigger SQL query and putting this connection back into the pool, it creates many smaller SQL queries and for each of them, it takes a connection from the pool, it executes this query and it puts this connection back into the pool.
Both Oracle and PostgreSQL compute an execution plan before executing an SQL query, they are able (in theory) to optimize the query itself (query transformations, you can disable them by using some hints, maybe you know the famous
NO_QUERY_TRANSFORMATION) and its execution. Imagine that you want to get an instance of Car, you need to eagerly retrieve its instances of Wheel, its instance of SteeringWheel, etc; I succeed in writing a single query (by using plain JDBC) to retrieve the Car whereas Hibernate JPA needs multiple queries. All relational database management systems have bugs, I find some RDBMS buggier than others and more complicated queries are more exposed to these bugs. Using simpler SQL queries can be a mean of favoring safety over performance and I suspect that some implementation choices were made with some considerations I disagree with, especially using simpler SQL queries for most RDBMS not to exhibit some bugs in some specific RDBMS.
Just to be sure that we’re talking about the same things, using sub-queries and (inner, outer, …) joins is ok, using several distinct queries when you can use a single one might cause performance problems. By the way, yes, using indices to filter and sort is a lot faster
Oracle’s optimizer might pick a very bad execution plan in some cases, you can either force it to use a particular execution plan, disable some optimizations, the
RULE hint disables the use of the query optimizer or even write your own pre-optimizer in order to simplify the SQL queries before sending them to Oracle (it allows to work around the bugs caused by the wrong branch being kept in a condition). I reminded that you need a Platinum subscription to report bugs against Oracle SQL because to me, it means that many programmers that can’t afford such an expensive subscription must learn how to live with those bugs or they have to switch to a less buggy RDBMS, which is what I advise after more than 18 years of experience in databases.