Three methods for multi-table JOIN: HASH JOIN MERGE JOIN NESTED LOOP

When performing a multi-table joint query, if we view its execution plan, we will find a connection method between multiple tables. I was planningSqlplusThe execution plan is used, but the format looks messy.ToadDone3.

 

 

 

 

 

 

 

 

 

Slave3Zhang Yu:

1.CBOUsedALL_ROWSMode 

2.The connection between tables is used.Hash Join, Nested loops,Sort Merge Join 

 

There are three methods to connect multiple tables:Nested Loops,Hash JoinAndSort Merge Join.The following describes three different connections:

 

 

I. Nested loop:

Nested loop join is a good choice for a small subset of connected data.. In a nested loop, the internal table is driven by the External table. Each row returned by the External table must be searched for the matched rows in the internal table. Therefore, the result set returned by the entire query cannot be too large (greater1Is not suitable). The table with a small subset returned should be used as the External table (CBOThe default External table is the driver table), and the connection fields of the internal table must have an index. You can also useORDEREDPrompt to changeCBODefault Driver table,UseUSE_NL (table_name1 table_name2)However, forceCBOExecute nested loop connection.

Nested loopIt is generally used when the connected table has an index and the index is more selective..

 

Steps:Determine a driver table(Outer table)And the other table isInner table, Each row in the driver table andInnerCorresponding records in the tableJOIN. Similar to a nested loop.A small set of records (<10000) AndInnerThe table must have a valid access method (Index). Note that:JOINThe order is very important. The record set of the driver table must be small, and the response time of the returned result set is the fastest.

Cost = outer access cost + (inner access cost * outer cardinality)

 

| 1 | nested loops | 3 | 141 | 7 (15) |
| 3 | table access full | EMPLOYEES | 3 | 60 | 4 (25) |
| 4 | table access by index rowid | JOBS | 19 | 513 | 2 (50) |
| 5 | index unique scan | JOB_ID_PK | 1 |

EMPLOYEESIsOuter table, JOBSIsInner table.

1 2 Next Page