Hi All,
I have a following tables . I have an issue in updating table having 25 million records . I used Merge query . It gives 14 minutes without adding constraint while it gives 30 mins or more when constraints are added. I have given my tables and query used.
Tables:
Tab1 (emp_id number (14) not null ,emp_token_id number(14), emp_name varchar2(50));
Tab1 have 25 million records ;
Tab2 (emp_token_id number(14), emp_token_desc varchar2(50),emp_reg_id number(14));
Tab 2 have 0.5 million records
Tab3 (emp_reg_id number(14), emp_reg_name varchar2(50),emp_token_id number(14));
Tab3 have 1.5 million records.
Foreign keys:
emp_token_id in tab1 is a foreign key referenced from Primary Key of Tab2 .
emp_reg_id in Tab2 is foreign key referenced from Primary key of Tab 3.
Now i need to
1)add a new column emp_reg_id in Tab1 which is a foreign key referenced from Tab3
2) i also need to populate values of emp_reg_id in Tab1 from Tab3 .
My Query :
ALTER TABLE Tab1
ADD CONSTRAINT fk_reg_id FOREIGN KEY (emp_reg_id )
REFERENCES tab3(emp_reg_id )
add emp_reg_id ;
Populating Values using Merge Query :
MERGE
INTO Tab1 c
USING (select b.emp_reg_id ,a.emp_token_id
from tab2 a , Tab3 b
where a.emp_reg_id = b.emp_reg_id
) st
ON (c.emp_token_id = st.emp_token_id )
WHEN MATCHED THEN
UPDATE SET c.emp_reg_id = st.emp_reg_id ;
Issue :
When i don't add any constraints it take only 14 minutes to insert records but when i add constraints it take 30 minutes or more than 30 mins to populate a value in Tab1. Is there any other better way other than Merge Query to update 25 million records. Please kind your advice