Le contexte

J'ai découvert cette différence dans les moteurs quand j'ai proposé une optimisation de requête à un client sur Microsoft SQL Server. Dans mon esprit, le moteur devait trouver par lui-même cette optimisation et j'ai testé ce comportement sur Oracle afin de comparer les 2 moteurs.
L'optimisation consiste à une réécriture de la requête en jouant sur les contraintes WHERE et celles des jointures. Le résultat est biensûr identique, les mêmes tables sont en jeu.

Reproduire le cas

Pour voir les effets de l'optimisation, 3 tables suffisent. Ces 3 tables sont liées par les classiques PK/FK et la table2 est une table de liaison 0,n entre les 2 autres tables. Tous les ordres de créations sont avec le minimum de paramètre (les tables sont donc des CLUSTERED INDEX sur SQL Server et des HEAP avec B-Tree sur les PK chez Oracle).
Nous avons le schéma suivant :
Query_rewrite_shema.png Je vous donne ici les 2 scripts de mon scénario :
- Query_rewrite_MSSQL.sqlScript SQL Server
- Query_rewrite_Oracle.sqlScript Oracle

La requête

Voici la requête candidate : select t1.col1a,t2.*,t3.col3b
from myUser.table1 t1
inner join myUser.table2 t2 on t1.col1a=t2.col2a
inner join myUser.table3 t3 on t2.col2b=t3.col3b
where t3.col3b=51
OR t1.col1a=50;

La requête lit les 3 tables qui sont jointes selon les contraintes PK/FK ; tout se fait via des INNER JOIN, pas de produit cartésien. Tout est correct.

Plan d'exécution sur SQL Server


|--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[col1a], [t2].[col2b]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[col2a]))
| |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[table2].[PK_tab2] AS [t2]))
| |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[table1].[PK_tab1] AS [t1]), SEEK:([t1].[col1a]=[MyDB].[dbo].[table2].[col2a] as [t2].[col2a]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[table3].[PK_tab3] AS [t3]), SEEK:([t3].[col3b]=[MyDB].[dbo].[table2].[col2b] as [t2].[col2b]), WHERE:([MyDB].[dbo].[table3].[col3b] as [t3].[col3b]=(51) OR [MyDB].[dbo].[table1].[col1a] as [t1].[col1a]=(50)) ORDERED FORWARD)

Optimisation

A la lecture du plan d'exécution, j'ai remarqué que la 1ère opération effectuée par le moteur (MS SQL) était une lecture sur la table table3 alors que la table la plus contrainte est table2. En y regardant de plus près, ce choix n'était pas guidé par la volumétrie des 2 tables. Voici les 2 égalités qu'on peut noter sur cette requête :
- t1.col1a = t2.col2a (contrainte de jointure) et t1.col1a = 50 (contrainte du WHERE) soit : t1.col1a = t2.col2a = 50
- t2.col2b = t3.col3b (contrainte de jointure) et t3.col3b = 51 (contrainte du WHERE) soit : t2.col2b = t3.col3b = 51

En inversant les colonnes dans la clause WHERE on peut écrire :
select t1.col1a,t2.*,t3.col3b
from table1 t1
inner join table2 t2 on t1.col1a=t2.col2a
inner join table3 t3 on t2.col2b=t3.col3b
where t2.col2b=51
OR t2.col2a=50;

