Oracle Partitioning "ON DELETE CASCADE em FKs"

Por Rodrigo Mufalani Oracle ACE e David Siqueira Oracle ACE
Postado em Julho 2016

Revisado por Marcelo Pivovar - Solution Architect

Neste artigo iremos discutir um dos grandes desafios enfrentados pelos DBAs que é o problema de design de um banco de dados. O Oracle Partitioning é uma "option" (significa que precisa ser licenciada a parte), que foi introduzida no Oracle 8. Essa funcionalidade é utilizada em larga escala por diversas empresas globais e teve diversos aprimoramentos ao longo dos anos.

Particionamento é uma técnica que permite o DBA ou AD do banco dividir um tabela em diversos segmentos e ainda dividir novamente em caso de necessidade usando subpartions para melhorar desempenho, flexibilidade e gerenciamento sobre dados em uma grande tabela.

Se o seu banco de dados é grande, a Oracle recomenda que todas as tabelas maiores de 2Gb sejam particionadas para se ter um melhor aproveitamento do recursos no banco de dados, esse tamanho pode depender de uma série de fatores, então cada ambiente pode ter uma necessidade diferente. Sugiro uma leitura no manual abaixo sobre VLDB que cobre tudo ou quase tudo que você deve saber para usar essa funcionalidade.

https://docs.oracle.com/database/121/VLDBG/title.htm

Como está descrito nesse manual, no Oracle 12c podemos particionar tabelas por:

  • RANGE - Permite particionar a tabela baseando-se em um range de valores, normalmente usado em colunas de tipo DATE e raramente usado em NUMBER
  • LIST - Permite particionar a tabela quando se tem uma lista definida de valores em colunas de tipo NUMBER e VARCHAR2
  • HASH - Permite particionar dados quando baseando-se em um algoritmo HASH que o Oracle aplica em uma coluna definida pelo DBA como chave do particionamento. Geralmente é usada quando não tem uma lista ou range de valores predefinidos impossibilitando o uso dos outros dois tipos.

Agora que já conhecemos um pouco mais dessa funcionalidade vamos expor um desafio que tive de solucionar recentemente.

Há alguns dias eu tive de resolver um problema em um cliente que queria fazer drop de algumas partições antigas, porém estava tomando erro ao tentar remover os dados. Ele tinha algumas tabelas filhas, com integridade referencial ligada sem a opção do ON DELETE CASCADE, como a tabela "PAI" teria registros antigos com a operação de DROP PARTITION o Oracle alarmava um erro ORA-02266: unique/primary key in table referenced by enabled foreign keys

Achei a resposta neste manual abaixo:

http://docs.oracle.com/database/121/VLDBG/GUID-D2FC4255-EF84-4AEC-889C-F7D4B555A75F.htm

Optamos pelo método 2 usando um delete, visto que  o volume dos dados não era tão significativo assim. Note que tem um alerta para isso na documentação, para deletar um volume considerável é melhor usar outra técnica.

Veremos agora o que ocorria no ambiente do cliente, iremos simular aqui:

1. Vamos iniciar criando duas tabelas conforme abaixo (a tabela filho não necessariamente precisa ser particionada, basta ter uma fk), além disso iremos criar as chaves primárias e estrangeiras apenas para a nossa simulação.

