OraFAQ Forum: Performance Tuning » Update million rows

Home » RDBMS Server » Performance Tuning » Update million rows (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update million rows [message #340364]

Tue, 12 August 2008 08:33

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

Report message to a moderator

Re: Update million rows [message #340369 is a reply to message #340364]

Tue, 12 August 2008 08:36

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Keep your lines in 80 characters. Use "Preview" button to verify.
Why don't you use "IN" instead of this bunch of "union"?
In the end, '01-july-2008' is a string not a date.

Regards
Michel

Report message to a moderator

Re: Update million rows [message #340379 is a reply to message #340364]

Tue, 12 August 2008 09:24

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')

Report message to a moderator

Re: Update million rows [message #340391 is a reply to message #340379]

Tue, 12 August 2008 10:19

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use "union all" instead of "union".
For the rest it all depends of the usual information you didn't give: number of rows, indexes, histogram...

Regards
Michel

Report message to a moderator

Re: Update million rows [message #340399 is a reply to message #340364]

Tue, 12 August 2008 11:15

huiyi
Messages: 3
Registered: August 2008
Location: china

Junior Member

use in in instead union.
use not exist instead not in.

btw:
index & rows count & explain information.

Report message to a moderator

Re: Update million rows [message #340405 is a reply to message #340399]

Tue, 12 August 2008 11:40

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Point 1: I already said that
Point 2: not in and not exists are not equivalent
Point 3: I already said that

Regards
Michel

Report message to a moderator

Re: Update million rows [message #340649 is a reply to message #340364]

Wed, 13 August 2008 10:50

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

Report message to a moderator

Previous Topic:	Calculate leaf_blocks, avg_leaf_blocks_per_key etc.
Next Topic:	Performance issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:45:23 CDT 2024