3

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?

autotrace output

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;
user7131669
  • 61
  • 1
  • 1
    Please learn e.g. [here](https://stackoverflow.com/a/34975420/4808122) how you can post the execution plan *in text form* (not image) and *complete* (with predicate information). – Marmite Bomber Oct 13 '20 at 12:51

1 Answers1

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.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Thanks! I've now run the execution plan in text form as you've suggested. I notice there's only a `hash join` executed. So, for the 2 `nested loop joins` seen in autotrace, they are not executed after `statistics collector` ran the analysis? Even so, I don't get why there are 2 `nested loop joins` in the autotrace; suppose if the collector needs to compare between `hash` & `nested loop joins`, there should still only be 1 (instead of 2) nested loop shown in autotrace? – user7131669 Oct 14 '20 at 10:59