RE: RE: JOINED UPDATE

From: Larry Elkins <elkinsl_at_flash.net>
Date: Thu, 12 Feb 2004 00:05:31 -0600
Message-ID: <MPBBKDBLJAGDLMINJNKBCELHAPAB.elkinsl@flash.net>

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_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jacques Kilchoer
> Sent: Wednesday, February 11, 2004 5:22 PM
> To: oracle-l_at_freelists.org
> 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_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of system manager
> > Sent: mercredi, 11. f=E9vrier 2004 13:00
> > To: oracle-l_at_freelists.org
> > 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_at_quest.com>
> > 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Feb 12 2004 - 00:05:31 CST