Category Archives: Relationships

A script to DELETE records recursively from all child tables

Português/English

In the last months I’ve been working for a client that has a very complex ER model, with about a thousand tables and several thousand of relationships, in many levels of depth. And there are a lot of relationships that cycles the model. For example, we have some situations like this: table A is parent of B, B is parent of C, C is parent of D, and D is parent of A. It means that table D, which is at the third level of dependency of table A, is at the same time parent of table A. I know it sounds weird, but the situation exists. By the way, the model is being reviewed.

Another issue they have is a lot of “trash” data that should be deleted. Old products for example, that came from another implementation of the application but were not cleaned properly.

So, for example, there is a need for deleting some products, but they have many child records in many tables, and each of these child records has its own child records in other tables, and so on.

No, the foreign keys are not “ON DELETE CASCADE”, and it could be a security risk to change all them just to delete some old products.

Then I was asked to develop a way to delete the unwanted data from a table, and all child records in all related tables recursively.

I browsed the Internet but couldn’t find any good solution. So I developed the procedure I present here.

It makes use of recursiveness to navigate throughout the model. For each child constraint it finds that has child records to delete, the procedure recursively calls itself passing the new found constraint, and so on until no more child data are found.

The statements created to delete records are built in the form ‘WHERE constraint_columns IN (SELECT … FROM parent table WHERE …)‘. So for a table in a deep level, you will see the final statement with several nested subqueries with this IN construction.

At first, the procedure does not delete any record. It just identifies the tables and records that should be deleted, and stores the statements to delete them in a table. After this step is done, it can then run the statements to really delete the data in the right order for not to have “parent records found” errors.

Below I present all the parameters that the procedure can accept. Please note that some of them are not intended to be passed in the execution, but are used instead for the recursive calls. Also note that only three parameters are mandatory and has no default values: p_owner, p_table and p_where:

  • p_owner: the owner of the main table to have records deleted.
  • p_table: the main table to have records deleted.
  • p_constraint: the PK/UK constraint to use as the first level. If NULL, all the PK/UK will be used. This parameter was created more to test the script. In the normal use, you should let it NULL.
  • p_where: the conditions that identifies the records to be deleted. For example ‘STATUS=1’.
  • p_commit: the commit interval (in rows of the main table). The default is 10000.
  • p_mode: the mode of execution (default is ‘GS’), with any combination of:
    • G – generate the statements.
    • S – show the generated statements (you must execute SET SERVEROUTPUT ON before).
    • X – execute the statements.
    • C – commit the deletes.
    • A – all in batch mode (the equivalent of ‘GXC’).
  • p_limit: use it to limit the depth of the search. Just for testing purposes.
  • p_level: (RECURSIVE PARAMETER) the current level of recursiveness.
  • p_parent_owner(RECURSIVE PARAMETER) the parent owner – will be used to build the recursive DELETE statement.
  • p_parent_table(RECURSIVE PARAMETER) the parent table – will be used to build the recursive DELETE statement.
  • p_parent_cols(RECURSIVE PARAMETER) the parent columns – will be used to build the recursive DELETE statement.
  • p_child_cols(RECURSIVE PARAMETER) the child columns – will be used to build the recursive DELETE statement.

In order for the procedure to work, you need first to create the table that will store the statements, and a sequence that will be used to define IDs for each statement (download it here):

create table tmp_delete_cascade_stmt ( id number primary key, lev number, owner varchar2(30), table_name varchar2(30), parent_constraint varchar2(30),  child_constraint varchar2(30), statement clob, rows_deleted number);
create sequence seq_delete_cascade;

If you call the procedure with the ‘G’ mode, then it first empties the table and then generates and store the new statements generated.

When using ‘S’ mode, the procedure shows all the statements generated. Please note you must issue SET SERVEROUTPUT ON in your SQL*Plus session in order to view the results. Alternatively, you could directly query the table TMP_DELETE_CASCADE_STMT to check the statements generated.

The ‘X’ mode executes all the statements, and stores in the table the number of rows each statement deleted. But it DOES NOT COMMIT any change. It is intentional, for you to have the opportunity to check if the deleted data is what you wanted, and only after confirming that you should issue the COMMIT.

If you use the ‘C’ mode (usually together with ‘X’) then the procedure commits the changes without asking for any confirmation. It makes partial commits for each 10000 rows of the main table, or the number you pass in the p_commit parameter.

You can execute the procedure step by step, first executing with ‘GS’ modes to see what is generated, then ‘X’ to execute and then ‘C’ to commit (or just issue the COMMIT by yourself), or you can do it all at once, by passing ‘GSXC’ or just ‘A’.

The procedure code can be downloaded from here.

