Nested Loops, Hash Joins, Sort Merge Joins, Bloom Filters, Parallel Plans, and Adaptive Features

Learning outcomes

ServiceHub has one query joining a selective customer lookup to a handful of work orders and another joining two broad reporting sets. “Nested loops for small tables, hash joins for big tables” is not a reliable rule because the optimizer chooses from row counts flowing through operators, available access paths, join predicates, ordering, memory, distribution, and sometimes runtime adaptation.

1. Nested loops multiply inner-probe work

A nested loops join consumes rows from an outer row source and probes the inner source for each relevant outer row. It can be excellent when the outer result is small and the inner access is cheap—often an index lookup. If the optimizer underestimates outer rows, repeated inner probes can become much more expensive than costed.

2. Hash joins build and probe

A hash join normally builds a hash table from one input and probes it with the other. It is attractive for equijoins processing substantial row sets without a cheap repeated lookup. If the build structure does not fit in available work-area memory, Oracle can partition/spill through TEMP, making PGA sizing and concurrent work important. A hash join does not imply both base tables are “big”; the build side may be small while the probe side is large.

3. Sort merge joins use ordered streams

A sort merge join sorts inputs on join keys when needed and then merges the ordered streams. It can be useful when inputs are already ordered or for certain non-equijoin conditions. Sort cost and TEMP usage matter. Oracle costs the legal alternatives rather than following a fixed hierarchy of join methods.

4. Bloom filters eliminate definite nonmatches

A bloom filter is a compact probabilistic membership structure often created from one join input and applied to another. Rows that definitely cannot match are discarded early; false positives can pass through for later verification, but true matches must not be lost. Plans may show JOIN FILTER CREATE and JOIN FILTER USE, especially in parallel/star-style execution.

5. Parallel execution is a data-flow topology

Parallel execution introduces a query coordinator, PX server sets, table queues, and distribution methods such as hash distribution or broadcast. It can reduce response time for suitable work but increases CPU, memory, I/O, and concurrency consumption. Oracle AI Database Free is capped at two foreground CPUs, so this chapter teaches PX plan literacy rather than claiming enterprise DOP performance from the lab.

EXPLAIN PLAN SET STATEMENT_ID='SH09L4_PX' FORSELECT /*+ PARALLEL(w 2) */ region_id,SUM(amount)FROM servicehub_join_work_order w GROUP BY region_id;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH09L4_PX','TYPICAL +PARALLEL'));

6. Adaptive plans can defer selected choices

Current 26ai documents OPTIMIZER_ADAPTIVE_PLANS=TRUE as the default. Adaptive plans can defer choices such as nested-loop versus hash-join selection, star-transformation bitmap pruning, and parallel distribution until runtime statistics are available. This does not mean every query is adaptive or that Oracle can arbitrarily replace the plan at any point.

SELECT name,value,isdefault,isses_modifiable,ispdb_modifiableFROM v$parameter WHERE name='optimizer_adaptive_plans';

7. Wrong rule: force hash join because a table has a million rows

If a unique outer lookup produces one row and an inner index probe is cheap, nested loops can be ideal even when the inner table is huge. Conversely, a small table can participate in a hash join when scanning/building is cheaper. Diagnose row-flow shape and estimates rather than table-size labels.

8. Hands-on lab: compare selective and broad shapes

DROP TABLE servicehub_join_work_order IF EXISTS PURGE;DROP TABLE servicehub_join_customer IF EXISTS PURGE;CREATE TABLE servicehub_join_customer(customer_id NUMBER PRIMARY KEY,customer_code VARCHAR2(20) UNIQUE NOT NULL,tier_code VARCHAR2(10) NOT NULL);CREATE TABLE servicehub_join_work_order(work_order_id NUMBER PRIMARY KEY,customer_id NUMBER NOT NULL,region_id NUMBER NOT NULL,amount NUMBER(10,2) NOT NULL);INSERT INTO servicehub_join_customer SELECT LEVEL,'C-'||LPAD(LEVEL,6,'0'),CASE MOD(LEVEL,4) WHEN 0 THEN 'GOLD' ELSE 'STD' END FROM dual CONNECT BY LEVEL<=1000;INSERT INTO servicehub_join_work_order SELECT LEVEL,1+MOD(LEVEL,1000),1+MOD(LEVEL,20),MOD(LEVEL*19,10000)/10 FROM dual CONNECT BY LEVEL<=20000;CREATE INDEX sh09_l4_wo_customer_ix ON servicehub_join_work_order(customer_id);BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_JOIN_CUSTOMER',cascade=>TRUE); DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_JOIN_WORK_ORDER',cascade=>TRUE); END;/

SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_code,w.work_order_idFROM servicehub_join_customer c JOIN servicehub_join_work_order w ON w.customer_id=c.customer_idWHERE c.customer_code='C-000042';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));

SELECT /*+ GATHER_PLAN_STATISTICS */ c.tier_code,SUM(w.amount)FROM servicehub_join_customer c JOIN servicehub_join_work_order w ON w.customer_id=c.customer_idGROUP BY c.tier_code;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));DROP TABLE servicehub_join_work_order PURGE;DROP TABLE servicehub_join_customer PURGE;

Do not fail the exercise if the optimizer chooses a different legal method. Explain its input rows, Starts, Buffers, access paths, and estimates.

9. Production judgment

Join methods are consequences of estimated input shapes and costs. Parallel and bloom-filter plans add distribution/resource dimensions; adaptive plans can defer specific decisions. Do not manipulate hidden/underscore parameters to force behavior. No parallel speedup claim is made from the Free lab.

Lesson 5 closes the chapter by asking how to govern plan stability when statistics repair is insufficient or an incident requires containment.

Authoritative references

• Joins — join algorithms
• OPTIMIZER_ADAPTIVE_PLANS — current adaptive-plan control
• Parallel Execution — PX data flow
• DBMS_XPLAN — runtime and parallel plan display