CREATE TABLE USUARIOS
 ( 
"NOME" VARCHAR2(20 BYTE), 
"MATRICULA" NUMBER, 
"DT_NASC" DATE
 ) 
 PARTITION BY RANGE ("DT_NASC") 
 (
PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P2" VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P3" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P4" VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P5" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P6" VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P7" VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P8" VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P9" VALUES LESS THAN (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P10" VALUES LESS THAN (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION "P11" VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 );

ALTER TABLE USUARIOS ADD CONSTRAINT PK_USUARIO PRIMARY KEY (NOME);

CREATE TABLE USUARIO_PERFIL
 (
NOME VARCHAR2(20),
PERFIL VARCHAR2(20)
 );


Obs.:  Note que a FK esta criada sem DELETE CASCADE, isso vai forçar o Oracle a tentar violar integridade referencial e é a razão do erro que o cliente estava enfrentando.

ALTER TABLE USUARIO_PERFIL 
ADD CONSTRAINT FK1_NODELCASC FOREIGN KEY (NOME)
REFERENCES USUARIOS (NOME) ;


Abaixo fazemos algumas cargas de dados apenas para nossa demonstração, nenhum dado aqui é real ou de produção, tanto que usei o nome para PK, em um ambiente real creio que uma chave primária não poderia ser criada com esse tipo de conteúdo.

INSERT INTO USUARIOS VALUES ('ISAQUE',1,TO_DATE('01/06/89','DD/MM/RR'));
INSERT INTO USUARIOS VALUES ('BRUNO'  ,2,TO_DATE('01/06/14','DD/MM/RR'));
INSERT INTO USUARIOS VALUES ('CRISTIANE',3,TO_DATE('01/05/13','DD/MM/RR'));
INSERT INTO USUARIOS VALUES ('GUI'    ,4,TO_DATE('01/03/10','DD/MM/RR'));
INSERT INTO USUARIOS VALUES ('MICHELLE' ,5,TO_DATE('01/03/11','DD/MM/RR'));
INSERT INTO USUARIOS VALUES ('DAVI'   ,6,TO_DATE('01/02/16','DD/MM/RR'));

INSERT INTO USUARIO_PERFIL VALUES ('BRUNO','TESTE');
INSERT INTO USUARIO_PERFIL VALUES ('ISAQUE','TESTE');
INSERT INTO USUARIO_PERFIL VALUES ('CRISTIANE','TESTE');
INSERT INTO USUARIO_PERFIL VALUES ('GUI','TESTE');
INSERT INTO USUARIO_PERFIL VALUES ('MICHELLE','TESTE');
INSERT INTO USUARIO_PERFIL VALUES ('DAVI','TESTE');
 

2. Agora vamos tentar fazer tentar eliminar alguma partição com dados P1, P2, P3 P5, P6 e P9

SQL> ALTER TABLE USUARIOS DROP PARTITION P2 UPDATE INDEXES; 

Erro a partir da linha : 1 no comando -
ALTER TABLE USUARIOS DROP PARTITION P2 UPDATE INDEXES
Relatório de erros -
Erro de SQL: ORA-02266: chaves exclusiva/primária na tabela referenciadas por chaves externas ativadas
02266. 00000 -  "unique/primary keys in table referenced by enabled foreign keys"

*Cause: An attempt was made to truncate a table with unique or
       primary keys referenced by foreign keys enabled in another table.
       Other operations not allowed are dropping/truncating a partition of a
       partitioned table or an ALTER TABLE EXCHANGE PARTITION.
 
*Action:Before performing the above operations the table, disable the
       foreign key constraints in other tables. You can see what
       constraints are referencing a table by issuing the following
       command:
       SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
 

Se fizermos o drop em uma partição que não contenha dados referenciados pela tabela filho, a manutenção da exclusão da partição ocorre sem problemas:

SQL> ALTER TABLE USUARIOS DROP PARTITION P4 UPDATE INDEXES;
Table USUARIOS alterado.
 

3. Agora iremos recriar a constraint referencial com ON DELETE CASCADE

ALTER TABLE USUARIO_PERFIL DROP CONSTRAINT FK1_NODELCASC;
Table USUARIO_PERFIL alterado.
ALTER TABLE USUARIO_PERFIL
ADD CONSTRAINT FK1_DELCASC FOREIGN KEY (NOME)
REFERENCES USUARIOS (NOME) ON DELETE CASCADE ;
Table USUARIO_PERFIL alterado.


4. Iremos refazer o teste agora, porém seguindo a nota da Oracle, antes iremos deletar os dados na tabela PAI, como a chave referencial está criada para deletar em cascata, não teremos mais problemas ao remover quaisquer partições em minha tabela.

SQL> DELETE FROM USUARIOS PARTITION (P2);
1 linha excluído.
SQL> ALTER TABLE USUARIOS DROP PARTITION P2 UPDATE INDEXES; 
Table USUARIOS alterado.
 

Essa solução resolveu o problema que encontramos, já que não existe um comando DROP PARTITION XXXXXX CASCADE CONSTRAINTS ou algo do tipo e espero que possa lhe ajuda caso encontre um problema semelhante em seu ambiente.

 


David Siqueira é DBA desde 2001, atuante no mercado de São Paulo Brasil, trabalhou nas principais consultorias sempre buscando melhorar conhecimentos e agregar valor aos ambientes por onde passou, é OCP 10 e 11g, OCE SQL Expert, OCE RAC 10g, OCE Exadata Essentials e foi nomeado Oracle ACE em Dezembro de 2011. Atua com ambientes de Alta Disponibilidade Oracel RAC 11g, Exadata X2-2 e Administração de Banco de Dados em Geral. Também possui conhecimentos em sistemas operacionais Oracle VM server e Oracle Businnes Intelligence.

Rodrigo Mufalani é DBA Sr. com mais de 10 anos de experiência, começou com o Oracle 8i, mas teve a oportunidade de dar suporte a Oracle 7.3.4 em diante. É especialista em banco de dados Oracle com foco principal em Performance & Tuning e RAC. É palestrante em eventos de Oracle como: OTN LAD TOUR e outros. Atualmente trabalha como consultor diversas empresas no segmento de variados ramos como: Educação, Saúde, Tecnologia, Seguros e etc. Foi o terceiro Oracle ACE a ser nomeado no Brasil e é OCP DBA nas versões 10g e 11g.

 

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.