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.