Solución:
No.
TRUNCATE y DROP son casi idénticos en comportamiento y velocidad, por lo que hacer un TRUNCATE justo antes de un DROP es simplemente innecesario.
Nota: escribí esta respuesta desde una perspectiva de SQL Server y asumí que se aplicaría igualmente a Sybase. Parece que este no es del todo el caso.
Nota: Cuando publiqué esta respuesta por primera vez, hubo varias otras respuestas altamente calificadas, incluida la respuesta entonces aceptada, que hicieron varias afirmaciones falsas como: TRUNCATE no está registrado; TRUNCATE no se puede revertir; TRUNCATE es más rápido que DROP; etc.
Ahora que se ha limpiado este hilo, las refutaciones que siguen pueden parecer tangenciales a la pregunta original. Los dejo aquí como referencia para otros que buscan desacreditar estos mitos.
Hay un par de falsedades populares, generalizadas incluso entre los administradores de bases de datos experimentados, que pueden haber motivado esto TRUNCATE-then-DROP patrón. Son:
-
Mito:
TRUNCATEno se registra, por lo tanto, no se puede revertir. -
Mito:
TRUNCATEes más rápido queDROP.
Déjame refutar estas falsedades. Estoy escribiendo esta refutación desde la perspectiva de SQL Server, pero todo lo que digo aquí debería ser igualmente aplicable a Sybase.
TRUNCAR es registrado, y pueden ser revertido.
-
TRUNCATEes una operación registrada, por lo que pueden ser revertido. Simplemente envuélvalo en una transacción.USE [tempdb]; SET NOCOUNT ON; CREATE TABLE truncate_demo ( whatever VARCHAR(10) ); INSERT INTO truncate_demo (whatever) VALUES ('log this'); BEGIN TRANSACTION; TRUNCATE TABLE truncate_demo; ROLLBACK TRANSACTION; SELECT * FROM truncate_demo; DROP TABLE truncate_demo;Sin embargo, tenga en cuenta que esto no es cierto para Oracle. Aunque está registrado y protegido por la funcionalidad de deshacer y rehacer de Oracle,
TRUNCATEy otras declaraciones DDL hipocresía ser revertido por el usuario porque Oracle emite confirmaciones implícitas inmediatamente antes y después de todas las declaraciones DDL. -
TRUNCATEestá mínimamente registrado, en contraposición al registro completo. ¿Qué significa eso? Dices tuTRUNCATEuna mesa. En lugar de poner cada fila eliminada en el registro de transacciones,TRUNCATEsimplemente marca las páginas de datos en las que viven como no asignadas. Por eso es tan rápido. También es por eso que no puede recuperar las filas de unTRUNCATE-ed tabla del registro de transacciones utilizando un lector de registros. Todo lo que encontrará allí son referencias a las páginas de datos desasignados.Compare esto con
DELETE. Si tuDELETEtodas las filas de una tabla y confirmar la transacción; en teoría, aún puede encontrar las filas eliminadas en el registro de transacciones y recuperarlas desde allí. Eso es porqueDELETEescribe cada fila eliminada en el registro de transacciones. Para tablas grandes, esto hará que sea mucho más lento queTRUNCATE.
DROP es tan rápido como TRUNCATE.
- Igual que
TRUNCATE,DROPes una operación mínimamente registrada. Eso significaDROPtambién se puede revertir. Eso también significa que funciona exactamente de la misma manera queTRUNCATE. En lugar de eliminar filas individuales,DROPmarca las páginas de datos correspondientes como no asignadas y, además, marca los metadatos de la tabla como eliminados. -
Porque
TRUNCATEyDROPfuncionan exactamente de la misma manera, funcionan tan rápido como el otro. No tiene sentidoTRUNCATE-comer una mesa antesDROP-haciéndolo. Ejecute este script de demostración en su instancia de desarrollo si no me cree.En mi máquina local con un caché caliente, los resultados que obtengo son los siguientes:
table row count: 134,217,728 run# transaction duration (ms) TRUNCATE TRUNCATE then DROP DROP ========================================== 01 0 1 4 02 0 39 1 03 0 1 1 04 0 2 1 05 0 1 1 06 0 25 1 07 0 1 1 08 0 1 1 09 0 1 1 10 0 12 1 ------------------------------------------ avg 0 8.4 1.3Entonces, para un 134 millón tabla de filas ambos
DROPyTRUNCATEefectivamente, no tome tiempo en absoluto. (En un caché frío, tardan entre 2 y 3 segundos en la primera ejecución o dos). También creo que la duración media más alta de laTRUNCATEluegoDROPLa operación es atribuible a variaciones de carga en mi máquina local y no porque la combinación es de alguna manera mágicamente un orden de magnitud peor que las operaciones individuales. Después de todo, son casi exactamente lo mismo.Si está interesado en obtener más detalles sobre la sobrecarga de registro de estas operaciones, Martin tiene una explicación sencilla al respecto.
Pruebas TRUNCATE luego DROP vs solo hacer el DROP muestra directamente que el primer enfoque en realidad tiene una sobrecarga de registro ligeramente mayor, por lo que incluso puede ser ligeramente contraproducente.
Mirar los registros individuales muestra la TRUNCATE ... DROP la versión es casi idéntica a la DROP versión excepto tiene estas entradas adicionales.
+-----------------+---------------+-------------------------+
| Operation | Context | AllocUnitName |
+-----------------+---------------+-------------------------+
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_LOCK_XACT | LCX_NULL | NULL |
+-----------------+---------------+-------------------------+
Entonces el TRUNCATE La primera versión termina desperdiciando un poco de esfuerzo al realizar algunas actualizaciones en varias tablas del sistema de la siguiente manera
- Actualizar
rcmodifiedpara todas las columnas de la tabla ensys.sysrscols - Actualizar
rcrowsensysrowsets - Cero fuera
pgfirst,pgroot,pgfirstiam,pcused,pcdata,pcreservedensys.sysallocunits
Estas filas de la tabla del sistema solo se eliminan cuando la tabla se elimina en la siguiente declaración.
Un desglose completo de la tala realizada por TRUNCATE vs DROP Esta abajo. También he agregado DELETE en para fines de comparación.
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| | | | Bytes | Count |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Operation | Context | AllocUnitName | Truncate / Drop | Drop Only | Truncate Only | Delete Only | Truncate / Drop | Drop Only | Truncate Only | Delete Only |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| LOP_BEGIN_XACT | LCX_NULL | | 132 | 132 | 132 | 132 | 1 | 1 | 1 | 1 |
| LOP_COMMIT_XACT | LCX_NULL | | 52 | 52 | 52 | 52 | 1 | 1 | 1 | 1 |
| LOP_COUNT_DELTA | LCX_CLUSTERED | System Table | 832 | | 832 | | 4 | | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | System Table | 2864 | 2864 | | | 22 | 22 | | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | T | | | | 8108000 | | | | 1000 |
| LOP_HOBT_DDL | LCX_NULL | | 108 | 36 | 72 | | 3 | 1 | 2 | |
| LOP_LOCK_XACT | LCX_NULL | | 336 | 296 | 40 | | 8 | 7 | 1 | |
| LOP_MODIFY_HEADER | LCX_PFS | Unknown Alloc Unit | 76 | 76 | | 76 | 1 | 1 | | 1 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | System Table | 644 | 348 | 296 | | 5 | 3 | 2 | |
| LOP_MODIFY_ROW | LCX_IAM | T | 800 | 800 | 800 | | 8 | 8 | 8 | |
| LOP_MODIFY_ROW | LCX_PFS | T | 11736 | 11736 | 11736 | | 133 | 133 | 133 | |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 92 | 92 | 92 | | 1 | 1 | 1 | |
| LOP_SET_BITS | LCX_GAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_IAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_PFS | System Table | 896 | 896 | | | 16 | 16 | | |
| LOP_SET_BITS | LCX_PFS | T | | | | 56000 | | | | 1000 |
| LOP_SET_BITS | LCX_SGAM | Unknown Alloc Unit | 168 | 224 | 168 | | 3 | 4 | 3 | |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Total | | | 36736 | 35552 | 32220 | 8164260 | 456 | 448 | 406 | 2003 |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
La prueba se realizó en una base de datos con modelo de recuperación total contra una tabla de 1000 filas con una fila por página. La tabla consume 1.004 páginas en total debido a la página de índice raíz y 3 páginas de índice de nivel intermedio.
8 de estas páginas son asignaciones de una sola página en extensiones mixtas y el resto se distribuye en 125 extensiones uniformes. Las 8 desasignaciones de una sola página se muestran como las 8 LOP_MODIFY_ROW,LCX_IAM entradas de registro. Las 125 desasignaciones de extensiones como LOP_SET_BITS LCX_GAM,LCX_IAM. Ambas operaciones también requieren una actualización del asociado PFS página de ahí el combinado 133 LOP_MODIFY_ROW, LCX_PFS entradas. Luego, cuando la tabla realmente se descarta, los metadatos sobre ella deben eliminarse de varias tablas del sistema, de ahí la tabla del sistema 22 LOP_DELETE_ROWS entradas de registro (contabilizadas de la siguiente manera)
+----------------------+--------------+-------------------+-------------------+
| Object | Rows Deleted | Number of Indexes | Delete Operations |
+----------------------+--------------+-------------------+-------------------+
| sys.sysallocunits | 1 | 2 | 2 |
| sys.syscolpars | 2 | 2 | 4 |
| sys.sysidxstats | 1 | 2 | 2 |
| sys.sysiscols | 1 | 2 | 2 |
| sys.sysobjvalues | 1 | 1 | 1 |
| sys.sysrowsets | 1 | 1 | 1 |
| sys.sysrscols | 2 | 1 | 2 |
| sys.sysschobjs | 2 | 4 | 8 |
+----------------------+--------------+-------------------+-------------------+
| | | | 22 |
+----------------------+--------------+-------------------+-------------------+
Guión completo a continuación
DECLARE @Results TABLE
(
Testing int NOT NULL,
Operation nvarchar(31) NOT NULL,
Context nvarchar(31) NULL,
AllocUnitName nvarchar(1000) NULL,
SumLen int NULL,
Cnt int NULL
)
DECLARE @I INT = 1
WHILE @I <= 4
BEGIN
IF OBJECT_ID('T','U') IS NULL
CREATE TABLE T(N INT PRIMARY KEY,Filler char(8000) NULL)
INSERT INTO T(N)
SELECT DISTINCT TOP 1000 number
FROM master..spt_values
CHECKPOINT
DECLARE @allocation_unit_id BIGINT
SELECT @allocation_unit_id = allocation_unit_id
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.hobt_id = a.container_id
WHERE p.object_id = object_id('T')
DECLARE @LSN NVARCHAR(25)
DECLARE @LSN_HEX NVARCHAR(25)
SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null)
SELECT @LSN_HEX=
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)
BEGIN TRAN
IF @I = 1
BEGIN
TRUNCATE TABLE T
DROP TABLE T
END
ELSE
IF @I = 2
BEGIN
DROP TABLE T
END
ELSE
IF @I = 3
BEGIN
TRUNCATE TABLE T
END
ELSE
IF @I = 4
BEGIN
DELETE FROM T
END
COMMIT
INSERT INTO @Results
SELECT @I,
CASE
WHEN GROUPING(Operation) = 1 THEN 'Total'
ELSE Operation
END,
Context,
CASE
WHEN AllocUnitId = @allocation_unit_id THEN 'T'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
ELSE AllocUnitName
END,
COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
COUNT(*) AS Cnt
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
GROUP BY GROUPING SETS((Operation, Context,
CASE
WHEN AllocUnitId = @allocation_unit_id THEN 'T'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
ELSE AllocUnitName
END),())
SET @I+=1
END
SELECT Operation,
Context,
AllocUnitName,
AVG(CASE WHEN Testing = 1 THEN SumLen END) AS [Truncate / Drop Bytes],
AVG(CASE WHEN Testing = 2 THEN SumLen END) AS [Drop Bytes],
AVG(CASE WHEN Testing = 3 THEN SumLen END) AS [Truncate Bytes],
AVG(CASE WHEN Testing = 4 THEN SumLen END) AS [Delete Bytes],
AVG(CASE WHEN Testing = 1 THEN Cnt END) AS [Truncate / Drop Count],
AVG(CASE WHEN Testing = 2 THEN Cnt END) AS [Drop Count],
AVG(CASE WHEN Testing = 3 THEN Cnt END) AS [Truncate Count],
AVG(CASE WHEN Testing = 4 THEN Cnt END) AS [Delete Count]
FROM @Results
GROUP BY Operation,
Context,
AllocUnitName
ORDER BY Operation, Context,AllocUnitName
DROP TABLE T
De acuerdo, pensé en intentar hacer algunos puntos de referencia que no se basaran en ningún “almacenamiento en caché en caliente” para que, con suerte, fueran una prueba más realista (también usando Postgres, para ver si coincide con las mismas características de otras respuestas publicadas) :
Mis puntos de referencia usando postgres 9.3.4 con una base de datos de gran tamaño (con suerte, lo suficientemente grande como para no caber en la memoria caché de RAM):
Usando este script de base de datos de prueba: https://gist.github.com/rdp/8af84fbb54a430df8fc0
con 10M filas:
truncate: 1763ms
drop: 2091ms
truncate + drop: 1763ms (truncate) + 300ms (drop) (2063ms total)
drop + recreate: 2063ms (drop) + 242ms (recreate)
con 100 millones de filas:
truncate: 5516ms
truncate + drop: 5592ms
drop: 5680ms (basically, the exact same ballpark)
Entonces, a partir de esto, supongo lo siguiente: soltar es “aproximadamente” tan rápido (o más rápido) como truncar + soltar (al menos para las versiones modernas de Postgres), sin embargo, si planea también dar la vuelta y recrear la tabla, puede hacerlo Bueno, quédate con hacer un truncado recto, que es más rápido que soltar + recrear (tiene sentido). FWIW.
nota 1: https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886 (dice que postgres 9.2 puede tener un truncado más rápido que las versiones anteriores). Como siempre, compare con su propio sistema para ver sus características.
nota 2: truncar se puede revertir en postgres, si en una transacción: http://www.postgresql.org/docs/8.4/static/sql-truncate.html
nota 3: truncar puede, con tablas pequeñas, a veces ser más lento que una eliminación: https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886