Re:RE: RE: JOINED UPDATE
- From: "system manager" <sysmgr@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 12 Feb 2004 15:02:46 -0600
Greate information! Thank you very much.
----------------------------------------------
Original Message
From: "Larry Elkins"<elkinsl@xxxxxxxxx>
Subject: RE: RE: JOINED UPDATE
Date: Thu, 12 Feb 2004 00:05:31 -0600
>You can do a join update, subject to rules of key-preserved tables and such
>(asktom.oracle.com as well as the docs have examples). The syntax is just a
>little different in that you use an in-line view as opposed to the syntax
>asked about in the original question. Here is an example that illustrates:
>
>update (select dname, ename
> from dept d, emp e
> where d.deptno = e.deptno) x
>set x.ename = x.dname
>
>Secondly, regarding in/exists, and when to use each, those rules of thumb
>change over time, and don't necessarily apply anymore. For example, in 9i,
>with _always_semi_join (and _always_anti_join) defaulting to choose, and
the
>CBO being able to transform a correlated sub-query to a non-correlated
>sub-query, and vice versa, the rules change. In the first example, it
>becomes a NESTED LOOPS SEMI whether I use the EXISTS or IN construct. In
the
>second example, it becomes a HASH SEMI JOIN regardless of whether I use the
>EXISTS or IN construct. And if you run stats on the queries, consistent
>gets, physical reads, etc, stay the same whether I use the IN or EXISTS
>construct (not included for brevity's sake).
>
>SQL> select *
> 2 from code_master
> 3 where foo_date = to_date('06/30/2001','MM/DD/YYYY')
> 4 and code in (select code from code_detail);
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=1 Bytes=15)
> 1 0 NESTED LOOPS (SEMI) (Cost=27 Card=1 Bytes=15)
> 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=25 Card=1
Bytes=11)
> 3 1 INDEX (RANGE SCAN) OF 'CD_U01' (UNIQUE) (Cost=2 Card=299600
>Bytes=1198400)
>
>SQL>
>SQL> select *
> 2 from code_master
> 3 where foo_date = to_date('06/30/2001','MM/DD/YYYY')
> 4 and exists (select null from code_detail where code_detail.code =
>code_master.code);
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=1 Bytes=15)
> 1 0 NESTED LOOPS (SEMI) (Cost=27 Card=1 Bytes=15)
> 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=25 Card=1
Bytes=11)
> 3 1 INDEX (RANGE SCAN) OF 'CD_U01' (UNIQUE) (Cost=2 Card=299600
>Bytes=1198400)
>
>SQL>
>SQL> select *
> 2 from code_master
> 3 where code in (select code from code_detail);
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=407 Card=99900
>Bytes=1498500)
> 1 0 HASH JOIN (SEMI) (Cost=407 Card=99900 Bytes=1498500)
> 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=25 Card=100000
>Bytes=1100000)
> 3 1 INDEX (FAST FULL SCAN) OF 'CD_U01' (UNIQUE) (Cost=94
>Card=299600 Bytes=1198400)
>
>SQL>
>SQL> select *
> 2 from code_master
> 3 where exists (select null from code_detail where code_detail.code =
>code_master.code);
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=407 Card=99900
>Bytes=1498500)
> 1 0 HASH JOIN (SEMI) (Cost=407 Card=99900 Bytes=1498500)
> 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=25 Card=100000
>Bytes=1100000)
> 3 1 INDEX (FAST FULL SCAN) OF 'CD_U01' (UNIQUE) (Cost=94
>Card=299600 Bytes=1198400)
>
>Regards,
>
>Larry G. Elkins
>elkinsl@xxxxxxxxx
>214.954.1781
>
>> -----Original Message-----
>> From: oracle-l-bounce@xxxxxxxxxxxxx
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jacques Kilchoer
>> Sent: Wednesday, February 11, 2004 5:22 PM
>> To: oracle-l@xxxxxxxxxxxxx
>> Subject: RE: RE: JOINED UPDATE
>>
>>
>> The "where COL in (select ...)" is of course also correct (I used "where
=
>> (exists ...)")
>> The general rule of thumb is to use "IN" if the subquery is small, and =
>> EXISTS if the results of the IN subquery would be large and the table in
=
>> the subquery has a usable index on the matching column.
>> You should try both ways on your system and see which query works best.
>>
>>
>> > -----Original Message-----
>> > From: oracle-l-bounce@xxxxxxxxxxxxx
>> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of system manager
>> > Sent: mercredi, 11. f=E9vrier 2004 13:00
>> > To: oracle-l@xxxxxxxxxxxxx
>> > Subject: Re:RE: JOINED UPDATE
>> >=20
>> >=20
>> > thank you very very much. =20
>> >=20
>> > update account A
>> > set (A.expire_dt, A.curr_eff_dt, A.acct_end_dt)
>> > =3D
>> > (select T.expire_dt, T.curr_eff_dt, T.acct_end_dt from=20
>> > temp_savedates T
>> > where T.acct_num=3DA.acct_num)
>> > where A.acct_num in (select T.acct_num from temp_savedates T);
>> >=20
>> > ----------------------------------------------
>> > Original Message
>> > From: "Jacques Kilchoer"<Jacques.Kilchoer@xxxxxxxxx>
>> > Subject: RE: JOINED UPDATE
>> > Date: Wed, 11 Feb 2004 12:13:46 -0800
>> >=20
>> > >The syntax you have below will cause an error in Oracle.
>> > >
>> > >Oracle version of your statement would be:
>> > >update account
>> > >set (account.expire_dt,
>> > > account.curr_eff_dt,
>> > > account.acct_end_dt) =3D3D (select temp_savedates.expire_dt,
>> > > temp_savedates.curr_eff_dt,
>> > > temp_savedates.acct_end_dt
>> > > from temp_savedates
>> > > where account.acct_num =3D3D temp_savedates.acct_num)
>> > >where exists
>> > >(select * from temp_savedates
>> > > where account.acct_num =3D3D temp_savedates.acct_num) ;
>> ----------------------------------------------------------------
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> ----------------------------------------------------------------
>> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at //www.freelists.org/archives/oracle-l/
>> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>> -----------------------------------------------------------------
>>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
_____________________________________________
Free email with personality! Over 200 domains!
http://www.MyOwnEmail.com
Looking for friendships,romance and more?
http://www.MyOwnFriends.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Re:RE: RE: JOINED UPDATE
