Redundant hash join in SQL developer's autotrace execution plan?

Question

Total newbie in SQL developer, I'm using the autotrace to understand the execution plan. In this autotrace output, what's the purpose of the last hash join?

Suppose the second ("the upper one") nested loop join can already be the final view as all the attributes are there and predicate conditions are met?

SQL:

SELECT TITLE, LASTNAME 
FROM MOVIE2 M, ARTIST A 
WHERE CODECOUNTRY='aaej' AND M.IDMES=A.IDARTIST;

Answer 1

Please check the new feature of Oracle 12g Adaptive Joins

e.g. here (blog) or here (white paper) or here (documentation)

Oracle will perform either NESTED LOOPS if there are small number of rows to be joined,

or will fall back to a HASH JOIN if there are a large number of rows, because the NESTED LOOP will take too long time to finish in this case.

The important line in the plan is STATISTICS COLLECTOR that observes the threshold of the row count and triggers the plan change if appropriate.