"Oracle" three table join mode __oracle

Table joins in three different ways: sort merge joins (sort merge join), nested loops joins (Nested Loops join), Hash joins (hash join). 1. Sort merge joins (sort merge Join)

The execution of the sort merge join IS as follows:

* Sort the rows of each row source by the connection predicate column

* Then merge two sorted row sources and return the generated row source

For example:

SELECT * FROM Employees d,departments T where d.department_id=t.department_id;
The access mechanism is as follows: Access the Departments table and sort the department_id columns, access the Employees table and sort department_id columns, then alternately compare and merge.

Note: Sort merge joins are generally used in cases where there are no indexes in both tables and the connection columns are sorted.

2. Nested loops join (Nested Loops join)

* One of the two tables is defined as "external table" (or "Driver table")

* Another table is called "Internal table"

* All matching rows in the internal table will be retrieved for each row in the external table

Note: The order of join is very important, generally select small table as "driver table", large table as "internal table". such as two tables, a 100 row, a 10000 line, the ideal way to connect is: 100 rows of small table as "driver table", 10000 rows of large table as "internal table", with each record in the small table to match the records in the large table. If the two tables ' connectors are indexed in a large table, they are the most perfect.

3. Hash joins (hash join)

* The optimizer creates a hash table in memory using the join key for the small table

* Scan large table, each get a record, on the hash table "probe" once, to find the hash table matching rows

Note: Hash Join is a common way for the CBO to connect large datasets.