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.