Discussion :

[phig]

Bonjour,

La version 10G de OEM (version Web) utilise différentes métriques pour mettre en évidence les 'instructions SQL les plus consommatrices de ressources'. Sur une de mes applications (en pro*C), cet outil met en évidence comme instruction un truncate.

Il s'agit de truncate d'une table d'une colonne dans laquelle je stocke temporairement quelques données (pas plus de 30). Le tout est dans une boucle qui peut s'exécuter plus milliers de fois.
Sachant que la table est très petite, je me demandais si un Delete * serait pas plus rapide qu'un truncate. Comme un truncate implique un commit, ce n'est peut être pas très efficace de faire tant de transaction ...

Comme un truncate est plus efficace qu'un delete * sur une grosse table, je me demandais si la réciproque était vraie ?

Avant de faire des tests concrets je voudrais avoir votre avis

Phig

[orafrance]

Comme un truncate implique un commit

euhh... non, il n'y a pas de commit avec un truncate

Alors d'après le gourou j'ai nommé Tom Kyte : le truncate est couteux donc pour les petits volumes c'est plus couteux que le delete même si celui-ci consomme du rollback et du redo. En plus, si il y a un lock sur la table le truncate foire immédiatement

[phig]

Salut Orafrance

euhh... non, il n'y a pas de commit avec un truncate

Dans ma doc oracle je lis :

Commit automatique : Exécution d'un ordre du LDD ou du LCD

et plus loin je lis encore

Truncate table est un autre ordre LDD ... vous ne pouvez pas annuler un ordre truncate

J'ai pas compris un truc alors, car je pensais que si je faisais un truncate dans mon code ça impliquait alors un commit ...

Sinon merci pour l'info du fameux Tom, je vais appliquer ses conseils sur le champs.

Phig

[orafrance]

alors là il y a une grosse boulette effectivement, le truncate détruit immédiatement les données de la table sans passer par les redos et surtout les rollbacks segments... donc c'est pas qu'il y a un commit auto mais surtout que le commit n'est pas nécessaire.

Si quelqu'un ayant des connaissances théoriques plus sûres que les miennes ce serait gentil d'expliquer les mécaniques du truncate

[orafrance]

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/general.htm#11948

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

A TRUNCATE statement does not generate any rollback information and it commits immediately

j'ai du mal à voir comment commiter quelque chose qui n'utilise pas de rollback... je pense que c'est juste un abus de langage pour montrer que l'effet est immédiat

[phig]

pour appuyer mes dires, je te fais part d'une petite expérience personnelle.
Pour tester un programme (ProC) sur la machine de production , j'avais supprimé tous les commit et mis des rollback à la place. Je testais autre chose que les instructions SQL donc c'est le moyen le plus simple que j'avais trouvé pour les désactiver. Manque de bol, plusieurs instructions avaient quand même été validées. Et c'est là que j'ai découvert que c'était les Truncate qui avait commité la transaction.
C'et la raison pour laquelle j'ai dit qu'un truncate implique un commit. Maintenant, la réalité est peut être plus subtile que cela...

[phig]

vu sur metalink

Actually TRUNCATE is a DDL, and all DDL does a commit, then the
data dictionary update, then another commit. The point is, if the
TRUNCATE fails, there was an implicit commit that preceded the
attempted TRUNCATE, and commited your transaction that was open up to
that point.

So, if session one does an insert into table test1, and does not
commit, then session two does an insert into table test2, and does
not commit, then each session has an open (uncommitted) transaction.

Now, if session two does a truncate of test1 (which has an uncommited
transaction against it in the session one), the truncate will fail
with an ORA-0054. However, the insert into test2, which was part of
the open transaction of session two, is now committed, even if the
truncate failed.

The point is, DDL does not do a commit in the transaction it is part
of. It does a commit, ending any current transaction in the current
session, then it does a data dictionary update, followed by another
commit.

[thomasjcj]

Orafrance a raison, c'est un abus de langage, un truncate n'est pas une transaction et on ne peut donc parler de commit.

D'apres ce que je sais, un Truncate (sans la clause REUSE STORAGE) remet simplement la HWM (High Water Mark) de la table a minextent (d'ou le gain de performace par rapport au DELETE qui genère des redos). Ensuite, les blocs est les extents de la table sont libérés et pourront etre réutilisés par d'autres segments. Ca explique aussi pourquoi ce n'est pas une transaction comme un DELETE.
Dans un Delete, les lignes sont effacées mais la HWM reste inchangée. Ca a pour effet que meme si la table est vide, la place physique occupée reste inchangée et les memes blocs seront utilisés pour de futurs Insert/Update.
Une conséquence par ex est l'effet sur les FULL TABLE SCAN. Au cours d'un FTS, tous les blocs sous la HWM sont parcourus (meme s'ils sont vides de données a la suite d'un DELETE). Si la table etait avant le DELETE tres grosse, le FTS sera toujours tres long sur une table pourtant vide. Dans ce cas, c'est plus efficace de faire un TRUNCATE pour que le FTS ne parcours que le minextent.

[thomasjcj]

vu sur metalink
Citation:
Actually TRUNCATE is a DDL, and all DDL does a commit, then the
data dictionary update, then another commit. The point is, if the
TRUNCATE fails, there was an implicit commit that preceded the
attempted TRUNCATE, and commited your transaction that was open up to
that point.

So, if session one does an insert into table test1, and does not
commit, then session two does an insert into table test2, and does
not commit, then each session has an open (uncommitted) transaction.

Now, if session two does a truncate of test1 (which has an uncommited
transaction against it in the session one), the truncate will fail
with an ORA-0054. However, the insert into test2, which was part of
the open transaction of session two, is now committed, even if the
truncate failed.

The point is, DDL does not do a commit in the transaction it is part
of. It does a commit, ending any current transaction in the current
session, then it does a data dictionary update, followed by another
commit.

Il ne faut pas confondre. La il est question des transaction ouvertes au moment ou le TRUNCATE est lancé. Comme tout DDL, avant d'executer la commande, Oracle fait un commit des transctions ouvertes. Le truncate en lui meme n'est pas commité.

[phig]

vi on est d'accord, ça doit être moi qui me suis mal expliqué dès le début.
C'est pas grave ça permet de réviser ces classiques , enfin je parle pour moi ...

[orafrance]

The point is, DDL does not do a commit in the transaction it is part
of. It does a commit, ending any current transaction in the current
session, then it does a data dictionary update, followed by another
commit.

c'est donc bien mes dires qui sont confirmés ici

Merci thomasjcj pour cette explication très claire