Discussion :

[Michel38]

Bonjour à tous,

J'ai une requête un peu mortelle à faire, et je crois que je n'en ai jamais écrite des comme cela. Je précise que c'est destiné à être lancé une ou deux fois par an pour faire une extraction CSV.
J'explique.
Les premières colonnes (environ 70) sont des colonnes issues d'une table principale (liste d'utilisateurs) et il y a 3 jointures dessus, pour récupérer des infos. Rien de bien méchant jusque là.
Mais c'est là que ça se complique. La requête me ramène environ 75 000 lignes (détail important).

Pour chaque ligne, je dois vérifier si chaque utilisateur possède, ou ne possède pas un logiciel. S'il le possède, je dois mettre 1, sinon 0.
Pour cela, j'ai créé une requête que j'ai déclaré du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
With REQUETE_B AS (
SELECT col1, col2, col3... FROM table1
LEFT JOIN table2 ON (......)
LEFT JOIN table3 ON (.....)
LEFT JOIN table4 ON (......)
LEFT JOIN table5 ON (.....)
LEFT JOIN table6 ON (......)
LEFT JOIN table7 ON (.....)
)

Et donc, à la suite des 70 champs de la première requête, je mets une requête du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
(
SELECT COUNT(1) FROM REQUETE_B b
JOIN tableN N ON (.......)
WHERE condition pour logiciel A
) AS "col71",

Et puisque j'ai 56 logiciel, et bien ça fait 56 nouvelles colonnes ajoutées à la toute première requête.

En comptant rapidement, étant donné que j'ai environ 75 000 lignes, ça fait 56 x 75 000 = 4 200 000 requêtes lancées.

C'est assez énorme. Et avec déjà 1 ou 2 logiciel c'est très très long pour obtenir le résultat.

On m'a parlé de requête croisée. Mais je ne sais pas du tout ce que c'est.

Quelqu'un aurait une idée qui me permettrait d'optimiser tout ça ?

Merci

Michel

[escartefigue]

Pouvez vous communiquer le script DDL de création des tables en jeu incluant les contraintes FK pour savoir comment joindre ces tables.
Plutôt que de repartir d'une requête toute faite, il est souvent préférable de repartir du début

Mais si il y a une table par logiciel, ce qui semble pour le moins curieux et plus probablement très mal modélisé, la requête sera de toutes façon alambiquée...

[Michel38]

Merci de votre réponse.
La table utilisateursA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CN
PERSONALTITLE
SN
MIDDLENAME
GIVENNAME
STATUT
SEXE
DISPLAYNAME
COMPANY
WORKINGCOMPANY
CONTRACTBEGINDATE
CONTRACTENDDATE
EMPLOYEETYPE
MAD
...
...
...
...

La requête WITH req AS :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
WITH compte AS (SELECT DISTINCT u.id_utilisateur, S.id_logiciel FROM historique_habilitations HH
LEFT JOIN UTILISATEURSB U ON (U.id_utilisateur=hh.id_utilisateur)
left join CONTACTS C ON (c.id_contact=u.id_contact) 
left join ENTITES E ON (E.ID_ENTITE=c.id_entite)
left join CLIENTS CL ON (CL.ID_CLIENT=e.id_client) 
left join PROFILS_APP PA ON (PA.ID_PROFIL_APP=hh.id_profil_app)
left join logiciel S ON (S.ID_logiciel = PA.ID_logiciel)
)

Il u a deux tables utilisateurs

la table utilisateursB :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
ID_CONTACTSTATUT
DATE_CREATION
ID_UTILISATEUR_MODIFICATION
ID_UTILISATEUR
COMPTE
ID_SERVICE_IAM
IDENTIFIANT_SECONDAIRE

la table contacts :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ID_CONTACTNOM
PRENOM
EMAIL
TELEPHONE_FIXE
TELEPHONE_PORTABLE
OPT_IN_EMAIL
OPT_IN_SMS
OPT_IN_PORTAIL
ID_ENTITE
ADRESSE
CODE_POSTAL
VILLE
...
...
...

