Forum Stats

  • 3,741,213 Users
  • 2,248,391 Discussions
  • 7,861,674 Comments

Discussions

Creating data chunks for deleting table records

Haider Hussain
Haider Hussain Member Posts: 130 Blue Ribbon

Hello Experts,

Oracle Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

I have around 6 million records in a table.

create table test1 (employee varchar2(13), 
                    manager number(8),
                    department number(8));

Hierarchy (1. highest, 3. lowest)

1. Department

2. Manager

3. Employee

Sample table data

Employee     Manager      Department
1001           101           10
1002           101           10
1003           101           10
1004           102           20
1005           103           20
1006           104           20
1007           103           20
1008           103           20
1009           105           30
1010           106           30

Data needs to be deleted from more than 100 tables.

I am trying to divide the data into chunks in order to delete records of manager column, from the target tables using a package-function.

Sample delete statement in the package function :

DELETE FROM <table_name> 
 WHERE manager = I_manager;

--Index is present on manager column in the required table. 

After deleting say 100 records (chunk_size), a COMMIT shall be issued.

I have tried chunking the data on all three columns using the below query.

select 
CEIL (ROW_NUMBER () OVER (ORDER BY a.dept) / 100) chunk_dept,
CEIL (ROW_NUMBER () OVER (ORDER BY a.manager) / 100) chunk_manager,
CEIL (ROW_NUMBER () OVER (ORDER BY a.employee) / 100) chunk_emp,
a.* from <table_name> a;

When I try to form data chunks on say department column, I notice that data for 1 department is getting assigned to different chunk ID's.

Could you please suggest an effective parameter to form data chunks for deleting the data from the tables.

Regards.

Tagged:

Best Answer

Answers

Sign In or Register to comment.