Home » RDBMS Server » Performance Tuning » Update million rows (oracle 10g)
Update million rows [message #340364] |
Tue, 12 August 2008 08:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ravi214u
Messages: 153 Registered: February 2008 Location: CANADA
|
Senior Member |
|
|
hi,
I have to update rows in a table. Itried with the query given below but it take lot of time. can any one suggest any other idea to update.
update master_table set v_code ='UM01' where v_code not in (select v_code from (SELECT b.v_code, a.fic_mis_date,c.v_sub_code FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='FA'
AND a.v_facility_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999'
union
SELECT b.v_code, a.fic_mis_date,c.v_sub_code FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='CU'
AND a.v_facility_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999'
union
SELECT b.v_code, a.fic_mis_date,c.v_sub_code FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='IN'
AND a.v_facility_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999'
union
SELECT b.v_code, a.fic_mis_date,c.v_sub_code FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='AI'
AND a.v_facility_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999')
Thanks in advance
|
|
|
|
Re: Update million rows [message #340379 is a reply to message #340364] |
Tue, 12 August 2008 09:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ravi214u
Messages: 153 Registered: February 2008 Location: CANADA
|
Senior Member |
|
|
Sorry michel,
I am posting my update statement again. there is change in the linkage id .So that why i have to introduce many union.
update master_table set v_code ='UM01' where v_code not in
(select v_code from (SELECT b.v_code, a.fic_mis_date,c.v_sub_code
FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='FA'
AND a.v_facility_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999'
union
SELECT b.v_code, a.fic_mis_date,c.v_sub_code
FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='CU'
AND a.v_gfc_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999'
union
SELECT b.v_code, a.fic_mis_date,c.v_sub_code
FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='IN'
AND a.v_net_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999'
union
SELECT b.v_code, a.fic_mis_date,c.v_sub_code
FROM FCT_MASTER a,STG_MASTERb,stg_mitigants c
WHERE a.fic_mis_date='01-july-2008'
AND a.fic_mis_date=b.fic_mis_date
AND b.v_linkage_type='AI'
AND a.v_inst_id = b.v_linkage_id
AND b.v_code=c.v_code
AND c.v_sub_code='999')
|
|
|
|
|
|
Re: Update million rows [message #340649 is a reply to message #340364] |
Wed, 13 August 2008 10:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
ravi214u
Messages: 153 Registered: February 2008 Location: CANADA
|
Senior Member |
|
|
Thanks for replying ..
In my table there are nearly 3 million rows and it will update few thousand.
The index used is created by composite primary key(3 column) .SO i am not creating any new index. if required please suggest.
i am attaching the final code for reference
CREATE OR REPLACE PROCEDURE UNUSED_code(P_MIS_DATE varchar2)
AS
LD_MIS_DATE DATE := TO_DATE(P_MIS_DATE, 'YYYYMMDD');
BEGIN
FOR I IN (SELECT v_code FROM STG_MITIGANTS WHERE v_code NOT IN
(SELECT v_code FROM
(SELECT V_RUN_ID,B.v_code, A.FIC_MIS_DATE
FROM FCT_MASTER A,STG_MASTERB,STG_MITIGANTS C
WHERE A.FIC_MIS_DATE=B.FIC_MIS_DATE
AND B.V_LINKAGE_TYPE='FA'
AND A.V_FACILITY_ID = B.V_LINKAGE_ID
AND B.v_code=C.v_code
AND C.v_sub_code='999'
UNION ALL
SELECT V_RUN_ID,B.v_code, A.FIC_MIS_DATE
FROM FCT_MASTER A,STG_MASTERB,STG_MITIGANTS C
WHERE A.FIC_MIS_DATE=B.FIC_MIS_DATE
AND B.V_LINKAGE_TYPE='IN'
AND A.V_INSTRUMENT_ID = B.V_LINKAGE_ID
AND B.v_code=C.v_code
AND C.v_sub_code='999'
UNION ALL
SELECT V_RUN_ID,B.v_code, A.FIC_MIS_DATE
FROM FCT_MASTER A,STG_MASTERB,STG_MITIGANTS C
WHERE A.FIC_MIS_DATE=B.FIC_MIS_DATE
AND B.V_LINKAGE_TYPE='CU'
AND A.V_GFC_ID = B.V_LINKAGE_ID
AND B.v_code=C.v_code
AND C.v_sub_code='999'
UNION ALL
SELECT V_RUN_ID,B.v_code, A.FIC_MIS_DATE
FROM FCT_MASTER A,STG_MASTERB,STG_MITIGANTS C
WHERE A.FIC_MIS_DATE=B.FIC_MIS_DATE
AND B.V_LINKAGE_TYPE='AI'
AND A.N_NETTING_AGREEMENT_ID = B.V_LINKAGE_ID
AND B.v_code=C.v_code
AND C.v_sub_code='999') GROUP BY v_code))
LOOP
UPDATE STG_MITIGANTS Z SET Z.V_REASON_CODE='UNMIT08' WHERE Z.v_code =I.v_code;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
end;
.
Thanks
|
|
|
Goto Forum:
Current Time: Wed Sep 11 20:55:03 CDT 2024
|