Hi All,
I have a table where i need to take parent's record date and insert that date into an another table. I have written the code but it is taking longer time than expected . select is taking very little amount of time but when i join with an another variable it is taking a longer time to run
Table:
CREATE TABLE EMPLOYEE ( EMP_ID NUMBER , PREV_EMP_ID NUMBER, JOB_STATUS ,EMP_START_DATE DATE );
CREATE DEPARTMENT (EMP_ID, EMP_ORG_START_DATE,JOB_STATUS);
INSERT INTO EMPLOYEE VALUES (5,4,1,SYSDATE);
INSERT INTO EMPLOYEE VALUES (4,3,2,TO_DATE('29-OCT-2019','DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (3,2,2,TO_DATE('21-AUG-2017','DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (2,1,2,TO_DATE('19-JUN-2015','DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (1, ,2,TO_DATE(' ,'DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (9,8,1,SYSDATE);
INSERT INTO EMPLOYEE VALUES (8,7,2,TO_DATE('29-JUN-2019','DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (7,6,2,TO_DATE('21-MAY-2017','DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (6,5,2,TO_DATE('19-AUG-2015','DD-MM-YYYY'));
INSERT INTO EMPLOYEE VALUES (15 ,2,TO_DATE('21-JAN-2012','DD-MM-YYYY'));
Expected Output is :
select * from department ;
EMP_ID ORG_START_DATE, JOB_STATUS
5 21-MAY-2012 1
9 21-JAN-2012 1
My query:
set serveroutput on
declare
cursor c1 is
select * from employee where job_status = 1 ;
Z_emp_id number;
z_emp_start_Date date ;
begin
for i in c1 loop
SELECT e.emp_id ,
connect_by_root EMP_START_DATE
INTO Z_emp_id ,Z_emp_start_date
FROM employee e where e.emp_id = i.emp_id
START WITH e.job_status = 1
CONNECT BY PRIOR e.pre_emp_id = e.emp_id;
insert into department
(emp_id,
emp_org_start_Date,
job_status)
values
(z_emp_id,
Z_emp_start_date,
1);
end loop;
exception
when others then
dbms_output.put_line ('There is an exception');
end ;
In real scenario total records of employee is 40 k records. Production date have 40 million records. For 40 k records itself it have performance issues . without his hirerachial query scripts executed in seconds . department table have 91 columns . It works well with select but when i put hierarchical query in for loop it causing a performance issue . Should i update the department table after the insert happens like doing bulk for all update ? please kindly advise me.
Regards,
Uday