Abordando o TRUNCATE TABLE CASCADE do Oracle 12c

Olá,

Possivelmente quase todo desenvolvedor ou mesmo DBA, já se deparou com a situação na qual executar o comando DDL (TRUNCATE TABLE) em algumas tabelas seria muito mais rápido e prático do que executar o comando DML (DELETE) nessas mesmas tabelas. Isso porque o comando DELETE além de gerar dados de UNDO, poderia tomar um tempo considerável dependendo do número de linhas nas tabelas envolvidas. Já o comando TRUNCATE, além de não gerar dados de UNDO, é praticamente instantâneo, pois apenas a (HWM) (High Water Mark) da tabela é afetada. No Oracle, caso queiramos truncar tabelas que estão sendo referenciadas através de chaves estrangeiras (Foreign Keys) em outras tabelas, precisaremos antes de mais nada, desabilitar essas constraints antes da execução do comando TRUNCATE. Como exemplo, poderíamos utilizar o resultado gerado pelas sentenças SQL abaixo para realizar essa operação.

-- Habilitar todas as constraints foreign key do usuário
select 'alter table '|| table_name ||' enable constraints '|| constraint_name||';'
from user_constraints
where constraint_type='R';

-- Desabilitar todas as constraints foreign key do usuário
select 'alter table '|| table_name ||' disable constraints '|| constraint_name||';'
from user_constraints
where constraint_type='R';

Com o resultado das instruções acima, poderemos desabilitar as constraints (FK), executar os comandos TRUNCATE nas tabelas, e então habilitar novamente as constraints (FK). No Oracle 12c, uma das novidades lançada, inclusive mencionada no artigo de Junho/2013, se refere à inclusão da cláusula CASCADE no comando TRUNCATE TABLE, conforme figura abaixo:

Bom, eu acredito que mesmo no Oracle 12c, os comandos SQL apresentados mais acima continuarão sendo úteis. Imagine um modelo de dados onde temos uma tabela MASTER e outra tabela DETAIL. A tabela DETAIL possui uma foreign key (NO ACTION) para a tabela MASTER. No Oracle 12c, se tentarmos truncar a tabela MASTER utilizando a cláusula CASCADE, o seguinte erro irá ocorrer:

SQL> truncate table master CASCADE;
truncate table master CASCADE
               *
ERRO na linha 1:
ORA-14705: chaves exclusiva ou primaria na tabela referenciadas por chaves
externas ativadas na tabela "SCOTT"."DETAIL" 

O erro acima ocorreu porque a foreign key definida na tabela DETAIL não era do tipo (ON DELETE CASCADE). Portanto, conclui-se que somente foreign keys que sejam do tipo (ON DELETE CASCADE) poderão ser afetadas pela cláusula CASCADE do comando TRUNCATE TABLE do Oracle 12c. No mais, segue abaixo um cenário de demonstração.
 

Na figura acima temos um modelo de dados contendo 4 tabelas e seus relacionamentos. O objetivo será truncar as 4 tabelas apenas executando o comando TRUNCATE TABLE ... CASCADE na tabela TABELA_A.

C:\>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.1.0 Production on Seg Set 2 21:02:55 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Horário do último log-in bem-sucedido: Seg Set 2 2013 21:02:48 -03:00

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select table_name, constraint_name, delete_rule
  2    from user_constraints
  3   where constraint_type = 'R';

TABLE_NAME           CONSTRAINT_NAME    DELETE_RU
-------------------- ------------------ ---------------
TABELA_B             FK_B_A             CASCADE
TABELA_C             FK_C_A             CASCADE  
TABELA_D             FK_D_C             CASCADE

No resultado acima, podemos verificar que as constraints foreign keys existentes nas tabelas B, C e D são do tipo (ON DELETE CASCADE). Abaixo, irei popular alguns registros nas tabelas.

SQL> insert into tabela_a values (1);

1 linha criada.

SQL> insert into tabela_b values (1);

1 linha criada.

SQL> insert into tabela_c values (1);

1 linha criada.

SQL> insert into tabela_d values (1);

1 linha criada.

SQL> commit;

Commit concluído.

Caso eu tente truncar a tabela TABELA_A sem utilizar a cláusula CASCADE, o erro ORA-02266 será emitido conforme demonstrado abaixo:

SQL> truncate table TABELA_A;
truncate table TABELA_A
               *
ERRO na linha 1:
ORA-02266: chaves exclusiva/primária na tabela referenciadas por chaves
externas ativadas

Para que a tabela TABELA_A e as tabelas dependentes sejam truncadas, bastará utilizar a cláusula CASCADE conforme demonstrado abaixo:

SQL> truncate table TABELA_A CASCADE:

Tabela truncada.

SQL> select * from TABELA_A;

não há linhas selecionadas

SQL> select * from TABELA_B;

não há linhas selecionadas

SQL> select * from TABELA_C;

não há linhas selecionadas

SQL> select * from TABELA_D;

não há linhas selecionadas