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

Discussion :

delete from


Sujet :

SQL Oracle

  1. #1
    Membre du Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    avril 2003
    Messages
    70
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : Biens de consommation

    Informations forums :
    Inscription : avril 2003
    Messages : 70
    Points : 50
    Points
    50
    Par défaut [resolu] delete from
    bonjour, j'aimerais savoir si Oracle supporte la syntaxe delete from

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    delete TABLE_1 from TABLE1, TABLE_2
    where TABLE_1.CLEF = TABLE_2.clef
    ou si je suis obligé de passer par une requete imbriquée (à mon avis beaucoup plus long...)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    delete TABLE_1 
    where TABLE_1.CLEF in( select TABLE_2.clef  from TABLE_2)

    merci de votre aide.

  2. #2
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    non, la première syntaxe n'est pas permise
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  3. #3
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 42
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 066
    Points
    19 066
    Par défaut
    La 1° et la 2° ne font pas tout à fait la même chose (bon déjà la 1° ne marche pas )

    Voila ce qu'il convient de faire :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    DELETE table_1 
    WHERE EXISTS (SELECT 1 FROM table_2 WHERE table_1.clef = table_2.clef);

  4. #4
    Membre expert

    Homme Profil pro
    Chef de projet en SSII
    Inscrit en
    janvier 2004
    Messages
    2 862
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Chef de projet en SSII
    Secteur : Conseil

    Informations forums :
    Inscription : janvier 2004
    Messages : 2 862
    Points : 3 609
    Points
    3 609
    Par défaut
    Quelle est la différence entre les deux ???

    La clause in cherche s'il y un enregistrement dans la TABLE_2 qui correspond à TABLE_1.clef (via TABLE_2.clef)

    La clause exists cherche s'il y a en enregistrement pour lequel TABLE_1.clef est égal à TABLE_2.clef

    Sauf erreur de ma part les deux clauses agissent sur le même ensemble...
    Un problème sans solution est un problème mal posé

    Merci de poser vos questions sur le forum, je ne réponds pas aux questions posées par MP.

  5. #5
    Membre du Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    avril 2003
    Messages
    70
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : Biens de consommation

    Informations forums :
    Inscription : avril 2003
    Messages : 70
    Points : 50
    Points
    50
    Par défaut
    bon dommage pour la première syntaxe , elle est pourtant permise sous Sql-server, quid de cette solution
    DELETE table_1
    WHERE EXISTS (SELECT 1 FROM table_2 WHERE table_1.clef = table_2.clef);
    quel est la différence avec un 'IN' ?

  6. #6
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    EXISTS s'arrête à la première occurence trouvée.
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  7. #7
    Membre du Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    avril 2003
    Messages
    70
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : Biens de consommation

    Informations forums :
    Inscription : avril 2003
    Messages : 70
    Points : 50
    Points
    50
    Par défaut
    cela rend la requete plus rapide qu'en utilisant IN ?

  8. #8
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 42
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 066
    Points
    19 066
    Par défaut
    Oui, le IN raméne toutes les lignes de la sous-requêtes alors que le EXISTS s'arrête à la première

  9. #9
    Membre expert

    Homme Profil pro
    Chef de projet en SSII
    Inscrit en
    janvier 2004
    Messages
    2 862
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Chef de projet en SSII
    Secteur : Conseil

    Informations forums :
    Inscription : janvier 2004
    Messages : 2 862
    Points : 3 609
    Points
    3 609
    Par défaut
    Pas forcément, cela dépend si ta table est indexée ou non sur la colonne sur laquelle tu fais le in et si tu as beaucoup de données ou non dans la table de la sous-requête.
    Un problème sans solution est un problème mal posé

    Merci de poser vos questions sur le forum, je ne réponds pas aux questions posées par MP.

  10. #10
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 42
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 066
    Points
    19 066
    Par défaut
    pas du tout, dans les deux cas les indexes sont utilisés convenablement

  11. #11
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    Il serait interressant de visualier l'explain plan des 2 solutions. Non pour l'utilisation des index mais plutôt pour la méthode d'accès à ces index
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  12. #12
    Membre expert

    Homme Profil pro
    Chef de projet en SSII
    Inscrit en
    janvier 2004
    Messages
    2 862
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Chef de projet en SSII
    Secteur : Conseil

    Informations forums :
    Inscription : janvier 2004
    Messages : 2 862
    Points : 3 609
    Points
    3 609
    Par défaut
    Tu m'excuseras mais je ne sais pas comment il peut prendre les mêmes indexes dans :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    select col1
    from table_1
    where col1 in (select col2
                         from table_2)
    => il est clair qu'il y a un access full sur table_2
    => prend l'index sur table_1.col1

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    select col1
    from table_1
    where exists (select null
                        from table_2
                         where col2 = col1)
    => prend l'index sur table_2.col2
    => fait un access full sur table_1.col1

    Donc après cela dépend de la quantité de données que tu as dans chacune de tes tables.
    Un problème sans solution est un problème mal posé

    Merci de poser vos questions sur le forum, je ne réponds pas aux questions posées par MP.

  13. #13
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    Citation Envoyé par PlaineR
    ...
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    select col1
    from table_1
    where col1 in (select col2
                         from table_2)
    => il est clair qu'il y a un access full sur table_2
    ...
    NON ! s'il index il y a, ce sera un INDEX RANGE ACCESS !
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

  14. #14
    Rédacteur

    Profil pro
    Consultant
    Inscrit en
    décembre 2002
    Messages
    3 351
    Détails du profil
    Informations personnelles :
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant

    Informations forums :
    Inscription : décembre 2002
    Messages : 3 351
    Points : 7 519
    Points
    7 519
    Par défaut
    Citation Envoyé par PlaineR
    Pas forcément, cela dépend si ta table est indexée ou non sur la colonne sur laquelle tu fais le in et si tu as beaucoup de données ou non dans la table de la sous-requête.
    Ce qui serait intéressant, c'est que vous nous fabriquiez un petit exemple réel où, sur les mêmes données avec les mêmes index, le IN est plus rapide que le EXISTS.

    Et si vous en trouvez un, bravo d'avance, car je suis convaincu que ce n'est pas possible
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

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

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 066
    Points
    19 066
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    select val
    from table_1
    where id in (select tab1_id
                         from table_2);
    VAL
    ------------------------------
    toto
    tata
    tutu
    Ecoulé : 00 :00 :00.15

    Plan d'exécution
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=33)
    1 0 NESTED LOOPS (SEMI) (Cost=2 Card=3 Bytes=33)
    2 1 TABLE ACCESS (FULL) OF 'TABLE_1' (Cost=2 Card=4 Bytes=32
    )

    3 1 INDEX (RANGE SCAN) OF 'INDEX_FK' (NON-UNIQUE)




    Statistiques
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    6 consistent gets
    0 physical reads
    0 redo size
    344 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select val
    from table_1
    where exists (select 1
                         from table_2
                         where id=tab1_id);
    VAL
    ------------------------------
    toto
    tata
    tutu
    Ecoulé : 00 :00 :00.14

    Plan d'exécution
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=33)
    1 0 NESTED LOOPS (SEMI) (Cost=2 Card=3 Bytes=33)
    2 1 TABLE ACCESS (FULL) OF 'TABLE_1' (Cost=2 Card=4 Bytes=32
    )

    3 1 INDEX (RANGE SCAN) OF 'INDEX_FK' (NON-UNIQUE)




    Statistiques
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    6 consistent gets
    0 physical reads
    0 redo size
    344 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed

  16. #16
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    Et s'il y avait 100000 enregistrements dans TABLE_1 ? FULLE SCAN encore ?

    et il y a bien un INDEX RANGE SCAN sur TABLE_2
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

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

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 066
    Points
    19 066
    Par défaut
    OK, on cherche la bagarre ? Ca marche

    j'ai 100000 dabs table_1 et 500000 dans table_2 avec les lignes jointes de manière alléatoire

    Les stats sont calculées sur les 2 tables

    Voila les résultats :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select val
    from table_1
    where id in (select tab1_id
                         from table_2)
    and rownum<10;
    Ecoulé : 00 :00 :02.15

    Plan d'exécution
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=545 Card=9 Bytes=126
    )

    1 0 COUNT (STOPKEY)
    2 1 HASH JOIN (SEMI) (Cost=545 Card=99340 Bytes=1390760)
    3 2 TABLE ACCESS (FULL) OF 'TABLE_1' (Cost=22 Card=100000
    Bytes=900000)

    4 2 INDEX (FAST FULL SCAN) OF 'INDEX_FK' (NON-UNIQUE) (Cos
    t=92 Card=500005 Bytes=2500025)





    Statistiques
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    1830 consistent gets
    242 physical reads
    9940 redo size
    369 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    9 rows processed
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select val
    from table_1
    where exists (select 1
                         from table_2
                         where id=tab1_id)
    and rownum<10;
    Ecoulé : 00 :00 :02.00

    Plan d'exécution
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=545 Card=9 Bytes=126
    )

    1 0 COUNT (STOPKEY)
    2 1 HASH JOIN (SEMI) (Cost=545 Card=99340 Bytes=1390760)
    3 2 TABLE ACCESS (FULL) OF 'TABLE_1' (Cost=22 Card=100000
    Bytes=900000)

    4 2 INDEX (FAST FULL SCAN) OF 'INDEX_FK' (NON-UNIQUE) (Cos
    t=92 Card=500005 Bytes=2500025)





    Statistiques
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    1662 consistent gets
    35 physical reads
    0 redo size
    369 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    9 rows processed
    Résultat :

    150 ms gagnées et 35 lecture sur disque au lieu de 242

    Donc il n'y a pas photo, le EXISTS est mieux

    Ce résultat est dû aux lectures sur disque puisque les plans d'exécution sont strictement identiques, FULL sur table_1 et FAST FULL SCAN sur table_2

    A noter que si j'avais sélectionné l'id de table_1 (donc la PK) j'aurais pas fait de TABLE ACCESS mais un INDEX ACCESS sur table_1

    PS : les développeurs vous haïssent, j'ai bien plombé la base là

  18. #18
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    Et si tu fais :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select/*+ USE_NL( a b ) */ a.val 
    from table_1 a
    where exists (select 1 
                         from table_2 b
                         where b.id= a.tab1_id) 
    and rownum<10;
    ça donne quoi ?
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

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

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 066
    Points
    19 066
    Par défaut
    c'est pareil, tu m'aurais dit /*+ ORDERED */ là OK parce qu'il traite le EXISTS en premier :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select/*+ ordered */ a.val
    from table_1 a
    where exists (select 1
                         from table_2 b
                         where b.tab1_id= a.id)
    and rownum<10
    Ecoulé : 00 :00 :01.04

    Plan d'exécution
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1457 Card=9 Bytes=12
    6)

    1 0 COUNT (STOPKEY)
    2 1 MERGE JOIN (Cost=1457 Card=500005 Bytes=7000070)
    3 2 SORT (UNIQUE)
    4 3 INDEX (FULL SCAN) OF 'INDEX_FK' (NON-UNIQUE) (Cost=9
    44 Card=500005 Bytes=2500025)

    5 2 SORT (JOIN) (Cost=513 Card=100000 Bytes=900000)
    6 5 TABLE ACCESS (FULL) OF 'TABLE_1' (Cost=22 Card=10000
    0 Bytes=900000)





    Statistiques
    ----------------------------------------------------------
    0 recursive calls
    4 db block gets
    1620 consistent gets
    138 physical reads
    0 redo size
    384 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    2 sorts (disk)
    9 rows processed
    score à battre 1s04

  20. #20
    Expert éminent sénior
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2003
    Messages : 6 760
    Points : 11 669
    Points
    11 669
    Par défaut
    Citation Envoyé par orafrance
    c'est pareil, tu m'aurais dit /*+ ORDERED */ là OK parce qu'il traite le EXISTS en premier :
    ??? tu veux dire que le hint USE_NL n'est pas pris en compte par l'optimiseur (il utilise toute de même le HASH JOIN) ?
    Rédacteur Oracle (Oracle ACE)
    Guide Oracle ,Guide PL/SQL, Guide Forms 9i/10g, Index de recherche
    Je ne réponds pas aux questions techniques par MP
    Blogs: Forms-PL/SQL-J2EE - Forms Java Beans

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 3 123 DernièreDernière

Discussions similaires

  1. [Débutant] Problème avec DELETE FROM
    Par gouakkamol dans le forum Langage SQL
    Réponses: 2
    Dernier message: 30/11/2007, 09h19
  2. Différences entre delete table et delete from table
    Par pegase06 dans le forum Administration
    Réponses: 3
    Dernier message: 16/02/2007, 15h25
  3. Delete * From * Where ?!
    Par gui38 dans le forum Langage SQL
    Réponses: 4
    Dernier message: 21/12/2006, 10h51
  4. Delete From In Select
    Par licorne dans le forum Requêtes
    Réponses: 4
    Dernier message: 06/10/2006, 13h13
  5. DELETE FROM t where t.id IN (SELECT id FROM t....)
    Par davcha dans le forum Langage SQL
    Réponses: 4
    Dernier message: 05/01/2006, 15h19

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