Différence entre TRUNCATE et DELETE ?

HRS · 04/08/2008, 09h48

quelle est la différence entre TRUNCATE et DELETE sans WHERE

lequel doit-on privilégier pour seulement enlever toutes les lignes d'une table
afin de pouvoir par sql*loader la recharger intégralement

McM · 04/08/2008, 10h04

Un truncate est plus rapide.
Il y a un commit implicite.
Il ne faut pas que la table soit référencée par une FK

Vincent Rogier · 04/08/2008, 10h20

Attention, Le fait que le commit soit implicite fait qu'un truncate est irrémédiable !

jmguiche · 04/08/2008, 19h06

Delete, c'est du DML, cela efface les lignes.
Truncate, c'est du DDL, cela inscrit dans le dictionnaire du sgbd que la table est vide.

Le temps de traitement d'un delete dépend du volume dans la table (nombre de lignes, volume disque à scanner, index à réaménager etc...).
Truncate n'accede qu'au dictionnaire pour simplement dire que le table (et les index !) sont vides.

Delete et truncate ne demandent pas les même privileges.

Comme tout DDL, en effet, il y a un commit implicite.

orafrance · 05/08/2008, 13h56

et DELETE ne touche pas à la HWM. Donc un DELETE de toutes les tables d'un tablespace ne suffit pas à réduire la taille des datafiles

pacmann · 05/08/2008, 14h49

Je peux en rajouter une ?
TRUNCATE ne déclenche pas les triggers !

(ouais, je suis content de moi, et alors ?

)

Vincent Rogier · 05/08/2008, 14h53

Citation:

Envoyé par pacmann

Je peux en rajouter une ?
TRUNCATE ne déclenche pas les triggers !

(ouais, je suis content de moi, et alors ?

)

La réponse de jmquiche le sous entendait déjà...

rvfranck · 16/08/2008, 05h48

Citation:

Envoyé par vicenzo

La réponse de jmquiche le sous entendait déjà...

J'aimerai bien savoir quel point de la reponse de jmquiche le sous entend. Merci

Vincent Rogier · 16/08/2008, 12h04

Citation:

Envoyé par rvfranck

J'aimerai bien savoir quel point de la reponse de jmquiche le sous entend. Merci

Citation:

Envoyé par jmquiche

Delete, c'est du DML, ...
Truncate, c'est du DDL,...

rvfranck · 19/08/2008, 14h19

Salut,
Je n'ai toujours pas compris, c'est vrai que j'ai la tête dur. Est ce que tu essayes de me dire qu'une DDL ne déclenche pas de trigger?

McM · 19/08/2008, 14h28

Non un DDL peut faire exécuter un trigger (exemple un ajout de colonne avec un defaut).

rvfranck · 19/08/2008, 15h40

Ok, oublions ça. J'ai relu les messages calmement et j'ai essayé de comprendre.

C'est ce message qui m'a embrouiller l'esprit

Citation:

Envoyé par pacmann

Je peux en rajouter une ?
TRUNCATE ne déclenche pas les triggers !
(ouais, je suis content de moi, et alors ?

)

C'est différent de dire que "TRUNCATE" ne déclenche pas un trigger "DELETE".

Vyns1520 · 08/08/2011, 17h58

J'en ajoute une autre (également déjà sous-entendu) :

Les colonnes de type "auto-incrément" (IDENTITY par exemple) sont réinitialisées.

kdmbella · 08/08/2011, 18h24

Citation:

Envoyé par rvfranck

Ok, oublions ça. J'ai relu les messages calmement et j'ai essayé de comprendre.

C'est ce message qui m'a embrouiller l'esprit

C'est différent de dire que "TRUNCATE" ne déclenche pas un trigger "DELETE".

Oui la difference c'est que TRUNCATE ne déclenche aucun type de Trigger y compris les trigger de Type DELETE

Pomalaix · 08/08/2011, 22h31

Citation:

Envoyé par Vyns1520

Les colonnes de type "auto-incrément" (IDENTITY par exemple) sont réinitialisées.

Sauf que sous Oracle, qui est l'objet de ce forum, les colonnes de type auto incrément n'existent pas... Dommage d'avoir réveillé un sujet qui dormait depuis 3 ans pour être à côté de la plaque !

Pomalaix · 08/08/2011, 22h40

Citation:

Envoyé par kdmbella

Oui la difference c'est que TRUNCATE ne déclenche aucun type de Trigger y compris les trigger de Type DELETE

Perdu ! Un déclencheur de type DDL peut parfaitement se déclencher suite à un TRUNCATE.

laurentschneider · 08/08/2011, 22h54

un truncate viole la sacro sainte read write consistency

en effet, un truncate pendant un long select ou refresh de mv a un effet imprévisible

Ahmed AANGOUR · 12/08/2011, 15h59

Le DELETE est l'une des opérations les plus coûteuses dans Oracle.
En effet, lors d'un DELETE Oracle doit effectuer les opérations suivantes:

