Demande d'algorithmes de jointure dans Oracle

Salut,

j'aimerais connaître Quels sont les différents algorithmes de jointure utilisés dans Oracle ? (autres que l'algorithme du produit cartésien,Sorge Merge, et par hachage)

Et Merci pour votre aide ! :ccool:

Bonjour,

Tu as oublié le Nested Loop.


En bonus quelques plans d'exécution:

Nested Loop:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

EXPLAINED SQL STATEMENT: ------------------------ select * from DEPT join EMP using(deptno) where sal>=3000   ---------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 13 | | 1 | NESTED LOOPS | | 1 | 3 | 13 | |* 2 | TABLE ACCESS FULL | EMP | 1 | 3 | 8 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 3 | 5 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 3 | 3 | 2 | ----------------------------------------------------------------------------

---

Sort Merge Join:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

EXPLAINED SQL STATEMENT: ------------------------ select * from DEPT join EMP on (EMP.deptno between DEPT.deptno and DEPT.deptno+10 ) where sal>=3000   ------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | OMem | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 14 | | | 1 | MERGE JOIN | | 1 | 5 | 14 | | | 2 | SORT JOIN | | 1 | 3 | 7 | 2048 | |* 3 | TABLE ACCESS FULL | EMP | 1 | 3 | 7 | | |* 4 | FILTER | | 3 | 5 | 7 | | |* 5 | SORT JOIN | | 3 | 5 | 7 | 2048 | | 6 | TABLE ACCESS FULL| DEPT | 1 | 4 | 7 | | -------------------------------------------------------------------------

---

Hash Join:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

EXPLAINED SQL STATEMENT: ------------------------ select * from DEPT join EMP using(deptno)   ----------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | OMem | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 15 | | |* 1 | HASH JOIN | | 1 | 14 | 15 | 1321K| | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 7 | | | 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 8 | | -----------------------------------------------------------------------

---

Merge Join Cartesian:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

EXPLAINED SQL STATEMENT: ------------------------ select /*+ leading(DEPT EMP) use_merge_cartesian(EMP) */ * from DEPT join EMP using(deptno) where sal>3000   ------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Buffers | OMem | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 15 | | | 1 | MERGE JOIN CARTESIAN| | 1 | 1 | 15 | | | 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 8 | | | 3 | BUFFER SORT | | 4 | 1 | 7 | 2048 | |* 4 | TABLE ACCESS FULL | EMP | 1 | 1 | 7 | | -------------------------------------------------------------------------

---

Cordialement,
Franck.