CAUTION: please be very careful when using this script. As explained, it will DELETE all the dependant rows in all related tables. So, it is preferable to execute it first in mode ‘G’, then ‘X’ and only commit the changes after checking the results carefully. USE AT YOUR OWN RISK. It was not exhaustively tested in all possible situations and may have bugs!

If you find something wrong or have any comments, please comment the post and let me know.

So, let me build a sample model in order to show how the procedure works. My sample creates the main table T_PRODUCT and some others around that. It has the following child tables that could have records deleted when trying to delete from the main:

  • T_PRODUCT_WAREHOUSE: the warehouses that keep each product in stock.
  • T_INVOICE_ITEM: the items of each invoice.
  • T_INVOICE_ITEM_DELIVERY: the delivery of the items invoice (items could be delivered partially).
  • T_PRODUCT: it is a self-relationship to relate a product with other equivalent one.

You can download the sample from here.

-- Generate, execute and show (but don't COMMIT yet) the DELETE statements
begin
	P_DELETE_CASCADE(
		p_owner=>user,
		p_table=>'T_PRODUCT',
		p_where=>'product_id in (1,6)',
		p_mode => 'GX'
		);
end;
/

PL/SQL procedure successfully completed.
-- Verify the statements and the number of rows affected by each one
col statement for a80 word_wrap
select table_name, statement, rows_deleted from tmp_delete_cascade_stmt order by lev desc, id;

TABLE_NAME                          STATEMENT                                                                        ROWS_DELETED
----------------------------------- -------------------------------------------------------------------------------- ------------
T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_INVOICE_ITEM_DELIVERY             DELETE FROM EDUARDO.T_INVOICE_ITEM_DELIVERY WHERE (INVOICE_ID, PRODUCT_ID) in               4
                                    (SELECT
                                    INVOICE_ID, PRODUCT_ID FROM EDUARDO.T_INVOICE_ITEM WHERE
                                    (PRODUCT_ID) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE (PRODUCT_EQUIVALENT) in (SELECT                         1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            3
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       4
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE product_id in (1,6)                                     2

7 rows selected.
-- Check which records remain
select * from T_PRODUCT;

PRODUCT_ID PRODUCT_NAME         PROVIDER_ID PROVIDER_PRODUCT_ID PRODUCT_EQUIVALENT
---------- -------------------- ----------- ------------------- ------------------
         3 Product 3                      1                9992
         4 Product 4                      1                9993
         5 Product 5                      2                9992

select * from T_PRODUCT_WAREHOUSE;

PRODUCT_ID WAREHOUSE_ID PIECES_IN_STOCK
---------- ------------ ---------------
         3            1              11
         4            2               3
         5            2               1

select * from T_INVOICE;

INVOICE_ID INVOICE_DATE
---------- -----------------
         1 26-MAR-2018 12:06
         2 26-MAR-2018 12:06
         3 26-MAR-2018 12:06
         4 26-MAR-2018 12:06

select * from T_INVOICE_ITEM;

INVOICE_ID PRODUCT_ID  QUANTITY
---------- ---------- ---------
         3          4         3
         4          3         5

select * from T_INVOICE_ITEM_DELIVERY;

INVOICE_ID PRODUCT_ID DELIVERY_DATE     QUANTITY_DELIVERED
---------- ---------- ----------------- ------------------
         3          4 26-MAR-2018 12:56                  3

Please note that the Invoices 1 and 2 still exist in the T_INVOICE table, despite there is no related child record in the T_INVOICE_ITEM. This is normal because this rule is not enforced by any constraint, so I must delete them manually.

Also note that the Product 2 was deleted because it was dependant of the Product 1. This is expected and shows the risks of using the script without the proper caution ;-).

After confirming everything is OK, you can COMMIT the changes.

Enjoy!!!!


Um script para apagar dados recursivamente de todas as tabelas filhas

Português/English

Nos últimos meses estou trabalhando em um cliente que tem um model ER bastante complexo, com cerca de mil tabelas e vários milhares de relacionamentos entre elas, em muitos níveis de profundidade. E há vários relacionamentos com referências cíclicas. Por exemplo, a tabela A é pai da B, a B é pai da C, a C é pai da D, e a D é pai da A. Isto significa que a tabela D, que por um relacionamento é bisneta da tabela A, é ao mesmo por outro relacionamento pai da tabela A. Eu sei que isso soa estranho, mas a situação existe. Para conhecimento, o modelo está sendo redesenhado.

Outro problema é que existem muitos dados “inúteis” que deveriam ser apagados. Produtos antigos por exemplo, que vieram de outra implementação da aplicação mas não foram excluídos propriamente.

Então, por exemplo, precisamos apagar alguns produtos, mas eles têm muitos registros filhos em muitas tabelas, e cada um destes filhos tem seus próprios registros filhos em outras tabelas, e assim por diante.

Não, as foreign keys não são “ON DELETE CASCADE”, e seria um risco de segurança alterar todas elas simplesmente para apagar registros antigos.

Então me pediram pra criar uma maneira de excluir os dados desnecessários de uma tabela, e todos os registros dependentes em todas as tabelas filhas, recursivamente.

Eu pesquisei na Internet e não encontrei nenhuma solução satisfatória. Então eu desenvolvi a procedure que apresento aqui.

Ela faz uso de recursividade para navegar através do modelo. Para cada constraint filha que tem registros filhos para apagar, a procedure recursivamente chama ela mesma passando a nova constraint encontrada, e assim por diante até que não encontre mais dados relacionados para apagar.

Os comandos criados para excluir os registros são construídos na forma ‘WHERE constraint_columns IN (SELECT … FROM parent table WHERE …)‘. Então para uma tabela em um nível profundo de dependência, você verá o comando final com vários subqueries aninhadas com esta construção IN.

Inicialmente, a procedure não apaga nenhum registro. Ela apenas identifica as tabelas e registros que devem ser apagados, e registra em uma tabela os comandos necessários para apagá-los. Em seguida os comandos podem ser executados para efetivamente excluir os dados na ordem correta para evitar erros de “registros pais encontrados”.

Abaixo eu apresento todos os parâmetros que a procedure pode aceitar. Note que alguns deles não são foram criados para serem passados na execução , mas nas chamadas recursicas que a própria procedure fará. Note também que somente três parâmetros são obrigatórios e não têm valores defaultp_owner, p_table e p_where:

  • p_owner: o owner da tabela principal que terá os registros excluídos.
  • p_table:a tabela principal que terá os registros excluídos.
  • p_constraint: a constraint PK/UK que será considerada como primeiro nível. Se NULL, todas as PK/UK serão usadas. Este parâmetros foi criado mais para testar o script. No uso normal, você deve deixar NULL.
  • p_where: as condições que identificam os registros a excluir. Por exemplo ‘STATUS=1’.
  • p_commit: o intervalo de commit (em linhas da tabela principal). O default é 10000.
  • p_mode: o modo de execução (default é ‘GS’), com qualquer combinação de:
    • G – gera os comandos.
    • S – mostra os comandos gerados (antes ligue o SET SERVEROUTPUT ON).
    • X – executa os comandos.
    • C – faz o COMMIT.
    • A – tudo em modo batch (o equivalente a ‘GXC’).
  • p_limit: use para limitar a profundidade da pesquisa. Apenas para testes.
  • p_level: (PARÂMETRO RECURSIVO) o nível atual de recursividade.
  • p_parent_owner(PARÂMETRO RECURSIVO) o owner da tabela pai – será usado para construir o DELETE recursivo.
  • p_parent_table(PARÂMETRO RECURSIVO) a tabela pai – será usado para construir o DELETE recursivo.
  • p_parent_cols(PARÂMETRO RECURSIVO) as colunas da tabela pai – será usado para construir o DELETE recursivo.
  • p_child_cols(PARÂMETRO RECURSIVO) as colunas da tabela filha – será usado para construir o DELETE recursivo.

Para que a procedure funcione, você precisa primeiro criar a tabela que vai guardar os comandos, e a sequence que será utilizada para gerar IDs para cada um deles (baixe aqui):

create table tmp_delete_cascade_stmt ( id number primary key, lev number, owner varchar2(30), table_name varchar2(30), parent_constraint varchar2(30),  child_constraint varchar2(30), statement clob, rows_deleted number);
create sequence seq_delete_cascade;

Se você chamar a procedure com o modo ‘G’, então ela primeiro limpa a tabela e depois gera e armazena os comandos gerados.

Ao usar o modo ‘S’, a procedure mostra todos os comandos gerados. Note que você precisa primeiro executar SET SERVEROUTPUT ON em sua sessão SQL*Plus para conseguir ver os resultados. Alternativamente, você pode consultar diretamente a tabela TMP_DELETE_CASCADE_STMT para ver os comandos gerados.

O modo ‘X’ executa os comandos, e salva na tabela o número de linhas que cada um excluiu. Mas ele NÃO FAZ O COMMIT. Isto é intencional, para que você tenha a oportunidade de checar se os dados apagados são os que você esperava, e só fazer o COMMIT depois de confirmar isto.

Se usar o modo ‘C’ mode (normalmente junto com o ‘X’) então a procedure faz o COMMIT das alterações sem pedir qualquer confirmação. Ela faz COMMIT parcial a cada 10000 linhas excluídas da tabela principal, ou o número passado no parâmetro p_commit.

Você pode executar a procedure passo a passo, primeiro com modos ‘GS’ para ver os comandos gerados, depois ‘X’ para executar e ‘C’ para confirmar (ou manualmente executar COMMIT), ou você pode fazer tudo de uma vez passando modos ‘GSXC’ ou apenas ‘A’.

A procedure pode ser baixada daqui.

CUIDADO: seja muito cuidados ao utilizar este script. Como explicado, ele vai APAGAR todas as linhas dependentes em todas as tabelas relacionadas. Então, é melhor executar primeiro com modo ‘G’, depois ‘X’ e só depois de ter certeza que tudo está correto confirmar as alterações. USE POR SUA CONTA E RISCO. Ele não foi testa exaustivamente em todas as situações possíveis e pode ter bugs!

Se você encontrar algo errado or tiver qualquer comentário, por favor comente o post.

Deixe-me então construir um modelo de exemplo para mostrar como a procedure funciona na prática. Meu exemplo cria a tabela principal T_PRODUCT e algumas outras ao redor dela. Ela tem as seguintes tabelas filhas que podem ter registros excluídos quando eu tentar excluir da principal:

  • T_PRODUCT_WAREHOUSE: os armazéns que mantém cada produto em estoque.
  • T_INVOICE_ITEM: os items dos pedidos.
  • T_INVOICE_ITEM_DELIVERY: as entregas dos items (eles podem ser entregues parcialmente).
  • T_PRODUCT: um auto-relacionamento que liga um produto a outro equivalente.

Você pode baixar o modelo exemplo aqui.

-- Generate, execute and show (but don't COMMIT yet) the DELETE statements
begin
	P_DELETE_CASCADE(
		p_owner=>user,
		p_table=>'T_PRODUCT',
		p_where=>'product_id in (1,6)',
		p_mode => 'GX'
		);
end;
/

PL/SQL procedure successfully completed.
-- Verify the statements and the number of rows affected by each one
col statement for a80 word_wrap
select table_name, statement, rows_deleted from tmp_delete_cascade_stmt order by lev desc, id;

TABLE_NAME                          STATEMENT                                                                        ROWS_DELETED
----------------------------------- -------------------------------------------------------------------------------- ------------
T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_INVOICE_ITEM_DELIVERY             DELETE FROM EDUARDO.T_INVOICE_ITEM_DELIVERY WHERE (INVOICE_ID, PRODUCT_ID) in               4
                                    (SELECT
                                    INVOICE_ID, PRODUCT_ID FROM EDUARDO.T_INVOICE_ITEM WHERE
                                    (PRODUCT_ID) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE (PRODUCT_EQUIVALENT) in (SELECT                         1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            3
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       4
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE product_id in (1,6)                                     2

7 rows selected.
-- Check which records remain
select * from T_PRODUCT;

PRODUCT_ID PRODUCT_NAME         PROVIDER_ID PROVIDER_PRODUCT_ID PRODUCT_EQUIVALENT
---------- -------------------- ----------- ------------------- ------------------
         3 Product 3                      1                9992
         4 Product 4                      1                9993
         5 Product 5                      2                9992

select * from T_PRODUCT_WAREHOUSE;

PRODUCT_ID WAREHOUSE_ID PIECES_IN_STOCK
---------- ------------ ---------------
         3            1              11
         4            2               3
         5            2               1

select * from T_INVOICE;

INVOICE_ID INVOICE_DATE
---------- -----------------
         1 26-MAR-2018 12:06
         2 26-MAR-2018 12:06
         3 26-MAR-2018 12:06
         4 26-MAR-2018 12:06

select * from T_INVOICE_ITEM;

INVOICE_ID PRODUCT_ID  QUANTITY
---------- ---------- ---------
         3          4         3
         4          3         5

select * from T_INVOICE_ITEM_DELIVERY;

INVOICE_ID PRODUCT_ID DELIVERY_DATE     QUANTITY_DELIVERED
---------- ---------- ----------------- ------------------
         3          4 26-MAR-2018 12:56                  3

Perceba que os pedidos 1 e 2 ainda existem na tabela T_INVOICE, mesmo sem nenhum item relacionado na tabela T_INVOICE_ITEM. Isto é normal, já que esta regra não é validada por nenhuma constraint criada no banco de dados. Eu preciso apagar estes registros manualmente.

Perceba também que o Produto 2 foi apagado porque ele era dependente do Produto 1. Isto é um resultado esperado e mostra o risco de usar este script sem os cuidados necessários ;-).

Depois de confirmar que tudo está OK, você pode fazer o COMMIT para confirmar as alterações.

Espero que gostem!!!

Advertisements