-
procedure to delete several records from several tables
First of all I would like to say Hi to everyone here.
Second thing:
I have a procedure which I want to delete several records from several tables. Those tables from were I want to delete rows have names stored in another table in database along with the column name that is used for condition.
Main table DB_CLEANUP_PARAMETERS (the one that stores tables names for other) is like this:
table_name varchar2(30)
date_field_name varchar2(30)
retention_days number
I wrote the following procedure to do the things
Code:
CREATE OR REPLACE PROCEDURE DB_CLEANUP_PROCEDURE_2 IS
i INTEGER;
TYPE j IS TABLE OF ROWID;
row_id j;
v_table_name VARCHAR2(30);
v_column_name VARCHAR2(30);
v_retention_days NUMBER(9);
v_chunk_size INTEGER := 1000;
BEGIN
FOR rec IN (SELECT dcp.table_name, dcp.date_field_name, dcp.retention_days
FROM db_cleanup_parameters dcp) LOOP
v_table_name := rec.table_name;
v_column_name := rec.date_field_name;
v_retention_days := rec.retention_days;
EXECUTE IMMEDIATE 'SELECT ROWID FROM ' || v_table_name || ' WHERE ' ||
v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
BULK COLLECT INTO row_id;
FORALL i IN 1 .. row_id.COUNT()
EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''' AND ROWNUM <='||v_chunk_size;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
COMMIT;
END DB_CLEANUP_PROCEDURE_2;
Main problem is that I get the following error:
Code:
Compilation errors for PROCEDURE ONAIR.DB_CLEANUP_PROCEDURE_2
Error: PLS-00801: internal error [*** ASSERT at file pdw4.c, line 589; Unknown expression Expr = 283.; DB_CLEANUP_PROCEDURE_2__ONAIR__P__287291[20, 5]]
Line: 20
Text: EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||''' AND ROWNUM <='||v_chunk_size;
Purpose of this procedure is to delete rows from the tables stored in DB_CLEANUP_PARAMETER table, but not one by one. Delete rows in chunks.
What is wrong ? can I get some help ?
Thanks
-
What's the purpose of doing twice the I/O?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
What's the purpose of doing twice the I/O?
I really don't understand what you want to say.
-
Posted procedure does - for each row to be deleted...
... 1st I/O : select looking for rowid
... 2nd I/O : delete based on rowid
e.g. two I/Os to delete a row.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
Posted procedure does - for each row to be deleted...
... 1st I/O : select looking for rowid
... 2nd I/O : delete based on rowid
e.g. two I/Os to delete a row.
Now I understand... What will be a solution for if you don't mind.
Thanks
-
I would do one statement per table - executing a dynamic sql statement like...
Code:
'DELETE ' || v_table_name || ' WHERE ' || v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
I would do one statement per table - executing a dynamic sql statement like...
Code:
'DELETE ' || v_table_name || ' WHERE ' || v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
The idea was to delete rows in chunks (let's say 10000 records once and commit)... not all records once, since there might be table with millions records.
I thought about one statement per table but it would block the database.
-
Innitially the procedure was like:
Code:
CREATE OR REPLACE PROCEDURE DB_CLEANUP_PROCEDURE IS
i INTEGER;
TYPE j IS TABLE OF ROWID;
row_id j;
v_table_name VARCHAR2(30);
v_column_name VARCHAR2(30);
v_retention_days NUMBER(9);
k NUMBER := 0;
BEGIN
FOR rec IN (SELECT dcp.table_name, dcp.date_field_name, dcp.retention_days
FROM db_cleanup_parameters dcp) LOOP
v_table_name := rec.table_name;
v_column_name := rec.date_field_name;
v_retention_days := rec.retention_days;
EXECUTE IMMEDIATE 'SELECT ROWID FROM ' || v_table_name || ' WHERE ' ||
v_column_name || ' < (SYSDATE - ' || v_retention_days || ')'
BULK COLLECT INTO row_id;
FOR i IN 1 .. row_id.COUNT LOOP
EXECUTE IMMEDIATE 'DELETE FROM '||v_table_name||' WHERE ROWID = '''|| row_id(i)||'''';
k := k + 1;
IF k = 1000 THEN
COMMIT;
k := 0;
END IF;
END LOOP;
END LOOP;
COMMIT;
END DB_CLEANUP_PROCEDURE;
But seems that this one makes a delete statement for each row which is not good when you have millions rows to delete
-
Two questions...
1- Are tables partitioned by v_column_name ?
If yes... truncating or switching partitions are good options to take into consideration.
2- What's the percentage of rows being deleted?
A high percentage purge works faster doing a reverse purge, instead of deleting what you don't want anymore, bulk insert what you want in a new incarnation of the same table.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
Two questions...
1- Are tables partitioned by v_column_name ?
If yes... truncating or switching partitions are good options to take into consideration.
2- What's the percentage of rows being deleted?
A high percentage purge works faster doing a reverse purge, instead of deleting what you don't want anymore, bulk insert what you want in a new incarnation of the same table.
1. No
In DB_CLEANUP_PARAMETERS I have the following entries:
TABLE_NAME DATE_FIELD_NAME RETENTION_DAYS
------------------------------------------------------------
PH_EVENT TO_DATE 100
PH_ACCOUNT END_DATE 150
PH_CONTACT DATE_TO 50
PH_EVENT, PH_ACCOUNT, PH_CONTACT are tables from database which have around 8 million records. From these table I have to delete all the records where the corresponding column is greater than (SYSDATE + RETENTION_DAYS).
2. Between 70 and 90%
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|