Examine the following query and execution plan: 
Which query transformation technique is used in this scenario?
A.
Join predicate push-down
B.
Subquery factoring
C.
Subquery unnesting
D.
Join conversion
Explanation:
* Normally, a view cannot be joined with an index-based nested loop (i.e., index
access) join, since a view, in contrast with a base table, does not have an index defined on it. A
view can only be joined with other tables using three methods: hash, nested loop, and sort-merge
joins.
* The following shows the types of views on which join predicate pushdown is currently supported.
UNION ALL/UNION view
Outer-joined view
Anti-joined view
Semi-joined view
DISTINCT view
GROUP-BY view
For me, answer: “C”
https://docs.oracle.com/database/121/TGSQL/tgsql_transform.htm#TGSQL211
No , C is not correct answer ,since subquery has max aggregate function, hence Subquery unnesting is not allowed.
In subquery unnesting, the optimizer transforms a nested query into an equivalent join statement, and then optimizes the join. This transformation enables the optimizer to consider the subquery tables during access path, join method, and join order selection. The optimizer can perform this transformation only if the resulting join statement is guaranteed to return the same rows as the original statement, and if subqueries do not contain aggregate functions such as AVG.
Oops, sorry my mistake, I agree with you raka.
C is the correct answer