Discussion :

[Isildur]

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.

[SheikYerbouti]

non, la première syntaxe n'est pas permise

[orafrance]

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);

[plaineR]

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...

[Isildur]

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' ?

[SheikYerbouti]

EXISTS s'arrête à la première occurence trouvée.

[Isildur]

cela rend la requete plus rapide qu'en utilisant IN ?

[orafrance]

Oui, le IN raméne toutes les lignes de la sous-requêtes alors que le EXISTS s'arrête à la première

[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.

[orafrance]

pas du tout, dans les deux cas les indexes sont utilisés convenablement

[SheikYerbouti]

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

[plaineR]

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.

[SheikYerbouti]

...

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 !

[Pomalaix]

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

[orafrance]

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

[SheikYerbouti]

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

[orafrance]

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à

[SheikYerbouti]

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 ?

[orafrance]

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

[SheikYerbouti]

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) ?