Semelhanças e Diferenças entre DELETE, TRUNCATE e DROP TABLE

Olá, leitores!
Tudo bem ?

Hoje vamos falar sobre uma dúvida clássica de desenvolvedores e alguns DBA’s que estão começando agora. Afinal, qual a diferença entre DELETE, TRUNCATE TABLE e DROP TABLE ? Quando devemos usar cada um destes comandos ?

Para ilustrar essa diferença, vou apresentar a tabela abaixo:

	DELETE	TRUNCATE	DROP
Apaga os dados de uma tabela
É uma operação de DDL (Exigindo privilégios para tal)
É uma operação de DML (Exigindo privilégios para tal)
Exige bloqueio exclusivo do objeto para ser executado (Nenhuma outra sessão pode estar utilizando esse objeto)
Bloqueia o objeto a nível de linha
Bloqueia o objeto a nível de schema
Durante a execução do comando, outras sessões podem fazer leituras no objeto usando HINTS para leitura “suja” (Ex: NOLOCK)
Geralmente, em tabelas muito grandes e com muita utilização, pode gerar LOCKS e contenções perceptíveis no banco
Permite selecionar quais dados serão apagados (Não apaga a tabela toda)
Apaga também a estrutura da tabela, metadados, índices, FK’s, PK. Elimina o objeto do banco de dados.
Reinicia o auto incremento, recria os índices e desfragmenta a tabela
Gera dados na transaction log / Redo log (Oracle), permitindo que os dados sejam restaurados usando backup de log
Comandos extremamente rápidos de serem executados
É possível executar o comando, mesmo que a tabela em questão seja FK de outra tabela
Possibilidade de utilização em conjunto com Triggers
Pode ser utilizado em tabelas que são partes de views indexadas (MSSQL)

Alguns pontos relevantes

• O comando DROP TABLE apaga a tabela e sua estrutura. O objeto será eliminado do banco. Esse comando não apaga apenas os dados
• Os comandos DROP TABLE e TRUNCATE TABLE não geram logs detalhados das operações. Eles apenas gravam que o comando foi executado e as páginas afetadas. Por isso, eles ocupam pouquíssimo espaço na transaction log / redo log e são executados tão rapidamente. O lado ruim disso, é que se um dia você precisar fazer um restore imediato usando o log do banco após alguém ter feito um TRUNCATE TABLE, isso não será possível. O comando DELETE, log cada linha que foi deletada, gerando uma quantidade de registros de log muito grande (Devido à cláusula WHERE), dependendo do tamanho da tabela. Isso permite que você possa realizar um restore imediato após alguém ter realizado um DELETE errado, mas pode estourar sua transaction log / redo log se estiver apagando uma quantidade de registros muito grande
• O comando TRUNCATE TABLE apaga TODOS os registros de uma tabela. Além disso, ele reinicia o auto incremento (se houver), reduz a fragmentação da tabela e índices para 0, quase não gera log e é executado rapidamente no banco, mesmo com tabelas muito grandes. Para rotinas onde todos os dados são apagados e gerados novamente a cada execução, é a solução mais recomendada
• Uma vez que o TRUNCATE simplesmente exclui todas as páginas e extensões de uma tabela, não seria possível validar se algum desses registros é referenciado por alguma tabela filha. O DELETE loga linha a linha e caso não haja violação de integridade referencial é possível utilizá-lo mesmo em tabelas referenciadas. A opção CASCADE é capaz de propagar as atualizações para o DELETE, mas não para o TRUNCATE uma vez que esse comando não mantém a relação de linhas afetadas e não é portanto capaz de propagar seus efeitos
• No Oracle Database, existe uma trigger que é disparada no evento “AFTER TRUNCATE ON Database”, que pode ser utilizada após algum comando de TRUNCATE, para logar qual o usuário que executou o comando, por exemplo. Mas não existe trigger específica para antes da execução do comando. Isso pode ser criado utilizando uma trigger que é disparada no evento “BEFORE DDL ON Database”, mas não é uma solução “oficial”
• As views indexadas materializam dados de uma tabela ou de várias tabelas combinadas. Se o TRUNCATE fosse executado em uma tabela participante, a view indexada simplesmente ficaria inválida, pois os comandos de exclusão individuais não seriam logados e uma falha não permitiria que o banco se recuperasse (não haveria tracking das alterações) para refazer o índice da view
• Por se tratarem de operações diferentes (DML x DDL), os privilégios exigidos para execução do comando DELETE é diferente dos necessários para DROP TABLE e TRUNCATE TABLE
• Os comandos DROP TABLE e TRUNCATE são praticamente idênticos em todas as comparações. A única diferença entre ambos, é que o DROP TABLE apaga os objetos e metadados do banco, enquanto o TRUNCATE TABLE apenas deixa a tabela vazia (sem registros)

Exemplos de utilização

Espero ter tirado todas as suas dúvidas sobre esse assunto.
Caso ainda tenha dúvidas, deixe o seu comentário aqui embaixo que irei respondê-la 🙂