Du mauvais usage du TRUNCATE

En préambule, voici la définition du TRUNCATE SQL(WIKIPEDIA):

In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse).
The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.
It was officially introduced in the SQL:2008 standard.

The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).
The following characteristics distinguish TRUNCATE TABLE from DELETE:
*In the Oracle Database, TRUNCATE is implicitly preceded and followed by a commit operation. (This may also be the case in MySQL, when using a transactional storage engine.)
*Typically, TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired. Records removed this way cannot be restored in a rollback operation. Two notable exceptions to this rule are the implementations found in PostgreSQL and Microsoft SQL Server, both of which allow TRUNCATE TABLE statements to be committed or rolled back transactionally.
*You cannot specify a WHERE clause in a TRUNCATE TABLE statement—it is all or nothing.
*TRUNCATE TABLE cannot be used when a foreign key references the table to be truncated, since TRUNCATE TABLE statements do not fire triggers. This could result in inconsistent data because ON DELETE/ON UPDATE triggers would not fire.
*In some database systems, TRUNCATE TABLE resets the count of an Identity column back to the identity’s seed.
*In Microsoft SQL Server 2000 and beyond in full recovery mode, every change to the database is logged, so TRUNCATE TABLE statements can be used for tables involved in log shipping. [1]

Lors de l’utilisation des ETL, la création d’un mapping peut, si on veut opérer en annule et remplace, faire appel à un mécanisme de suppression des données.
Ce mécanisme proposé est souvent nommé TRUNCATE TABLE.
Les ETL Buisness Objects Data Integrator et Informatica power Center sont tous deux concernés par ma remarque.
Ces ETL font appel à la commande SQL DELETE, et non au TRUNCATE SQL.

Voici donc la définition du DELETE SQL (WIKIPEDIA):

In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
A subset may be defined for deletion using a condition, otherwise all records are removed.
Some DBMSs, like MySQL, allow to delete rows from multiple tables with one DELETE statement (this is sometimes called multi-table DELETE).

Ainsi, lors de suppression de gros volumes de données, l’ETL peut prendre un temps non négligeable, long, beaucoup trop long, voire s’approchant de l’infini.
En effet, le DELETE SQL réalise le nettoyage de la table LIGNE PAR LIGNE, alors que le TERUNCATE SQL fait la même opération, mais globalement.
Dans ce cas, il est souvent plus malin de ne pas suivre la voie tracée par l’ETL, de stopper l’alimentation, et de changer son fusil d’épaule.
Ainsi, Buisness Objects Data Integrator propose en alternative un DROP and RECREATE vraiement efficace.
Il aussi est possible de créer et appeler un code SQL faisant l’opération, que ce soit du TRUNCATE SQL ou DROP and RECREATE.

Publicités

Poster un commentaire

Classé dans DATA, ETL

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s