Home » RDBMS Server » Performance Tuning » Summary table Update statement.
Summary table Update statement. [message #309184] Wed, 26 March 2008 13:57 Go to next message
arksjd
Messages: 13
Registered: November 2007
Junior Member
Hi

The below update statement takes huge amount of time to exeucte and does not finish.

 	UPDATE SUMMARY_TABLE 
 	SET v_parameter_1 = (SELECT i_parameter 
 			     FROM (SELECT MIN(i_parameter) i_parameter,v_parameter_3,v_parameter_4,v_parameter_5,v_parameter_17,
 								v_parameter_26,v_parameter_32,v_parameter_18,v_parameter_27,v_parameter_31,v_parameter_33
  				   FROM SUMMARY_TABLE a
 				   WHERE v_product_processor='CARDS' 
 				    AND v_parameter_1 IS NULL
 				     GROUP BY			v_parameter_3,
 								v_parameter_4,
 								v_parameter_5,
 								v_parameter_17,
 								v_parameter_26,
 								v_parameter_32,
 								v_parameter_18,
 								v_parameter_27,
 								v_parameter_31,
 								v_parameter_33) temp1
 			      WHERE coalesce(SUMMARY_TABLE.v_parameter_3,'MSG') = coalesce(temp1.v_parameter_3,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_4,'MSG') = coalesce(temp1.v_parameter_4,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_5,'MSG') = coalesce(temp1.v_parameter_5,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_17,'MSG') = coalesce(temp1.v_parameter_17,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_26,'MSG') = coalesce(temp1.v_parameter_26,'MSG') AND
 			        coalesce(SUMMARY_TABLE.v_parameter_32,'MSG') = coalesce(temp1.v_parameter_32,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_18,'MSG') = coalesce(temp1.v_parameter_18,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_27,'MSG') = coalesce(temp1.v_parameter_27,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_31,'MSG') = coalesce(temp1.v_parameter_31,'MSG') AND
 				    coalesce(SUMMARY_TABLE.v_parameter_33,'MSG') = coalesce(temp1.v_parameter_33 ,'MSG') )
 	WHERE v_product_processor='CARDS' 
           AND v_parameter_1 IS NULL;


I have an index on the summary_table for the columns (v_product_processor,v_parameter_1). The table has got 1 million records.

The execution plan is
UPDATE STATEMENT, GOAL = ALL_ROWS													Cost=2	Cardinality=1	Bytes=137
 UPDATE								Object owner=REVDEV	Object name=IL_OUTPUT				
  TABLE ACCESS BY INDEX ROWID		Object owner=REVDEV	Object name=IL_OUTPUT		Cost=2	Cardinality=1	Bytes=137
   INDEX RANGE SCAN					Object owner=REVDEV	Object name=IDX_ILO_PDTPSCR	Cost=2	Cardinality=1	
  VIEW	                                                      Object owner=REVDEV	Cost=3	Cardinality=1	Bytes=333
   SORT GROUP BY																	Cost=3	Cardinality=1	Bytes=143
    TABLE ACCESS BY INDEX ROWID	                            Object owner=REVDEV	Object name=IL_OUTPUT			Cost=2	Cardinality=1	Bytes=143
     INDEX RANGE SCAN	                                    Object owner=REVDEV	Object name=IDX_ILO_PDTPSCR				Cost=2	Cardinality=1	


kindly help me how to optimize the statement.

Thanks.
Re: Summary table Update statement. [message #309217 is a reply to message #309184] Wed, 26 March 2008 17:37 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Make

SELECT COUNT(*) FROM SUMMARY_TABLE 
WHERE v_product_processor='CARDS' AND v_parameter_1 IS NULL


Post the results.

Michael
Re: Summary table Update statement. [message #309228 is a reply to message #309184] Wed, 26 March 2008 18:41 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
If you are on oracle 10.2 or higher, consider rewriting using the MERGE statement and UPDATE Rows only.

That way you can select the rows you are going to update with their new values, and also pick out the pk, or rowids to match on.

Re: Summary table Update statement. [message #309247 is a reply to message #309228] Wed, 26 March 2008 21:00 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Some info here on why your update is slow.

Ross Leishman
Previous Topic: Partitioning effect
Next Topic: Check and telll whether the following configuration is correct or not
Goto Forum:
  


Current Time: Sun Dec 08 19:04:15 CST 2024