Le produit cartésien n'est pas le MAL mais quand peut-on l'utiliser? - The Cartesian product is not EVIL but when can we use it?
Introduction
On vous a dit, re-dit et re-re-dit que le produit cartésien (ou produit en croix) c'était le mal, que c'était à éviter à tout prix car cette absence de jointure retourne des enregistrements en trop et que ça monopolisait trop de ressources serveur, au point de parfois le mettre à genoux.
OK, mais dans quel cas peut-on faire un produit cartésien sachant que ce n'est pas interdit en SQL et qu'il y a même un opérateur dédié à cette opération, appelé CROSS JOIN?
C'est le but de cet article!
Points d'attention
N'utilisez le produit cartésien qu'en toute connaissance de cause, en sachant que le résultat peut être sans commune mesure avec la volumétrie des tables jointes et que vos SELECT peuvent mettre des heures pour s'exécuter et donc faire tomber votre serveur.
Base de tests
N'importe quelle base Oracle.
Exemples
============================================================================================
Comment faire un produit cartésien.
============================================================================================
Pour rappels :
- le produit cartésien est l'absence de jointure entre deux tables, que ce soit volontaire ou, le plus souvent, un oubli
- le résultat du produit cartésien est la combinatoire entre les deux tables, soit pour deux tables ayant N et M enregistrements, le résultat sera N*M enregistrements alors qu'une équijointure (jointure avec le signe = ) aurait pu ramener 1 000 fois moins d'enregistrements dans un cas classique
Jeu d'essai
On utilise deux tables avec deux enregistrements chacune pour éviter les problèmes de volumétrie.
Notez que les noms des colonnes sont différents entre les deux tables.
SQL> select * from emp;
NOM PRENOM AGE
------------------------------ ------------------------------ ----------
DUPONT TOM 40
DURAND TOM 50
SQL> select * from clients;
NOM_CLIENT PRENOM_CLIENT AGE_CLIENT
------------------------------ ------------------------------ -------------------------
DUPONT TOM 40
DUPONT DAVID 40
Syntaxe propriétaire Oracle
Dans le cas de la syntaxe classique Oracle, le produit cartésien a lieu en ne faisant aucune jointure entre les tables du FROM.
Si on fait le produit cartésien, voilà le résultat : toute la combinatoire possible et imaginable, soit 2 x 2 = 4 enregistrements dans notre exemple dont 3 mauvais.
SQL> select *
from emp, clients;
NOM PRENOM AGE NOM_CLIENT PRENOM_CLIENT AGE_CLIENT
------------------------------ ------------------------------ ---------- -------------------------
DUPONT TOM 40 DUPONT TOM 40
DUPONT TOM 40 DUPONT DAVID 40
DURAND TOM 50 DUPONT TOM 40
DURAND TOM 50 DUPONT DAVID 40
Si on avait fait la jointure : 1 seul enregistrement au lieu de 4 mais c'est le bon!
SQL> select *
from emp e, clients c
where e.nom = c.nom_client and e.prenom = c.prenom_client;
NOM PRENOM AGE NOM_CLIENT PRENOM_CLIENT AGE_CLIENT
------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------
DUPONT TOM 40 DUPONT TOM 40
Syntaxe ANSI92
Dans le cas de la syntaxe ANSI92, le produit cartésien se fait avec le mot clé CROSS JOIN ou bien, attention c'est un piège de cette syntaxe, avec NATURAL JOIN dans le cas où aucune colonne entre les tables jointes n'a de nom en commun.
SQL> select * from emp CROSS JOIN clients;
NOM PRENOM AGE NOM_CLIENT PRENOM_CLIENT AGE_CLIENT
------------------------------ ------------------------------ ---------- ----------------------
DUPONT TOM 40 DUPONT TOM 40
DUPONT TOM 40 DUPONT DAVID 40
DURAND TOM 50 DUPONT TOM 40
DURAND TOM 50 DUPONT DAVID 40
SQL> select * from emp NATURAL JOIN clients;
NOM PRENOM AGE NOM_CLIENT PRENOM_CLIENT AGE_CLIENT
------------------------------ ------------------------------ ---------- ----------------------
DUPONT TOM 40 DUPONT TOM 40
DUPONT TOM 40 DUPONT DAVID 40
DURAND TOM 50 DUPONT TOM 40
DURAND TOM 50 DUPONT DAVID 40
============================================================================================
Pourquoi faire un produit cartésien.
============================================================================================
Pour répondre à certains besoins, il est possible, voir obligatoire, de faire un produit cartésien.
Tests de volumétrie avec des données réelles dans les tables de tests
Quand on fait des tests de volumétrie, il faut disposer de tables avec des données en quantité suffisamment importante. Il existe diverses méthodes pour remplir des tables mais une des plus intéressante est de faire un produit cartésien. Prenons comme exemple DBA_OBJECTS, une vue qui renferme près de 80 000 enregistrements dans une nouvelle base Oracle 12. Faire un produit cartésien dessus signifie créer 80000 * 80000 soit 6 400 000 000 d'enregistrements... oui, 6 milliards et demi!
OK, c'est trop mais on pourra toujours faire une sélection sur le type des objets ou utiliser rownum pour filtrer et avoir des tables avec quelques millions de rows. L'autre gros intérêt à faire un produit en croix depuis des tables système existantes, c'est l'assurance d'avoir des données dont les valeurs ne sont pas bidon et qui permettront ensuite de faire des tests pertinents. Entre générer deux millions de noms composés d'un libellé fixe comme AA concaténé à une séquence ou utiliser DBMS_RANDOM, je préfère de loin utiliser les données de DBA_OBJECTS car elles ont un sens.
SQL> select count(*) from dba_objects;
COUNT(*)
----------
78398
SQL> select 80000 * 80000 from dual;
80000*80000
-----------
6400000000
On vérifie qu'on a bien 6 milliards et demi d'enregistrements avec ce cross join.
SQL> select count(*)
from dba_objects o1, dba_objects o2;
COUNT(*)
----------
6146246404
En utilisant des filtres, nous avons presque 34 millions d'enregistrements, ce qui est plus gérable pour Oracle.
SQL> select count(*)
from dba_objects o1, dba_objects o2
where o1.owner <> 'SYS' and o2.owner <> 'SYS' and o1.object_type = 'TABLE';
COUNT(*)
----------
33557060
On peut donc utiliser ce filtre pour créer une table avec des données nombreuses et faisant sens.
SQL> create table TEST_OBJECTS AS
SELECT o1.OWNER, o1.object_name, o2.object_type, o2.status
from dba_objects o1, dba_objects o2
where o1.owner <> 'SYS' and o2.owner <> 'SYS' and o1.object_type = 'TABLE';
Afficher toute la combinatoire possible.
Il est parfois nécessaire d'afficher tous les enregistrements possibles et imaginables entre deux tables. Dans ce cas là, la solution est le produit cartésien.
Par exemple une entreprise a 10 employés et 100 clients réguliers qu'elle a fréquemment au téléphone. Elle décide d'affecter certains employés à certains clients, plutôt de la même classe d'âge car ils partagent le même vocabulaire, les mêmes valeurs... MAIS cela n'est pas une obligation, la RH se réserve le droit de réfléchir et d'affecter telle employé à tel client, ce qui fait qu'il y a un élément humain de décision dans le process, chose qui ne peut pas se traduire par un filtre SQL. Par classe d'âge on entends plus ou moins 10 ans par exemple. On fera donc un produit cartésien pour afficher tous les cas possibles puis ensuite la RH va affecter manuellement tel employé à tels clients. Nous aurons 1000 enregistrements pour voir tous les cas possibles.
Pour que ce test soit plus lisible, j'ai ajouté des employés et des clients par rapport aux tests précédents.
SQL> select e.nom || ' ' || e.prenom || ' ' || TO_CHAR(e.age) AS "EMPLOYE",
c.nom_client || ' ' || c.prenom_client || ' ' || TO_CHAR(c.age_client) AS "CLIENT",
e.age - c.AGE_CLIENT AS "DIFFERENCE AGE"
from emp e, clients c
order by "EMPLOYE", "DIFFERENCE AGE";
EMPLOYE CLIENT DIFFERENCE AGE
------------------------------ ------------------------------ ------------------
DURAND TOM 45 MIMON DELPHINE 70 -25
DURAND TOM 45 VRAMO DAVID 40 5
DURAND TOM 45 PAPIN MARCEL 40 5
DURAND TOM 45 MARTIN SOPHIE 25 20
MARCIAL PAUL 40 MIMON DELPHINE 70 -30
MARCIAL PAUL 40 VRAMO DAVID 40 0
MARCIAL PAUL 40 PAPIN MARCEL 40 0
MARCIAL PAUL 40 MARTIN SOPHIE 25 15
8 rows selected.
Tester le gestionnaire de ressources.
Je tiens à remercier le DBA de pseudo Pomalaix qui sur le lien suivant explique qu'on peut empêcher une requête trop longue de s'exécuter via des profils ou le Resource Manager. Avec des profils, cette requête va certes commencer à s'exécuter mais elle va s'interrompre dès qu'on aura dépassé un seuil de ressources alloué au user. Avec le gestionnaire de ressources, c'est carrément le lancement de la requête qui n'aura pas lieu si celle-ci devait consommer trop de ressources.
https://www.developpez.net/forums/d1601098/bases-donnees/oracle/administration/produit-croix-requete-lente-blocage-oracle/
Et où intervient le produit cartésien là-dedans? Dans la création des requêtes pour tester le profil ou le plan de gestion des ressources :-) Si on ne veut pas qu'une requête de plus de 15 minutes puisse s'exécuter, il faut tester avec une requête mettant au minimum ce temps : un produit cartésien entre dba_objects et dba_tab_cols sera parfait pour cela.
Pomalaix nous donne même un exemple.
"Par exemple, en jouant sur une directive avec une limite MAX_EST_EXEC_TIME fixée à 10 secondes de temps CPU :
select count(*) from dba_objects, dba_users;
ERREUR à la ligne 1 :
ORA-07455: temps d'exécution prévu (105 sec.) hors limites (10 sec.)"
Je ne décrirai pas ici comment tester cette partie car créer un plan de ressource n'est pas simple et c'est hors périmètre de cet article.
Afficher sur une seule ligne le contenu de deux tables avec ou sans lien.
Ce cas là est plus subtil :-)
Nous pouvons également utiliser le produit cartésien lorsque nous voulons afficher sur une seule ligne des informations venant de deux tables ayant ou non des colonnes en commun MAIS OBLIGATOIREMENT si ces tables n'ont qu'un seul enregistrement!
Vous avez deviné pourquoi? Parce qu'un produit cartésien crée un résultat de Table1.Nb_rows * Table2.Nb_rows enregistrements et, quand on a 1 des deux côtés, le résultat est 1 * 1 = 1, donc on ne peut pas avoir d'enregistrement parasite dans ce cas!
Il est possible d'obtenir le même résultat avec un autre ordre SQL mais je veux montrer que le produit cartésien est ici possible.
Premier exemple : jointure entre deux tables sans colonne en commun et ayant chacune un seul enregistrement : V$INSTANCE et USER_USERS.
SQL>select COLUMN_NAME
from dba_tab_cols
where table_name = 'V_$INSTANCE'
INTERSECT
select COLUMN_NAME
from dba_tab_cols
where table_name = 'USER_USERS';
no rows selected
Le test est simple : j'affiche en une ligne des infos sur ma base et sur le nom de mon user.
SQL> select d.dbid, d.name, d.created, u.username from v$database d, user_users u;
DBID NAME CREATED USERNAME
---------- --------- --------- ------------------------------------------------
768045447 ORCL12C 02-MAR-17 HR
Deuxième exemple : jointure entre deux tables avec une ou des colonnes en commun mais ayant à nouveau chacune un seul enregistrement : V$INSTANCE et V$DATABASE.
SQL>select COLUMN_NAME
from dba_tab_cols
where table_name = 'V_$INSTANCE'
INTERSECT
select COLUMN_NAME
from dba_tab_cols
where table_name = 'V_$DATABASE';
COLUMN_NAME
-------------------------------------------
CON_ID
On va comparer le résultat sans et avec jointure sur la colonne commune : même résultat, comme je m'y attendais!
Select sans jointure.
SQL> select d.dbid, d.name, d.created, i.instance_name, i.status
from v$database d, v$instance i;
DBID NAME CREATED INSTANCE_NAME STATUS
---------- --------- --------- ---------------- ---------------------
768045447 ORCL12C 02-MAR-17 orcl12c OPEN
Select avec jointure.
SQL> select d.dbid, d.name, d.created, i.instance_name, i.status
from v$database d, v$instance i
where d.con_id = i.con_id;
DBID NAME CREATED INSTANCE_NAME STATUS
---------- --------- --------- ---------------- ----------------------
768045447 ORCL12C 02-MAR-17 orcl12c OPEN
Voilà, comme je vous le disais, le produit cartésien peut parfois grandement vous simplifier la vie.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)