Oracle Database Table join method: nested loop, sorting-merge, hash instance explanation, oracle instance explanation

Oracle Database Table join method: nested loop, sorting-merge, hash instance explanation, oracle instance explanation

Oracle Database Table join method: nested loop, sorting-merge, and hash

Nested loop: Generally, a small table is the driving table, and a large table is the driving table. The data that meets the conditions in a small table is traversed cyclically to find the data that meets the conditions. In general, an index on the condition column of a large table will speed up the query. Small tables are queried once, and large tables are scanned N times.

SQL> set autotrace traceonly;

SQL> select/* + leading (dept) use_nl (emp) */empno, dname, loc

From emp, dept

Where emp. deptno = dept. deptno; -- if the prompt is incorrect, the prompt is ignored.

Sort-merge: data is first sorted by the join key, and then the sorted data rows are merged. Data that cannot be sorted in memory may be sorted using a temporary disk space.

Both tables are accessed once. (Memory consumption)

SQL> set autotrace traceonly;

SQL> select/* + ordered use_merge (dept) */empno, dname, loc

From emp, dept

Where emp. deptno = dept. deptno; -- if the prompt is incorrect, the prompt is ignored.

Hash join: hash the tables that return the least rows to the memory by using the hash function, and then hash the joined columns in another table, if yes, this row of data is returned; otherwise, this row is discarded. Each table is accessed once. (Memory and CPU consumption)

SQL> set autotrace traceonly;

SQL> select/* + leading (emp) use_hash (dept) */empno, dname, loc

From emp, dept

Where emp. deptno = dept. deptno; -- if the prompt is incorrect, the prompt is ignored.