Team,
First let me explain you my table structures
we have 2 tables by name ,st_lo_master and Pdl_loan_Codes.
{code}
st_lo_master table structure :
create table st_lo_master1(loan_num number,bo_Code number, st_Code number, loan_amt number,loan_date date,loan_end_date date, total_due number);
pdl_loan_Codes table structure:
create table pdl_loan_Codes(loan_COde number);
{code }
st_lo_master table has around 3 million records and pdl_loan_code table has around 1.5 million records. the requriement is i need to delete the records from st_lo_master for which loan_num(column name) are present in pdl_loan_codes table.
Basic code goes like this :
delete from st_lo_master where loan_num in (select loan_Code from pdl_loan_Codes);
THe above stmt did not work:
Tried the below 2 code snippets as well, but they are taking a lot of time and still running. we have some indexes on the st_lo_master table on other column of the table.
we are supposed to do the deletes in production environment, and our production is 24/7 and hence we cannot get a long down time.
Our DB Server RAM is 14 GB and its a 4 core
Snippet 1:
{code}
DECLARE
vn_limit constant NUMBER := 100000;
TYPE t_id
IS
TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
v_id t_id;
CURSOR c
Is
SELECT LOAN_CODE FROM PDL_LOAN_CODES;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_id limit vn_limit;
EXIT
WHEN v_id.COUNT = 0;
FORALL i IN v_id.FIRST .. v_id.LAST
DELETE FROM st_lo_master WHERE loan_num = v_id(i);
COMMIT;
END LOOP;
END;
{code}
snippet 2:
{code}
CREATE INDEX idx_tempdate ON st_lo_master
(loan_num
) unrecoverable parallel 5;
CREATE OR REPLACE
PROCEDURE prc_bulk_delete
AS
Type l_bulk_collect
IS
TABLE OF st_lo_master.loan_num%type;
v_type_var l_bulk_collect;
CURSOR c1
Is
SELECT loan_code FROM pdl_loan_codes;
BEGIN
OPEN C1 ;
FETCH c1 Bulk collect INTO v_type_var;
CLOSE c1;
ForAll i IN v_type_var.first .. v_type_var.last
DELETE FROM st_lo_master WHERE loan_num =v_type_var(i);
COMMIT;
END;
{code}
Any recommendations most welcome.