I promised to write about Oracle 12c new features quite a while ago (when 12c got officially released), but I was actually on (a long) vacation then and so many cool 12c-related white-papers and blog entries started popping up so I took it easy for a while. I plan to be focusing on the less known low-level internal details anyway as you see from this blog entry.
As far as I can remember, Oracle has been able to unnest regular subqueries since 8i and merge views since Oracle 8.0.
First, a little terminology:
Update: I have changed the terminology section below a bit, thanks to Jason Bucata’s correction. A scalar subquery sure can also be used in the WHERE clause (as you can see in the comments). So, I clarified below that this blog post is comparing the “Scalar Subqueries in SELECT projection list” to “Regular non-scalar Subqueries in WHERE clause”. I also wrote a Part 2 to explain the scalar subqueries in WHERE clause.
- A “regular” subquery in Oracle terminology is a query block which doesn’t return rows back to the select projection list, but is only used in the WHERE clause for determining whether to return any rows from the parent query block, based on your specified conditions, like
WHERE v IN (SELECT x FROM q)or
WHERE EXISTS (SELECT x FROM inner WHERE inner.v=outer.v)etc.
The subquery can return zero to many rows for the parent query block for evaluation.
- A scalar subquery is a subquery which can only return a single value (single row, single column) back to the parent block. It can be used both in the WHERE clause of the parent query or right in the SELECT list of the parent query instead of some column name. (In this post I am discussing only the case of a scalar subquery in SELECT clause). Whatever the scalar subquery returns, will be put in the “fake” column in the query result-set. If it returns more than one row, you will get the
ORA-01427: single-row subquery returns more than one rowerror, if it returns no rows for the given lookup, the result will be NULL. An example is below.
I crafted a very simple SQL with a scalar subquery for demo purposes (the tables are just copies of all_users and all_objects views):
SELECT u.username , (SELECT MAX(created) FROM test_objects o WHERE o.owner = u.username) FROM test_users u WHERE username LIKE 'S%'
Now, why would you want to write the query this way is a different story. I think it’s actually pretty rare when you need to use a scalar subquery, you usually can get away with an outer join. I have used scalar subqueries for populating some return values in cases where adding a yet another (outer) join to the query would complicate the query too much for my brain (as there are some limitations how you can arrange outer joins). I have only done this when I know that the query result-set (on which the scalar subquery is executed once for every row returned, unless the subquery caching kicks in!) will only return a handful of rows and the extra effort of running the scalar subquery once for each row is acceptable.
Nevertheless, non-experienced SQL developers (who come from the procedural coding world) write lots of scalar subqueries, even up to the point of having every single column populated by a scalar subquery! And this can be a serious problem as this breaks the query into separate non-mergeable chunks, which means that the CBO isn’t as free to move things around – resulting in suboptimal plans.
So, starting from Oracle 12c (and maybe even 18.104.22.168?), the CBO transformation engine can unnest some types of the scalar subqueries and convert these to outer joins internally.