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

Oracle Discussion :

Utilisation de minus: optimisation de la requête


Sujet :

Oracle

  1. #1
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut Utilisation de minus: optimisation de la requête
    Bonjour,

    j'ai une requête qui dure 1h30 environ et j'aimerais pouvoir réduire ce temps.
    J'utilise MINUS et je me demandais si ce n'était pas du à cela.
    Je ne m'y connais pas trop en optimisation.
    Si quelqu'un pouvait m'aider, ce serait très sympa.
    Voici ma requête:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    select count(colonne1) 
    from(select colonne2 
    from table1, table2, table3 
    where valeur1<>'333'and valeur2=' 'and valeur3like'x%'and valeur4='Y' 
    and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
    minusselect colonne5 from table4)
    
    il y a un index sur les identifiant des table 1 2 et 3.
    Merci de votre aide...

  2. #2
    Membre Expert Avatar de nuke_y
    Profil pro
    Indépendant en analyse de données
    Inscrit en
    Mai 2004
    Messages
    2 076
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Indépendant en analyse de données

    Informations forums :
    Inscription : Mai 2004
    Messages : 2 076
    Par défaut
    Est-ce qu'il y a un index sur colonne5 de la table 4 ?

    Et que donne ceci ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     select count(1)from (
    (
    select colonne2 
    from table1, table2, table3, table4
    where valeur1<>'333'and valeur2=' 'and valeur3like'x%'and valeur4='Y' 
    and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
    and colonne2=colonne5(+)
    and colonne5 is null
    )
    )
    
    Car si j'ai bien compris ta requête, tu souhaites avoir une jointure entre Table1, Table 2 et Table 3 et retirer de ce résultat toutes les lignes qui ont un colonne2 présent dans le champ Colonne5 de Table4 c'est ça ?

  3. #3
    Membre émérite Avatar de plabrevo
    Profil pro
    Inscrit en
    Décembre 2005
    Messages
    548
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2005
    Messages : 548
    Par défaut
    Il est assez vain en general de faire des travaux d'optimisation de requete sans disposer de
    1. la volumetrie
    2. de la liste des indexes et de leur selectivite.
    3. accessoirement, de l'explain plan.
    En ce qui concerne le minus, il est probable que le temps de response sans l'operation MINUS doive approcher les 1h29 minutes.

  4. #4
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    Et pourquoi pas simplement :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     select count(colonne2)
    from table1, table2, table3 , table4
    where valeur1<>'333'and valeur2=' 'and valeur3like'x%'and valeur4='Y' 
    and idtable1 = idtable2 and idtable2 = idtable3 
    and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
    and colonne2 <> colonne5

  5. #5
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Merci pour tous vos conceils.
    je vais essayer ce que vous m'avez dit et je vous tiens au courant.

  6. #6
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    J'ai bien un index sur ma colonne5 de ma table 4.
    Pour ce qui est des requêtes, je les ai essayé toutes les 2 mais elle ne fonctionne pas.
    Merci quand même de votre aide.

  7. #7
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    qu'est ce qui ne fonctionne pas ?

    Essaye ça sinon :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    select count(colonne1) 
    from(select colonne2 
    from table1, table2, table3 
    where valeur1<>'333'and valeur2=' 'and valeur3 like'x%'and valeur4='Y' 
    and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
    and 
    colonne2 NOT IN (select colonne5 from table4))



    Avec un index sur colonne 2

    Par ailleurs tu fais count(colonne1) alors que c'est colonne2 que tu sélectionnes... je vois mal comment ça pourrait fonctionner

  8. #8
    Membre Expert Avatar de nuke_y
    Profil pro
    Indépendant en analyse de données
    Inscrit en
    Mai 2004
    Messages
    2 076
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Indépendant en analyse de données

    Informations forums :
    Inscription : Mai 2004
    Messages : 2 076
    Par défaut
    Euh attends, elles ne fonctionnent pas, c'est à dire ? Il se passe quoi ?

    Fred_D je ne suis pas d'accord avec ta PREMIERE requête. Pour moi le but c'est de retirer à la jointure les lignes pour lesquelles la valeur de colonne2 existe dans colonne5 de table4.

    Donc :
    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
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    SQL> create table test1( ID number);
     
    Table crÚÚe.
     
    SQL> insert into test1 values(1);
     
    1 ligne crÚÚe.
     
    SQL> insert into test1 values(2);
     
    1 ligne crÚÚe.
     
    SQL> insert into test1 values(3);
     
    1 ligne crÚÚe.
     
    SQL> insert into test1 values(4);
     
    1 ligne crÚÚe.
     
    SQL> insert into test1 values(5);
     
    1 ligne crÚÚe.
     
     
    SQL> create table test2( ID number);
     
    Table crÚÚe.
     
    SQL> insert into test2 values(3);
     
    1 ligne crÚÚe.
     
    SQL> insert into test2 values(5);
     
    1 ligne crÚÚe.
     
     
    SQL> select t1.* from test1 t1, test2 t2 where t1.id<>t2.id;
     
            ID
    ----------
             1
             2
             4
             5
             1
             2
             3
             4
     
    8 ligne(s) sÚlectionnÚe(s).
     
    SQL> select t1.* from test1 t1 where not exists (select 1 from test2 t2
    where t2.id=t1.id);
     
            ID
    ----------
             1
             2
             4
     
    SQL> select t1.* from test1 t1, test2 t2 where t1.id=t2.id(+) and t2.id
    is null;
     
            ID
    ----------
             1
             2
             4

  9. #9
    Membre Expert Avatar de nuke_y
    Profil pro
    Indépendant en analyse de données
    Inscrit en
    Mai 2004
    Messages
    2 076
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Indépendant en analyse de données

    Informations forums :
    Inscription : Mai 2004
    Messages : 2 076
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SQL> select t1.* from dwh.test1 t1 where t1.id not IN (Select t2.id from dwh.test2 t2);
     
            ID
    ----------
             1
             2
             4
     
    ---
    Ok avec ta DEUXIEME requête. On gagne en perf avec le NOT IN plutôt que le EXISTS ? J'imagine que oui si le Sous-Select ramène peu de lignes.

  10. #10
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Citation Envoyé par Fred_D
    qu'est ce qui ne fonctionne pas ?
    je n'obtiens pas les mêmes résultats.
    je vais essayer de voir pourquoi.
    Sinon j'ai un index sur ma colonne 2 et en effet ma colonne 1 et 2 sont les mêmes. Je me suis trompée en écrivant .

    Sinon pour le not in, j'ai essayé mais ma reqûete est aussi longue.

  11. #11
    Membre Expert Avatar de nuke_y
    Profil pro
    Indépendant en analyse de données
    Inscrit en
    Mai 2004
    Messages
    2 076
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Indépendant en analyse de données

    Informations forums :
    Inscription : Mai 2004
    Messages : 2 076
    Par défaut
    Est-ce que tu peux nous confirmer que ton but est bien de soustraire à ta liste les lignes pour lesquelles la valeur de colonne2 existe dans colonne4 ?

  12. #12
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Citation Envoyé par nuke_y
    Est-ce que tu peux nous confirmer que ton but est bien de soustraire à ta liste les lignes pour lesquelles la valeur de colonne2 existe dans colonne4 ?
    Oui c'est ce que je veux faire. et J'aimerais avoir une requête la moins longue possible.

  13. #13
    Membre Expert Avatar de nuke_y
    Profil pro
    Indépendant en analyse de données
    Inscrit en
    Mai 2004
    Messages
    2 076
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Indépendant en analyse de données

    Informations forums :
    Inscription : Mai 2004
    Messages : 2 076
    Par défaut
    Et combien de temps te prend cette requête ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     select count(colonne2) 
    from(select colonne2 
    from table1, table2, table3 
    where valeur1<>'333'and valeur2=' 'and valeur3 like'x%'and valeur4='Y' 
    and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' )
     
    --
    Pour être sûr que ce n'est pas la requête principale qu'il faut améliorer.

  14. #14
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Elle dure 10 secondes avec le minus et 7 secondes sans le minus.

    Mais sachant que cette requête, je l'effectue pour 301 valeur et pour chacune de ces valeurs, je l'a fait 3 fois avec un minus différent.
    Donc ça fait très long.
    Je ne sais pas si je peux faire plus court.

  15. #15
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    dans ce cas, il faut se pencher sur le GROUP BY pour éviter de la lancer 300 fois

  16. #16
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Mais je ne vois pas le faire avec un GROUP BY.
    J'ai créé une procédure qui fait ces requêtes pour les différentes valeurs et l'insère dans une table.
    je voudrais optimiser cette requête pour la rendre plus rapide.
    Voici ma procédure en entier:
    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
    45
    46
    47
    48
    49
    CREATEORREPLACEPROCEDURE remplir_table IS
     
    code_action varchar2(20);
    libelle_action varchar2(30); 
    le_sql VARCHAR2(5000);
    nom_extract varchar2(30);
    nombre number;
    BEGIN
    executeimmediate'TRUNCATE TABLE COUNT;
    FOR c IN(select decode(length(valeur),1,'00',2,'0')||to_char(valeur) mag fromtable_mag)
    LOOP
    
     
    BEGIN
    select code, libelle into code_action, libelle_action from table_code wherecocod='S';
    select colonne5 into nom from table5where exnum ='REFUSS';
    le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and valeur3 like ''x%''and valeur4=''Y'' and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(val)-2)= :1 minus select valeur5 from '||nom||')';
    executeimmediate le_sql into nombre using c.mag;
    insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre); 
    COMMIT;
    END;
    
    -- insertion des comptages pour les mails
    BEGIN
    select code, libelle into code_action, libelle_action from table_code wherecocod='W';
    select colonne5 into nom from table5wherewhere exnum ='REFUSEM';
    le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and valeur3 <>'' '' and valeur4 IN(''Y'', ''A'', ''T'') and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(val)-2)= :1 minus select valeur5 from '||nom||')';
    executeimmediate le_sql into nombre using c.mag;
    insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre); 
    COMMIT;
    END;
    
    -- insertion des comptages pour les couriers
    BEGIN
     
    select code, libelle into code_action, libelle_action from table_code wherecocod='M';
    select colonne5 into nom from table5wherewhere exnum ='REFUSEA';
    le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(ival)-2)= :1 minus select valeur5 from '||nom||')';
    executeimmediate le_sql into nombre using c.mag;
    insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre); 
    COMMIT;
    END;
    ENDLOOP;
    Exception
    WhenOTHERSthennull;
    END;
    /
    
    donc voilà je ne sais pas si je peux optimiser les requêtes pour que le temps d'execution de cette procédure soit plus rpide.

  17. #17
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    dois y avoir pas mal d'erreurs de syntaxe

    En effet, ça va être difficile de tout faire en une passe

  18. #18
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Citation Envoyé par Fred_D
    dois y avoir pas mal d'erreurs de syntaxe
    Ah bon?
    il y a beaucoup d'erreurs de syntaxe?

  19. #19
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    il manque des espaces mais c'est peut être un copier/coller raté

  20. #20
    Membre éclairé
    Inscrit en
    Décembre 2005
    Messages
    455
    Détails du profil
    Informations forums :
    Inscription : Décembre 2005
    Messages : 455
    Par défaut
    Citation Envoyé par Fred_D
    il manque des espaces mais c'est peut être un copier/coller raté
    oui c'est un copier coller râté car ma procédure fonctionne

Discussions similaires

  1. Optimisation d'une requête pour l'utiliser dans trigger
    Par Skandyx dans le forum Développement
    Réponses: 1
    Dernier message: 29/11/2012, 09h12
  2. Optimisation d'une requête d'insertion
    Par fdraven dans le forum Oracle
    Réponses: 15
    Dernier message: 01/12/2005, 14h00
  3. Optimisation d'une requête patchwork
    Par ARRG dans le forum Langage SQL
    Réponses: 1
    Dernier message: 11/09/2005, 15h23
  4. optimisation d'une requête avec jointure
    Par champijulie dans le forum PostgreSQL
    Réponses: 8
    Dernier message: 07/07/2005, 09h45
  5. [DB2] Optimisation d'une requête
    Par ahoyeau dans le forum DB2
    Réponses: 7
    Dernier message: 11/03/2005, 17h54

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