la table entites :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
ID_ENTITEID_CLIENT
ID_ENTITE_IAM
NOM
LOGO
STATUT
ID_UTILISATEUR_MODIFICATION
ID_RCS
STATUT_RCS
DATE_MODIF_RCS
 
FACTURABLE

la table clients :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
ID_CLIENTREFERENCE_RFF
ID_CATEGORIE_CLIENTS
NOM
ADRESSE
CODE_POSTAL
VILLE
PAYS
...
...
...

La table profils_app :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
ID_PROFIL_APPPROFIL_APP
ID_LOGICIEL
ID_LOGICIEL_IAM
LIBELLE
STATUT
FACTURABLE
COMMENTAIRE
SOURCE

la table logiciel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
ID_LOGICIELLIBELLE
DESCRIPTION
STATUT
ID_UTILISATEUR_MODIFICATION
LOGICIEL_EXTERNE_INTERNE
AUTH_RENFORCEE
CODE_IAM
DATE_MODIFICATION
...
...
...
...

La sous requête lancée autant de fois qu'il y a d'utilisateurs et de logiciel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
(SELECT COUNT(1) FROM req c
JOIN utilisateursB u ON (c.id_utilisateur=u.id_utilisateur)
WHERE u.compte=RU.CN AND c.id_logiciel='LOGICIEL A'
) AS "LOGICIEL A"

L'alias RU correpond à la table utilisateursA

[vanagreg]

Bonjour,

Il faut que tu émules tes 56 lignes de logiciels avec un cte "with logiciels as (select 'logiciel1' from dual union all select 'logiciel2' from dual union all...)" et que tu pivotes les lignes avec un count pour avoir le résultat en colonnes.

[Michel38]

Je ne vois pas comment faire, mais je vais essayer.
Merci

Michel

[Michel38]

Bonjour,

Bon, et bien finalement je ne vois pas comment faire.
Si quelqu'un a une idée ou un exemple, je suis preneur

Merci

Michel

[vanagreg]

Bonjour,

Voilà un exemple de ce dont je parlais. J'ai limité à 4 utilisateurs et à 6 logiciels pour l'exemple, mais tu devras mettre ta liste complète (dans les valeurs IN du pivot également):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
with req(id_utilisateur, id_logiciel) 
as (select 1, 'LOGICIEL A' from dual union all
    select 2, 'LOGICIEL A' from dual union all
    select 2, 'LOGICIEL B' from dual union all
    select 3, 'LOGICIEL C' from dual union all
    select 4, 'LOGICIEL B' from dual union all
    select 4, 'LOGICIEL D' from dual
   ),
liste_logiciels(id_logiciel)
as (select 'LOGICIEL A' from dual union all
    select 'LOGICIEL B' from dual union all
    select 'LOGICIEL C' from dual union all
    select 'LOGICIEL D' from dual union all
    select 'LOGICIEL E' from dual union all
    select 'LOGICIEL F' from dual 
   ),
logiciels_utilisateurs 
as (select r.id_utilisateur, nvl2(r.id_logiciel, 1, 0) logiciel_present, l.id_logiciel
    from req r
    partition by (r.id_utilisateur)
    right join liste_logiciels l
    on (r.id_logiciel = l.id_logiciel)
   )
select *
from logiciels_utilisateurs  
pivot (sum(logiciel_present) for id_logiciel in ('LOGICIEL A' as logiciel_A,
                                                 'LOGICIEL B' as logiciel_B,
                                                 'LOGICIEL C' as logiciel_C,
                                                 'LOGICIEL D' as logiciel_D,
                                                 'LOGICIEL E' as logiciel_E,
                                                 'LOGICIEL F' as logiciel_F
                                                )
      ) 
order by 1;   
 
ID_UTILISATEUR LOGICIEL_A LOGICIEL_B LOGICIEL_C LOGICIEL_D LOGICIEL_E LOGICIEL_F
-------------- ---------- ---------- ---------- ---------- ---------- ----------
             1          1          0          0          0          0          0
             2          1          1          0          0          0          0
             3          0          0          1          0          0          0
             4          0          1          0          1          0          0
 
4 rows selected.

[Michel38]

Ah c'est génial. Je vais essayer comme ça.
Merci beaucoup.

Michel