Il y a quelques jours, on m’a présenté un cas dégénéré de jointure externe multi-colonnes à moitié cuisinée. Si vous êtes intéressé par ces singularités de développeurs, qui ne signifient rien, regardez l’excellent article de Kevin Meade intitulé Common errors seen when using OUTER-JOIN, vous y découvrirez quelques « bonnes » idées… ou pas.
Celà étant, le cas dont il s’agit est d’autant plus intéressant qu’il prévient l’élimination de partitions. Je vous propose donc dans cet article d’illustrer le cas original avant le cas dégénéré.
Jointure externe multi-colonnes à moitié cuisinée
Avant d’expliquer le cas « A moitié cuisiné », voici un graphique qui présente comment une jointure externe multi-colonne est résolue de manière graphique :
La jointure externe est dite à moitié cuisinée si certaines des colonnes de la jointure externe ne sont pas dans la clause outer join mais dans la clause where. Comparez l’exemple précédent avec cette nouvelle syntaxe et son illustration graphique :
Comme vous pouvez vous en rendre compte, cela ne ressemble pas du tout à une jointure externe.
Note:
La syntaxe non-ANSI de la jointure consiste à écrire de manière erronée :from master, detail
where master.col1=detail.col1(+)
and master.col2=detail.col2au lieu de :
from master, detail
where master.col1=detail.col1(+)
and master.col2=detail.col2(+)
Cas dégénéré … OR detail.col2 is NULL
Le cas dégénéré dont il s’agit consiste à ajouter une condition OR detail.col2 is NULL à la clause de jointure inclue dans la clause WHERE. Comme vous le voyez dans la représentation graphique ci-dessous, le résultat ne signifie pas grand chose non plus puisque la colonne master(1,2) n’a pas de correspondance alors que la colonne (3,3) en a une :
Exemple, partitions et plans
Pour aller un peu plus loin, voici un schéma d’exemple qui reprend l’exemple ci-dessus avec des tables partitionnées :
create table master
(attr1 number not null,
attr2 number not null,
text varchar2(200))
partition by range(attr2)
(partition master1 values less than (2),
partition master2 values less than (3),
partition master3 values less than (4));
insert into master
values (1,1,'X:1-Y:1');
insert into master
values (1,2,'X:1-Y:2');
insert into master
values (2,1,'X:2-Y:1');
insert into master
values (2,2,'X:2-Y:2');
insert into master
values (3,3,'X:3-Y:3');
commit;
create table detail
(id number primary key,
attr1 number not null,
attr2 number not null,
val1 number)
partition by range(attr2)
(partition detail1 values less than (2),
partition detail2 values less than (3),
partition detail3 values less than (4));
insert into detail
values (1, 1,1, 10);
insert into detail
values (2, 1,1, 20);
insert into detail
values (3, 1,1, 30);
insert into detail
values (4, 1,1, 40);
insert into detail
values (5, 2,1, 50);
insert into detail
values (6, 2,1, 60);
insert into detail
values (7, 2,1, 70);
insert into detail
values (8, 2,2, 80);
commit;
exec dbms_stats.gather_table_stats(user, 'master');
exec dbms_stats.gather_table_stats(user, 'detail');
Voici la requête dégénérée dont il s’agit ainsi que son plan :
select master.attr1,
master.attr2,
detail.val1
from master left outer join detail
on master.attr1=detail.attr1
where (master.attr2=detail.attr2 or detail.attr2 is null)
and master.attr2=2;
ATTR1 ATTR2 VAL1
---------- ---------- ----------
2 2 80
select *
from table(
dbms_xplan.display_cursor(
format=>'basic last +partition +cost +predicate'
)
);
[...]
Plan hash value: 2089997886
------------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 42 (100)| | |
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN OUTER | | 42 (3)| | |
| 3 | PARTITION RANGE SINGLE| | 14 (0)| 2 | 2 |
|* 4 | TABLE ACCESS FULL | MASTER | 14 (0)| 2 | 2 |
| 5 | PARTITION RANGE ALL | | 27 (0)| 1 | 3 |
| 6 | TABLE ACCESS FULL | DETAIL | 27 (0)| 1 | 3 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("MASTER"."ATTR2"="DETAIL"."ATTR2" OR "DETAIL"."ATTR2" IS
NULL))
2 - access("MASTER"."ATTR1"="DETAIL"."ATTR1")
4 - filter("MASTER"."ATTR2"=2)
Comme vous pouvez l’observer la requête nécessite d’interroger toutes les partitions de la table ; et pour cause, le contenu des autres partitions impacte le résultat de la requête. Pour le démontrer, il suffit de supprimer les données de la partition detail1 :
delete from detail
where attr1=1
and attr2=1;
commit;
select master.attr1,
master.attr2,
detail.val1
from master left outer join detail
on master.attr1=detail.attr1
where (master.attr2=detail.attr2 or detail.attr2 is null)
and master.attr2=2;
ATTR1 ATTR2 VAL1
----- ---------- ----------
2 2 80
1 2
Autrement dit, malgré les prédicats et les clause NOT NULL, le partitioning ne peut pas être utilisé dans ce cas, alors que dans le cas d’une jointure externe multi-colonnes, le partitioning peut effectivement servir :
select master.attr1,
master.attr2,
detail.val1
from master left outer join detail
on master.attr1=detail.attr1
and master.attr2=detail.attr2
where master.attr2=2;
ATTR1 ATTR2 VAL1
----- -- -------- ----------
2 2 80
1 2
select *
from table(
dbms_xplan.display_cursor(
format=>'basic last +partition +cost +predicate'
)
);
[...]
Plan hash value: 3122420928
-----------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 (100)| | |
|* 1 | HASH JOIN OUTER | | 29 (4)| | |
| 2 | PARTITION RANGE SINGLE| | 14 (0)| 2 | 2 |
|* 3 | TABLE ACCESS FULL | MASTER | 14 (0)| 2 | 2 |
| 4 | PARTITION RANGE SINGLE| | 14 (0)| 2 | 2 |
|* 5 | TABLE ACCESS FULL | DETAIL | 14 (0)| 2 | 2 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MASTER"."ATTR2"="DETAIL"."ATTR2" AND
"MASTER"."ATTR1"="DETAIL"."ATTR1")
3 - filter("MASTER"."ATTR2"=2)
5 - filter("DETAIL"."ATTR2"=2)
Conclusion
Dans le cas où attr2 est obtenu depuis attr1 à l’aide d’une fonction surjective, les 2 requêtes sont pourtant équivalentes ! Oracle n’est pas capable de le savoir, ni vous de l’exprimer à l’aide de contraintes. Bien sur à partir de 11g, il est possible de partitioner par référence mais il faut changer la conception de la base de données alors qu’ajouter la clé de partitioning dans la clause de jointure externe est un travail assez simple.
Pour en finir avec cet exemple, supprimez l’ensemble du schéma :
connect / as sysdba
drop user demo cascade;