- Rechercher les lignes à updater (équivalent à l'instruction SELECT)

- Supprimer les lignes

- Les entrées d’index qui référencent les lignes à supprimer doivent être supprimées de l’index

- Exécuter le code des triggers BEFORE ou AFTER DELETE.

- Vérifier s'il existe des lignes dans une autre table enfant qui référencent des lignes à supprimer dans la table parent (d'ailleurs si la table enfant est volumineuse et qu'il n'existe pas d'index pour la Foreign key cette partie du DELETE peut être cause de lenteur).

- S'il existe des lignes enfants et que la FK est définie en mode "ON DELETE CASCADE" alors ces lignes doivent aussi être supprimées (triggers déclenchés+MAJ des index etc.)

- Génération d'UNDO + REDO

De plus comme l'a dit OraFrance le HWM n'est pas redescendu. Ainsi, même si vous avez supprimé 90% de votre table les prochains Full Table Scan liront tous les blocks comme si la table n'avait jamais été purgée.

Le truncate lui a un coût très faible mais comme l'a dit Laurent c'est une opération irreversible.
Le truncate a 2 modes d'utilisation
- Drop Storage => va restituer les blocks alloués au segment truncaté
- Reuse Storage => Les lignes n'existent plus, le HWM est redescendu mais les blocks restent alloués au segment.

laurentschneider · 12/08/2011, 16h07

et aussi dans 11.2.0.2
DROP ALL STORAGE
qui restitue tous les extents (y compris le segment initial)

> une opération irréversible
et imprévisible! on ne sait pas vraiment l'effet que ça a sur les requêtes en cours

04/08/2008, 09h48	#1
HRS Membre éprouvé Inscription : mars 2002 Messages : 574 Détails du profil Informations forums : Inscription : mars 2002 Messages : 574 Points : 470 Points : 470	Différence entre TRUNCATE et DELETE ? quelle est la différence entre TRUNCATE et DELETE sans WHERE lequel doit-on privilégier pour seulement enlever toutes les lignes d'une table afin de pouvoir par sql*loader la recharger intégralement
	00

04/08/2008, 10h04	#2
McM Expert Confirmé Sénior Inscription : juillet 2003 Messages : 3 459 Détails du profil Informations forums : Inscription : juillet 2003 Messages : 3 459 Points : 4 226 Points : 4 226	Un truncate est plus rapide. Il y a un commit implicite. Il ne faut pas que la table soit référencée par une FK __________________ More Code : More Bugs. Less Code : Less Bugs
	00

04/08/2008, 10h20	#3
Vincent Rogier Rédacteur/Modérateur vincent rogier Inscription : juillet 2007 Messages : 2 355 Détails du profil Informations personnelles : Nom : vincent rogier Âge : 34 Informations forums : Inscription : juillet 2007 Messages : 2 355 Points : 3 108 Points : 3 108	Attention, Le fait que le commit soit implicite fait qu'un truncate est irrémédiable ! __________________ Vincent Rogier. Rubrique ORACLE : Accueil - Forum - Tutoriels - FAQ - Livres - Blog Vous voulez contribuer à la rubrique Oracle ? Contactez la rubrique ! OCILIB (C Driver for Oracle) Librairie C Open Source multi-plateformes pour accéder et manipuler des bases de données Oracle
	00

05/08/2008, 13h56	#5
orafrance Rédacteur/Modérateur Inscription : janvier 2004 Messages : 15 861 Détails du profil Informations personnelles : Âge : 35 Informations forums : Inscription : janvier 2004 Messages : 15 861 Points : 16 212 Points : 16 212	et DELETE ne touche pas à la HWM. Donc un DELETE de toutes les tables d'un tablespace ne suffit pas à réduire la taille des datafiles __________________ Règles et liens du forum Oracle Guide Oracle Pense-bête du DBA Oracle : Exemple
	00

05/08/2008, 14h49	#6
pacmann Membre Expert Pacman Pacman Business analyst Inscription : juin 2004 Messages : 1 417 Détails du profil Informations personnelles : Nom : Pacman Pacman Âge : 31 Localisation : France, Paris (Île de France) Informations professionnelles : Activité : Business analyst Secteur : Finance Informations forums : Inscription : juin 2004 Messages : 1 417 Points : 2 309 Points : 2 309	Je peux en rajouter une ? TRUNCATE ne déclenche pas les triggers ! (ouais, je suis content de moi, et alors ? ) __________________ (c'est ma photo) Paku, Paku ! Pour les jeunes incultes : non, je ne suis pas un pokémon... Le pacblog : http://pacmann.over-blog.com/
	00

04/08/2008, 19h06	#4
jmguiche Membre confirmé Inscription : août 2005 Messages : 270 Détails du profil Informations forums : Inscription : août 2005 Messages : 270 Points : 294 Points : 294	Delete, c'est du DML, cela efface les lignes. Truncate, c'est du DDL, cela inscrit dans le dictionnaire du sgbd que la table est vide. Le temps de traitement d'un delete dépend du volume dans la table (nombre de lignes, volume disque à scanner, index à réaménager etc...). Truncate n'accede qu'au dictionnaire pour simplement dire que le table (et les index !) sont vides. Delete et truncate ne demandent pas les même privileges. Comme tout DDL, en effet, il y a un commit implicite.
	00

19/08/2008, 14h19	#10
rvfranck Membre éprouvé Étudiant Inscription : novembre 2004 Messages : 739 Détails du profil Informations personnelles : Localisation : Canada Informations professionnelles : Activité : Étudiant Informations forums : Inscription : novembre 2004 Messages : 739 Points : 494 Points : 494	Salut, Je n'ai toujours pas compris, c'est vrai que j'ai la tête dur. Est ce que tu essayes de me dire qu'une DDL ne déclenche pas de trigger? __________________ "Celui qui reconnaît consciemment ses limites est le plus proche de la perfection." Johann Wolfgang
	00

19/08/2008, 14h28	#11
McM Expert Confirmé Sénior Inscription : juillet 2003 Messages : 3 459 Détails du profil Informations forums : Inscription : juillet 2003 Messages : 3 459 Points : 4 226 Points : 4 226	Non un DDL peut faire exécuter un trigger (exemple un ajout de colonne avec un defaut). __________________ More Code : More Bugs. Less Code : Less Bugs
	00

08/08/2011, 17h58	#13
Vyns1520 Invité de passage Inscription : juin 2008 Messages : 12 Détails du profil Informations forums : Inscription : juin 2008 Messages : 12 Points : 2 Points : 2	J'en ajoute une autre (également déjà sous-entendu) : Les colonnes de type "auto-incrément" (IDENTITY par exemple) sont réinitialisées.
	02

08/08/2011, 22h54	#17
laurentschneider Expert Confirmé Sénior Laurent Schneider Administrateur de base de données Inscription : décembre 2005 Messages : 2 927 Détails du profil Informations personnelles : Nom : Laurent Schneider Localisation : Suisse Informations professionnelles : Activité : Administrateur de base de données Secteur : Finance Informations forums : Inscription : décembre 2005 Messages : 2 927 Points : 4 549 Points : 4 549	un truncate viole la sacro sainte read write consistency en effet, un truncate pendant un long select ou refresh de mv a un effet imprévisible __________________ Mon blog : laurentschneider.com Mon livre : Advanced Oracle SQL Programming
	00

12/08/2011, 15h59	#18
Ahmed AANGOUR Membre confirmé Ahmed AANGOUR DBA Etudes Oracle Inscription : janvier 2010 Messages : 123 Détails du profil Informations personnelles : Nom : Ahmed AANGOUR Âge : 33 Localisation : France Informations professionnelles : Activité : DBA Etudes Oracle Informations forums : Inscription : janvier 2010 Messages : 123 Points : 217 Points : 217	Le DELETE est l'une des opérations les plus coûteuses dans Oracle. En effet, lors d'un DELETE Oracle doit effectuer les opérations suivantes: - Rechercher les lignes à updater (équivalent à l'instruction SELECT) - Supprimer les lignes - Les entrées d’index qui référencent les lignes à supprimer doivent être supprimées de l’index - Exécuter le code des triggers BEFORE ou AFTER DELETE. - Vérifier s'il existe des lignes dans une autre table enfant qui référencent des lignes à supprimer dans la table parent (d'ailleurs si la table enfant est volumineuse et qu'il n'existe pas d'index pour la Foreign key cette partie du DELETE peut être cause de lenteur). - S'il existe des lignes enfants et que la FK est définie en mode "ON DELETE CASCADE" alors ces lignes doivent aussi être supprimées (triggers déclenchés+MAJ des index etc.) - Génération d'UNDO + REDO De plus comme l'a dit OraFrance le HWM n'est pas redescendu. Ainsi, même si vous avez supprimé 90% de votre table les prochains Full Table Scan liront tous les blocks comme si la table n'avait jamais été purgée. Le truncate lui a un coût très faible mais comme l'a dit Laurent c'est une opération irreversible. Le truncate a 2 modes d'utilisation - Drop Storage => va restituer les blocks alloués au segment truncaté - Reuse Storage => Les lignes n'existent plus, le HWM est redescendu mais les blocks restent alloués au segment. __________________ Mon blog Oracle: http://ahmedaangour.blogspot.com/
	00

12/08/2011, 16h07	#19
laurentschneider Expert Confirmé Sénior Laurent Schneider Administrateur de base de données Inscription : décembre 2005 Messages : 2 927 Détails du profil Informations personnelles : Nom : Laurent Schneider Localisation : Suisse Informations professionnelles : Activité : Administrateur de base de données Secteur : Finance Informations forums : Inscription : décembre 2005 Messages : 2 927 Points : 4 549 Points : 4 549	et aussi dans 11.2.0.2 DROP ALL STORAGE qui restitue tous les extents (y compris le segment initial) > une opération irréversible et imprévisible! on ne sait pas vraiment l'effet que ça a sur les requêtes en cours __________________ Mon blog : laurentschneider.com Mon livre : Advanced Oracle SQL Programming
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email