Home » SQL & PL/SQL » SQL & PL/SQL » diffrence between both query (oracle,9.2.0.8,unix)
diffrence between both query [message #282862] Sat, 24 November 2007 04:44 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi,

I have run the below queries as per my understanding both query should update the similar data but first query is updating more
number of rows. Could you let me know why it is updating some extra records.there is some diffrence in logic.

begin

dbms_output.put_line('start_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));
update  erp_avp_transactions_backup t	set  
 (df_fee_cost)
              = (Select cic.item_cost
				FROM mf_cmf_item_cost_details cic ,mf_bom_resources br 
 				where 1=1 
				AND cic.inventory_item_id=t.inventory_item_id
  				AND cic.organization_id   = 1 
  				AND cic.cost_type_id  = 1      
  				AND cic.global_name  ='US' 
  				and cic.RESOURCE_ID = br.RESOURCE_ID 
 				and cic.COST_ELEMENT_ID  = br.COST_ELEMENT_ID  
  				and cic.ROLLUP_SOURCE_TYPE =1 
  				and cic.LEVEL_TYPE  =1          
  				and br.RESOURCE_CODE ='DF Fee' 
  				and br.COST_ELEMENT_ID = 3     
  				and br.ORGANIZATION_ID = cic.organization_id 
  				and br.GLOBAL_NAME     = 'US'
)
where  t.po_line_type='TK Transf'
                        and t.df_fee_cost is NULL
                        and t.last_update_date>=TO_DATE('2007-10-28 00:00:00' , 'YYYY-MM-DD HH24:MI:SS');

						
dbms_output.put_line('end_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));						

dbms_output.put_line('updated df_fee_cost '||sql%rowcount||' rows');


end;


output result-

start_time :24-NOV-2007 00:54:47
end_time :24-NOV-2007 02:09:26
updated df_fee_cost 613421 rows



second query

begin

dbms_output.put_line('start_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));

update  erp_avp_transactions_backup t	set  
 (df_fee_cost)
              = (Select cic.item_cost
				FROM mf_cmf_item_cost_details cic ,mf_bom_resources br 
 				where 1=1 
				AND cic.inventory_item_id=t.inventory_item_id
  				AND cic.organization_id   = 1 
  				AND cic.cost_type_id  = 1      
  				AND cic.global_name  ='US' 
  				and cic.RESOURCE_ID = br.RESOURCE_ID 
 				and cic.COST_ELEMENT_ID  = br.COST_ELEMENT_ID  
  				and cic.ROLLUP_SOURCE_TYPE =1 
  				and cic.LEVEL_TYPE  =1          
  				and br.RESOURCE_CODE ='DF Fee' 
  				and br.COST_ELEMENT_ID = 3     
  				and br.ORGANIZATION_ID = cic.organization_id 
  				and br.GLOBAL_NAME     = 'US'
)
where  exists 
(
Select  1 
				FROM mf_cmf_item_cost_details cic ,mf_bom_resources br 
 				where 1=1 
				AND cic.inventory_item_id=t.inventory_item_id
  				AND cic.organization_id   = 1 
  				AND cic.cost_type_id  = 1      
  				AND cic.global_name  ='US' 
  				and cic.RESOURCE_ID = br.RESOURCE_ID 
 				and cic.COST_ELEMENT_ID  = br.COST_ELEMENT_ID  
  				and cic.ROLLUP_SOURCE_TYPE =1 
  				and cic.LEVEL_TYPE  =1          
  				and br.RESOURCE_CODE ='DF Fee' 
  				and br.COST_ELEMENT_ID = 3     
  				and br.ORGANIZATION_ID = cic.organization_id 
  				and br.GLOBAL_NAME     = 'US'
)
                        and t.po_line_type='TK Transf'
                        and t.df_fee_cost is NULL
                        and t.last_update_date>=TO_DATE('2007-10-28 00:00:00' , 'YYYY-MM-DD HH24:MI:SS');
dbms_output.put_line('end_time :' || to_char(sysdate ,'DD-MON-YYYY HH24:MI:SS'));						

dbms_output.put_line('updated df_fee_cost '||sql%rowcount||' rows');


end;



start_time :23-NOV-2007 00:09:42
end_time :23-NOV-2007 02:04:07
updated df_fee_cost 613403 rows



Thanks,
Sagar

Re: diffrence between both query [message #282864 is a reply to message #282862] Sat, 24 November 2007 05:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

assuming the only difference is in the WHERE clause (presence of EXISTS clause, the same subquery as in the SET clause), then, yes, the queries may differ in number of updated rows.

In the second example, the query updates only the rows, where the subquery returns a row.
In the first example, it additionally updates the rows, where the subquery does not return any row; such rows in T are updated with NULL value.

Of course, correct behaviour is defined by your requirements and may be any of these two.
Re: diffrence between both query [message #282874 is a reply to message #282864] Sat, 24 November 2007 08:22 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Plus that the two were executed at different environments, considering that the time they ran overlaps.
Previous Topic: How to move data between two partitions?
Next Topic: how do form this string?
Goto Forum:
  


Current Time: Thu Dec 08 14:09:07 CST 2016

Total time taken to generate the page: 0.06707 seconds