메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Explain the Explain Plan: Join Methods

명품관 2021.02.10 19:34 조회 수 : 30

원본 : https://sqlmaria.com/2021/02/02/explain-the-explain-plan-join-methods/

 

Explain the Explain Plan: Join Methods

Continuing my blog series on reading and interpreting Oracle execution plans, this week’s post covers the different Join Methods and types available to the Optimizer.

What is an Oracle Join Method?

Join Methods are the techniques used by the Oracle Optimizer to join data coming from two data producing operators in an execution plan. You can find the individual Join Methods chosen by the Optimizer in the Operations column of an Execution table.

How many Join Methods does the Optimizer have to choose from?

 

The Oracles Optimizer supports three join methods; Nested Loops, Hash Join and Sort Merge Join.

Nested Loops Join

Nested loops joins are useful when small subsets

of data are being joined and if there is an efficient way of accessing the second table (for example an index lookup).

For every row in the first table (the outer table), Oracle accesses all the rows in the second table (the inner table) looking for a match. You can think of it as a set of embedded FOR loops.

NOTE: In Oracle Database 11g the internal implementation for nested loop joins changed to reduce overall latency for physical I/O. You may see two NESTED LOOPS operators in the plan’s operations column, where you previously only saw one on earlier versions of Oracle. You can find more details on why there are two operators in the video below.

Hash Joins

Hash joins are used for joining large data sets. The Optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger table and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row.

Sort Merge Joins

Sort Merge joins are useful when the join condition between two tables is an in-equality condition such as, <, <=, >, or >=. Sort merge joins can perform better than nested loop joins for large data sets. The join consists of two steps:

  1. Sort join operation: Both the inputs are sorted on the join key.
  2. Merge join operation: The sorted lists are merged together.

A Sort Merge join is more likely to be chosen if there is an index on one of the tables that will eliminate one of the sorts.

When will the Optimizer choose each of these methods, and what can I do to influence that decision?

To clearly explain how each of the Join Methods works and when they will be chosen, I’ve created the short video below.

What if I don’t get the Join Method I want?

The leading cause of getting the wrong Join Method is typically a cardinality misestimate on the table on the left-hand side of the join.  That’s why Oracle introduced Adaptive Plans and more specifically Adaptive Join Methods in Oracle Database 12c to help automatically correct itself if the wrong Join Method is chosen.

How Adaptive Joins work

During the initial execution of a plan, if Oracle detects that the Optimizer’s cardinality estimates were wrong, the join method can be changed “on the fly” to a better option.

It’s possible to change an adaptive plan “on the fly” because it consists of a default plan, which is the plan that the Optimizer picks based on the current statistics and an alternative method for various portions of the plan. For example, the default plan could be a Nested Loops plan, and the alternative(subplan) would be a Hash join.

A new operated called a Statistics Collector is inserted into the plan, right above the table on the left-hand side of the join, which will buffer the rows coming out of the table until we can get a sense of how many rows will be returned. Once we know the number of rows returned or the number is above a certain threshold, the Optimizer will choose the final join method. After the initial execution, the Statistics Collector and the subplan components not chosen become no-ops, and the impact on execution plan performance is nill.

Don’t forget this post is part of a series of posts on interpreting execution plans, which also covers, how to generate planscardinality estimates, and access methods.

The next instalment will be all about join orders.

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 188
» Explain the Explain Plan: Join Methods 명품관 2021.02.10 30
43 Explain the Explain Plan: Access Methods 명품관 2021.02.10 26
42 Explain the Explain Plan: Cardinality Estimates 명품관 2021.02.09 16
41 Explaining the Explain Plan – How to Read and Interpret Execution Plans 명품관 2021.02.09 26
40 SQL Window Functions Cheat Sheet 명품관 2020.05.26 154
39 SQL Tuning Workshop 명품관 2020.02.20 1439
38 New initialization parameters, data dictionary views & dynamic performance views in Oracle Database 19c 명품관 2019.02.08 1315
37 More New Features in Oracle Database 19c 명품관 2019.02.08 524
36 New Features of Performance Tuning in Oracle Database 19c 명품관 2019.02.08 532
35 New Features of RAC & ASM in Oracle 19c 명품관 2019.02.08 642
34 New Features of Data Guard in Oracle Database 19c 명품관 2019.02.08 449
33 New Features of Security in Oracle Database 19c 명품관 2019.02.08 3246
32 Patch conflicts 명품관 2019.02.07 439
31 New Features in Oracle Multitenant 19c 명품관 2019.02.07 466
30 New Features of Backup & Recovery in Oracle Database 19c 명품관 2019.02.07 208
29 New Features in Oracle Database 19c 명품관 2019.02.02 375
28 Different MOS Notes for xTTS PERL scripts – Use V4 scripts 명품관 2019.01.29 157
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 205
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 287
25 How to change the database name in 12c 명품관 2016.05.31 325
위로