better update with sub query(subquery)-co-related query

create table t1( x int constraint t1_pk primary key,y int );
create table t2( x int constraint t2_pk primary key,y int );

insert into t1 values ( 1, 1 );
insert into t1 values ( 2, 1 );
insert into t1 values ( 3, 1 );
insert into t2 values ( 3, 2 );

set autotrace on explain
update
( select /*+ USE_NL(t1) INDEX( t1 t1_pk ) */
t1.y t1_y, t2.y t2_y
from t1, t2
where t1.x = t2.x )
set t1_y = t2_y
This is used with hints to see that hint is really not required

insert into t1 select rownum+5, 1
from all_objects
analyze table t1 compute statistics
analyze table t2 compute statistics
set autotrace on explain
update
( select t1.y t1_y, t2.y t2_y
from t1, t2
where t1.x = t2.x )
set t1_y = t2_y
/
see the plan its same as first
set autotrace off

It should be NOTED that in order for the update of a join to work, T2 in the
above example must have a primary or unique key on it. If T2 does not,
you’ll not be able to update the join. The optimizer needs to know that T1 will
be joined to AT MOST 1 row in T2 and the way it does this is by seeing that a
primary key or unique constraint exists on the join conditions

good hints to use are

first_rows
o all_rows
o optimizer_dynamic_sampling (new in 9ir2)
o driving_site
update ( select a.substcriber_id, a.app_username, a.app_pwd, b.sap_id, b.mgr_pw
from WWSSO_APPUSERINFO_T A, T B
where A.SSO_USERNAME = B.SW_ONE_ID AND A.APP_ID = B.APP_ID )
SET SUBSCRIBER_ID = 1,
APP_USERNAME=SAP_ID,
APP_PWD=MGR_PW

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: