Dans cette page, je vais présenter trois méthodes de jointures, chacune peut avoir des variantes un peu différentes, en fonction des options de jointure (INNER, OUTER, CARTESIAN) :

• Hash Joins
  
• Sort-Merge Joins
  
• Nested Loops Joins
  

This table (and its data) is used in the examples bellow:
CREATE TABLE table_join_methods ( id1 NUMBER(5,0) NOT NULL , id2 NUMBER(5,0) NOT NULL , type1 NUMBER(1,0) NOT NULL , CONSTRAINT cp_table_join_methods_id1 PRIMARY KEY (id1) -- Primary_Key );
DECLARE indexLoop NUMBER(5,0); BEGIN FOR indexLoop IN 1..50000 LOOP IF MOD(indexLoop,2) = 0 THEN INSERT INTO table_join_methods (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 1); ELSE INSERT INTO table_join_methods (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 2); END IF; END LOOP; END; / COMMIT;
CREATE INDEX ix_table_join_methods_id2 ON table_join_methods (id2); ANALYZE INDEX ix_table_join_methods_id2 COMPUTE STATISTICS;
CREATE INDEX ix_table_join_methods_type1_id2 ON table_join_methods (type1,id2); ANALYZE INDEX ix_table_join_methods_type1_id2 COMPUTE STATISTICS;

Dans ce cas, les données des deux tables sont lues en mémoire, et les données de la table principale sont hachées.
Pour chaque ligne de la table secondaire, la fonction de hachage est appliquée sur les colonnes de la jointure pour décider s'il y a un match ou non avec une ligne de la table principale.

Typiquement la table hachée doit être celle qui censée retourner le moins de lignes.
SELECT tjm1.id1 FROM table_join_methods tjm1 JOIN table_join_methods tjm2 ON tjm2.id2 = tjm1.id1 WHERE tjm1.type1 = 1
Plan d'exécution : ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 166 | | | | |* 1 | HASH JOIN | | 1 | 36890 | 50 |00:00:00.01 | 166 | 1295K| 1295K| 1738K (0)| |* 2 | TABLE ACCESS FULL | TABLE_JOIN_METHODS | 1 | 20887 | 25000 |00:00:00.01 | 159 | | | | | 3 | INDEX FAST FULL SCAN| IX_TABLE_JOIN_METHODS_ID2 | 1 | 41763 | 96 |00:00:00.01 | 7 | | | | ---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TJM2"."ID2"="TJM1"."ID1") 2 - filter("TJM1"."TYPE1"=1)

Dans ce cas, les lignes des deux tables, qui matchent avec les conditions de la clause WHERE, sont lues en mémoire, et triées par les colonnes de la jointure.
En suite, les lignes des deux tables sont fusionnées ensemble en gardant que les lignes qui matchent avec les conditions de la jointure.
SELECT tjm1.id2, tjm2.id2 FROM table_join_methods tjm1 JOIN table_join_methods tjm2 ON tjm2.id2 = tjm2.id2 WHERE tjm1.type1 = 1 AND tjm2.type1 = 2
Plan d'exécution : --------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 69 | | | | | 1 | MERGE JOIN CARTESIAN| | 1 | 734M| 50 |00:00:00.01 | 69 | | | | |* 2 | INDEX RANGE SCAN | IX_TABLE_JOIN_METHODS_TYPE1_ID2 | 1 | 27092 | 1 |00:00:00.01 | 2 | | | | | 3 | BUFFER SORT | | 1 | 27099 | 50 |00:00:00.01 | 67 | 619K| 472K| 550K (0)| |* 4 | INDEX RANGE SCAN | IX_TABLE_JOIN_METHODS_TYPE1_ID2 | 1 | 27099 | 25000 |00:00:00.01 | 67 | | | | --------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TJM2"."TYPE1"=2) 4 - access("TJM1"."TYPE1"=1)

Dans ce cas, la jointure est appliquée en effectuant "deux boucles imbriquées" sur les deux tables (ou un de ses indexes si disponibles) :
la première boucle est effectuée sur la table principale de la jointure, et pour chaque itération de cette boucle une deuxième boucle est effectuée sur la table secondaire.
La ligne de la table principale et la ligne de la table secondaire seront retenue si la condition de la jointure matche.

SELECT tjm1.id1 FROM table_join_methods tjm1 JOIN table_join_methods tjm2 ON tjm2.id2 = tjm1.id1
Plan d'exécution : ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 16 | | 1 | NESTED LOOPS | | 1 | 41763 | 50 |00:00:00.01 | 16 | | 2 | INDEX FAST FULL SCAN| IX_TABLE_JOIN_METHODS_ID2 | 1 | 41763 | 50 |00:00:00.01 | 7 | |* 3 | INDEX UNIQUE SCAN | CP_TABLE_JOIN_METHODS_ID1 | 50 | 1 | 50 |00:00:00.01 | 9 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TJM2"."ID2"="TJM1"."ID1")