Dans le cas de mon client, cette réécriture a optimisé la requête par un facteur 100 puisque SQL Server a utilisé le CLUSTERED INDEX sur table2 puis les NesteadLoop vers les 2 autres tables (car peu de lignes). Dans notre cas, la réécriture divise le coût de la requête par 2 mais je change pas les accès vers les tables (trop peu de lignes dans mon jeu d'essai).

Voici le nouveau plan d'exécution

|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[col2b]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([t2].[col2a]))
| |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[table2].[PK_tab2] AS [t2]), WHERE:([MyDB].[dbo].[table2].[col2b] as [t2].[col2b]=(51) OR [MyDB].[dbo].[table2].[col2a] as [t2].[col2a]=(50)))
| |--Clustered Index Seek(OBJECT:([MyDB].[dbo].[table1].[PK_tab1] AS [t1]), SEEK:([t1].[col1a]=[MyDB].[dbo].[table2].[col2a] as [t2].[col2a]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([MyDB].[dbo].[table3].[PK_tab3] AS [t3]), SEEK:([t3].[col3b]=[MyDB].[dbo].[table2].[col2b] as [t2].[col2b]) ORDERED FORWARD)

Optimisation phase 2 :

On a vu qu'en inversant les colonnes contraintes dans le WHERE on améliore les exécutions ; nous allons maintenant voir les colonnes dans le SELECT et faire la même action.
select t2.col2a,t2.*,t2.col2b
from table1 t1
inner join table2 t2 on t1.col1a=t2.col2a
inner join table3 t3 on t2.col2b=t3.col3b
where t2.col2b=51
OR t2.col2a=50;

Vous remarquez de suite, en inversant les colonnes, le SELECT ne retourne que des infos de table2 ! Et bien entendu, je n'ai pas changé les résultats retournés pas le moteur.

Plan d'exécution

Le plan pourrait difficilement être plus simple ; on demande qu'une seule table, les contraintes ne portent que sur cette table ; SQL Server comprend que les tables table1 et table3 sont inutiles.
|--Clustered Index Scan(OBJECT:([MyDB].[dbo].[table2].[PK_tab2] AS [t2]), WHERE:([MyDB].[dbo].[table2].[col2b] as [t2].[col2b]=(51) OR [MyDB].[dbo].[table2].[col2a] as [t2].[col2a]=(50)))

Le cas sur Oracle

Sur la base Oracle (12.1), ces 2 phases d'optimisation sont directement réalisées par le moteur. J'ai exécuté le même cas de test (avec les mêmes données) et voici les opérations que l'on trouve dans la trace de l'optimiseur (10053).
------ Current SQL Statement for this session (sql_id=2tjfsbwba5sbx) ------
select t1.col1a,t2.*,t3.col3b
from myUser.table1 t1
inner join myUser.table2 t2 on t1.col1a=t2.col2a
inner join myUser.table3 t3 on t2.col2b=t3.col3b
where t3.col3b=51
OR t1.col1a=50
*************************
Join Elimination (JE)
*************************
Query block (0x7f7ada2524b8) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."COL1A" "COL1A","T2"."COL2A" "COL2A","T2"."COL2B" "COL2B","T3"."COL3B" "COL3B" FROM "MYUSER"."TABLE1" "T1","MYUSER"."TABLE2" "T2","MYUSER"."TABLE3" "T3" WHERE ("T3"."COL3B"=51 OR "T1"."COL1A"=50) AND "T2"."COL2B"="T3"."COL3B" AND "T1"."COL1A"="T2"."COL2A"
JE:   eliminate table: TABLE3 (T3)
JE:   Replaced column: T3.COL3B with column: T2.COL2B
JE:   Replaced column: T3.COL3B with column: T2.COL2B
JE:   cfro: TABLE1 objn:92078 col#:1 dfro:TABLE2 dcol#:1
JE:   cfro: TABLE1 objn:92078 col#:1 dfro:TABLE2 dcol#:1
JE:   cfro: TABLE2 objn:92074 col#:1 dfro:TABLE1 dcol#:1
JE:   cfro: TABLE2 objn:92074 col#:1 dfro:TABLE1 dcol#:1
JE:   cfro: TABLE2 objn:92074 col#:1 dfro:TABLE1 dcol#:1
JE:   cfro: TABLE2 objn:92074 col#:1 dfro:TABLE1 dcol#:1
JE:   cfro: TABLE2 objn:92074 col#:1 dfro:TABLE1 dcol#:1
JE:   cfro: TABLE2 objn:92074 col#:1 dfro:TABLE1 dcol#:1
JE:   eliminate table: TABLE1 (T1)
JE:   Replaced column: T1.COL1A with column: T2.COL2A
JE:   Replaced column: T1.COL1A with column: T2.COL2A
Registered qb: SEL$7A6F9CE2 0xda2524b8 (JOIN REMOVED FROM QUERY BLOCK SEL$9E43CB6E; SEL$9E43CB6E; "T1"@"SEL$1" SEL$9E43CB6E; "T3"@"SEL$2")
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."COL2A" "COL1A","T2"."COL2A" "COL2A","T2"."COL2B" "COL2B","T2"."COL2B" "COL3B" FROM "MYUSER"."TABLE2" "T2" WHERE "T2"."COL2B"=51 OR "T2"."COL2A"=50
Query block SEL$7A6F9CE2 (#0) simplified
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
===================================
SELECT "T2"."COL2A" "COL1A","T2"."COL2A" "COL2A","T2"."COL2B" "COL2B","T2"."COL2B" "COL3B" FROM "MYUSER"."TABLE2" "T2" WHERE "T2"."COL2B"=51 OR "T2"."COL2A"=50

On peut voir que la requête est directement "simplifiée". Vient ensuite la génération du plan d’exécution.
Le moteur considère 3 plans : lecture FULL SCAN de la table, INDEX RANGE SCAN ; INDEX FULL SCAN. Le Full Table Scan a un coût de 3 (j'ai généré très peu de données) ; l'Index Range Scan et l'Index Full Scan ont tous les deux des coûts de 2. Il semblerait que dans ce cas, Oracle choisi l'Index Full Scan.

============
Plan Table
============
------------------------------------+-----------------------------------+
| Id  | Operation         | Name    | Rows  | Bytes | Cost  | Time      |
------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |         |       |       |     2 |           |
| 1   |  INDEX FULL SCAN  | PK_TAB2 |     3 |    78 |     2 |  00:00:01 |
------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("T2"."COL2B"=51 OR "T2"."COL2A"=50)

Conclusion

Nous pouvons voir avec cet exemple que l'optimiseur Oracle arrive à trouver un meilleur plan en utilisant les règles de transitivités. Oracle identifie que les tables Table1 et Table3 ne sont pas utiles et va simplement lire la Table2 ; au contraire, l'optimiseur SQL Server prend la requête au pied de la lettre et va lire les 3 tables si elles apparaissent dans une clause du SELECT ou du WHERE.