SQL改寫優化

wei-xh發表於2012-05-03
SQL優化
update /*+ gather_plan_statistics */ wxh_tbd a
   set (object_id, object_name) = (select object_id, object_name
                                     from wxh_tbd b
                                    where a.object_id = b.object_id);
試了N種辦法，都走不上HASH。
改寫如下：
update (
 select /*+ BYPASS_UJVC  use_hash(a b) ordered */ a.object_name,a.object_id,b.object_name new_object_name,b.object_id new_object_id
 from wxh_tbd a, wxh_tbd b
 where a.object_id=b.object_id
 ) set object_name=new_object_name,object_id=new_object_id
 ;
 
如果OBJECT_ID是唯一索引，就不用加BYPASS_UJVC 了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-722640/，如需轉載，請註明出處，否則將追究法律責任。

相關文章