IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Clause WITH pour limiter le nombre de SELECT mais ECHEC TOTAL!


Sujet :

SQL Oracle

  1. #1
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut Clause WITH pour limiter le nombre de SELECT mais ECHEC TOTAL!
    Bonjour,

    Je veux utiliser la clause WITH pour une requête présente plusieurs fois dans mon SELECT. De la sorte j'évite qu'elle ne s'exécute N fois mais une seule pour des raisons de performances.
    Visiblement je n'ai pas tout compris sur comment fonctionne cette clause car j'ai un échec lamentable...

    Voici mes tests.

    Une sous-requête dans la clause SELECT d'un SELECT ne peux pas être réutilisée dans ce SELECT, même avec un ALIAS.
    Création d'un alias OK.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    	SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM FROM EMP;
    	NAME                                                  SALAIRE        MINIMUM
    	-------------------------------------------------- ---------- ----------
    	DUBOIS                                                  40000      40000
    	DURAND                                                  45000      40000
    	DUPONT                                                  48000      40000
    Réutilisation KO d'un alias créé dans un SELECT dans le même SELECT .
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    	SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/MINIMUM 	FROM EMP;
    	SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/MINIMUM FROM 	EMP
                  								                                                                *
    	ERROR at line 1:
    	ORA-00904: "MINIMUM": invalid identifier
    Il faut donc réutiliser l'ordre SQL associé à l'alias si je veux le bon résultat.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/(SELECT 	MIN(SALAIRE) FROM EMP) FROM EMP;
    	NAME                                                  SALAIRE    MINIMUM SALAIRE/(SELECTMIN(SALAIRE)FROMEMP)
    	-------------------------------------------------- ---------- ---------- -----------------------------------
    	DUBOIS                                                  40000      40000                                1
    	DURAND                                                  45000      40000                               1,125
    	DUPONT                                                  48000      40000                              1,2
    Le problème est que l'ordre SQL est exécuté N fois : la table EMP est lue trois 3!
    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
    	SQL> SET AUTOTRACE ON
    	SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/(SELECT 	MIN(SALAIRE) FROM EMP) FROM EMP;
    	NAME                                                  SALAIRE    MINIMUM SALAIRE/(SELECTMIN(SALAIRE)FROMEMP)
    	-------------------------------------------------- ---------- ---------- -----------------------------------
    	DUBOIS                                                  40000      40000                                1
    	DURAND                                                  45000      40000                               1,125
    	DUPONT                                                  48000      40000                              1,2
     
            Execution Plan
    	----------------------------------------------------------
    	Plan hash value: 1174980467
    	---------------------------------------------------------------------------
    	| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    	---------------------------------------------------------------------------
    	|   0 | SELECT STATEMENT   |      |     3 |   345 |     3   (0)| 00:00:01 |
    	|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
    	|   2 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
    	|   3 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
    	|   4 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
    	|   5 |  TABLE ACCESS FULL | EMP  |     3 |   345 |     3   (0)| 00:00:01 |
    	---------------------------------------------------------------------------
    Le problème est que si j'utilise la clause WITH pour afficher le même résultat, le coût passe de 3 à 6
    Je crée une vue en ligne SELECT_MINIMUM qui récupère le salaire le plus bas de tous les employés et après je réutilise cette vue dans le SELECT.
    Et c'est là où ça coince ...
    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
    SQL> WITH SELECT_MINIMUM AS (select MIN(SALAIRE) AS MINIMUM FROM EMP) select name, salaire, (SELECT MINIMUM FROM SELECT_MINIMUM), salaire/(SELECT MINIMUM FROM SELECT_MINIMUM)  from EMP;
     
    NAME                                                  SALAIRE (SELECTMINIMUMFROMSELECT_MINIMUM) SALAIRE/(SELECTMINIMUMFROMSELECT_MINIMUM)
    -------------------------------------------------- ---------- --------------------------------- -----------------------------------------
    DUBOIS                                                  40000                             40000                                 1
    DURAND                                                  45000                             40000                             1,125
    DUPONT                                                  48000                             40000                               1,2
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3834331678
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                           |     3 |   345 |     6   (0)| 00:00:01 |
    |   1 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6620_AC1496 |     1 |    13 |     2   (0)| 00:00:01 |
    |   3 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6620_AC1496 |     1 |    13 |     2   (0)| 00:00:01 |
    |   5 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
    |   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6620_AC1496 |       |       |            |          |
    |   7 |    SORT AGGREGATE          |                           |     1 |    13 |            |          |
    |   8 |     TABLE ACCESS FULL      | EMP                       |     3 |    39 |     3   (0)| 00:00:01 |
    |   9 |   TABLE ACCESS FULL        | EMP                       |     3 |   345 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------
    Si vous pouvez m'aider à comprendre ce qui se passe, par avance merci
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    Le WITH a matérialisé la requête, étape : LOAD AS SELECT | SYS_TEMP_0FD9D6620_AC1496

    Puis c'est cette table temporaire qui est accédée, comme vous codez plusieurs accès, vous avez plusieurs accès à cette table temporaire, mais vous pouvez coder comme ça :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    WITH SELECT_MINIMUM AS (
    select MIN(SALAIRE) AS MINIMUM 
      FROM EMP
    ) 
    select name, salaire, sm.MINIMUM, salaire/sm.MINIMUM
      from EMP
     cross join SELECT_MINIMUM sm
    A noter que le WITH ne materialise pas forcément la requête dans une table temporaire.

    Par ailleurs la comparaison du coût de 2 requêtes syntaxiquement différentes n'est pas très pertinente.
    Le coût permet plutôt de comprendre pourquoi l'optimiseur a privilégié un accès plutôt qu'un autre.

    https://asktom.oracle.com/pls/asktom...D:313416745628

  3. #3
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    Par curiosité, as-tu essayer avec les fonctions de regroupement fenêtrées ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select  name
        ,   salaire
        ,   min(salaire) over() as MINIMUM
        ,   salaire / min(salaire) over()
    from    EMP
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  4. #4
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Merci pour les réponses très rapide :-)
    Je ferai mes tests ce week-end.

    [EDIT]
    Merci pour le conseil al1 24 mais je voulais surtout voir, avec la clause WITH, comment on peut diminuer le coût d'une opération appelée N fois.

    Bon, voici le résultat de mes tests et on voit bien qu'utiliser une vue en ligne réduit, dans mon cas, le nombre de lignes utilisées et évite des SORT.
    Avec la clause WITH, Oracle crée une vue et ensuite, en y accédant, lit une seule donnée à chaque fois et fait un seul SORT.
    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
    SQL> WITH SELECT_MINIMUM AS (select MIN(SALAIRE) AS MINIMUM FROM EMP) select name, salaire, 	(SELECT MINIMUM FROM SELECT_MINIMUM) AS MINI01, salaire/(SELECT MINIMUM FROM 	SELECT_MINIMUM) AS MINI02,  salaire*(SELECT MINIMUM FROM SELECT_MINIMUM) AS MINI03 from 	EMP;
    
    NAME                                                  SALAIRE     MINI01     MINI02     MINI03
    -------------------------------------------------- ---------- ---------- ---------- ----------
    DUBOIS                                                  40000      40000          1 1600000000
    DURAND                                                  45000      40000      1,125 1800000000
    DUPONT                                                  48000      40000        1,2 1920000000
    
    	Execution Plan
    	----------------------------------------------------------
    	Plan hash value: 3276894477
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT           |                           |     3 |    33 |     6   (0)| 00:00:01 |
    |   1 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6627_AC1496 |     1 |     4 |     2   (0)| 00:00:01 |
    |   3 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6627_AC1496 |     1 |     4 |     2   (0)| 00:00:01 |
    |   5 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
    |   6 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6627_AC1496 |     1 |     4 |     2   (0)| 00:00:01 |
    |   7 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
    |   8 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6627_AC1496 |       |       |            |          |
    |   9 |    SORT AGGREGATE          |                           |     1 |     4 |            |          |
    |  10 |     TABLE ACCESS FULL      | EMP                       |     3 |    12 |     3   (0)| 00:00:01 |
    |  11 |   TABLE ACCESS FULL        | EMP                       |     3 |    33 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------

    En revanche, sans clause WITH, les N lignes sont lues à chaque fois et, à chaque fois, un SORT est fait, d'où des problèmes de performances.
    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
    SQL> select name, salaire,      (select MIN(SALAIRE) FROM EMP) AS MINI01, salaire/(select MIN(SALAIRE) FROM EMP) AS MINI02,  salaire*(select MIN(SALAIRE) FROM EMP) AS MINI03 from      EMP;
    NAME                                                  SALAIRE     MINI01     MINI02     MINI03
    -------------------------------------------------- ---------- ---------- ---------- ----------
    DUBOIS                                                  40000      40000          1 1600000000
    DURAND                                                  45000      40000      1,125 1800000000
    DUPONT                                                  48000      40000        1,2 1920000000
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3137437906
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     3 |    33 |     3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
    |   2 |   TABLE ACCESS FULL| EMP  |     3 |    12 |     3   (0)| 00:00:01 |
    |   3 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
    |   4 |   TABLE ACCESS FULL| EMP  |     3 |    12 |     3   (0)| 00:00:01 |
    |   5 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
    |   6 |   TABLE ACCESS FULL| EMP  |     3 |    12 |     3   (0)| 00:00:01 |
    |   7 |  TABLE ACCESS FULL | EMP  |     3 |    33 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  5. #5
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Bonsoir,

    Question subsidiaire, pourquoi utiliser une table dérivée dans votre première requête plutôt que directement min(salaire) ?

  6. #6
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Citation Envoyé par escartefigue Voir le message
    Bonsoir,

    Question subsidiaire, pourquoi utiliser une table dérivée dans votre première requête plutôt que directement min(salaire) ?
    Je voulais afficher les enregistrements détails de la table des employés donc impossible de faire un MIN(SALAIRE) car c'est une fonction de regroupement.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM FROM EMP;
     
    NAME                                                  SALAIRE    MINIMUM
    -------------------------------------------------- ---------- ----------
                                                            40000      40000
    DURAND                                                  45000      40000
    DUPONT                                                  48000      40000
     
    SQL> SELECT NAME, SALAIRE, MIN(SALAIRE)  FROM EMP;
    SELECT NAME, SALAIRE, MIN(SALAIRE)  FROM EMP
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  7. #7
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Vous voulez afficher le détail et le regroupement => fonction analytique. Rien ne peut battre ça.

  8. #8
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Je suis d'accord mnitu, mais je voulais utiliser la clause WITH pour voir sur quoi je gagne.
    Et puis la syntaxe que j'ai utilisée est plus facile à faire comprendre à des débutants que celle des fonctions analytiques, même si c'est une partie du SQL très intéressant.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

Discussions similaires

  1. Réponses: 7
    Dernier message: 21/01/2011, 16h56
  2. Tags pour limiter le nombre de caractères
    Par DjChat dans le forum Django
    Réponses: 2
    Dernier message: 20/09/2010, 16h50
  3. [AC-2003] Comment limiter le nombre de selections d'une ListeBox ?
    Par maringot dans le forum IHM
    Réponses: 2
    Dernier message: 15/03/2010, 17h01
  4. Contrainte pour limiter le nombre de lignes
    Par kanebody dans le forum Développement
    Réponses: 1
    Dernier message: 13/02/2009, 10h20
  5. Fonction pour limiter le nombre de checkboxes cochees
    Par nabab dans le forum Général JavaScript
    Réponses: 1
    Dernier message: 25/07/2007, 21h24

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo