DB query performance comparison and tuning

dingxy

于 2020-02-14 12:00:30 发布

阅读量179

点赞数

分类专栏：数据库和Oracle

本文链接：https://blog.csdn.net/dingxy/article/details/104309464

版权

数据库和Oracle 专栏收录该内容

88 篇文章 0 订阅

订阅专栏

exmaple 1:

----Status date update
select jde2date(wrinsdte),jde2date(wrupmj), wruser  
from  AA.f1217
where wrnumb in 
      (
 select 
fanumb 
from  AA.f1201
where faasid in
(
'209995'
 )
 );
  
update AA.f1217
set wrinsdte = date2jde(sysdate),
    wruser = sys_context('USERENV', 'CURRENT_SCHEMA'),
    wrupmj = date2jde(sysdate)
where wrnumb in 
 (
 select 
fanumb 
from  AA.f1201
where faasid in
('209995'
 )
 );

Exmaple2: when there are thousands records to update , using join and exists


-- ***************Pre-check(modems)******************* note down how many records to update 
 
select jde2date(wrinsdte),jde2date(wrupmj), wruser,wrnumb from AA.f1217 aa
where exists(
  select 1  from AA.f1201 bb
  where aa.wrnumb = bb.fanumb
  and bb.faasid
  in (select  MODEM_SERIALNO from modem_sim_temp
 
  )
)
 
-- ******** update the date to sysdate, make sure the number of update records is same as above , then commit in **************
update AA.f1217 aa
set wrinsdte = date2jde(sysdate),
    wruser = sys_context('USERENV', 'CURRENT_SCHEMA'),
    wrupmj = date2jde(sysdate)
where exists(
  select 1  from AA.f1201 bb
  where aa.wrnumb = bb.fanumb
  and bb.faasid 
  in (select  MODEM_SERIALNO from modem_sim_temp
  )
)