Re: Update Statement Syntax

Lets run an explain plan on your statements: SQL> set autotrace traceonly explain
SQL> update a
  2 set a.col1 = 'P'
  3 where a.col2 in (
  4 select b.col1
  5 from b
  6 where b.col2 = a.col3
  7 )
  8 and a.col3 = '2'
  9 /

1 row updated.

Execution Plan

   0 UPDATE STATEMENT Optimizer=CHOOSE    1 0 UPDATE OF 'A'

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'A'
   4    2       TABLE ACCESS (FULL) OF 'B'

SQL> rollback;

Rollback complete.

SQL> update a
  2 set a.col1 = 'P'
  3 where a.col2 in (
  4 select b.col1
  5 from b
  6 where b.col2 = a.col3
  7 and a.col3 = '2'
  8 )
  9 /

1 row updated.

Execution Plan

   0 UPDATE STATEMENT Optimizer=CHOOSE    1 0 UPDATE OF 'A'

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'A'
   4    2       FILTER
   5    4         TABLE ACCESS (FULL) OF 'B'

SQL> rollback;

Rollback complete.

SQL> set autotrace off;

.... analyze the two tables ............................

SQL> set autotrace traceonly explain
SQL> update a
  2 set a.col1 = 'P'
  3 where a.col2 in (
  4 select b.col1
  5 from b
  6 where b.col2 = a.col3
  7 )
  8 and a.col3 = '2'
  9 /

1 row updated.

Execution Plan

   0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=5)    1 0 UPDATE OF 'A'

   2    1     HASH JOIN (SEMI) (Cost=5 Card=1 Bytes=5)
   3    2       TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=2 Bytes=6)
   4    2       TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=1 Bytes=2)

SQL> rollback;

Rollback complete.

SQL> update a
  2 set a.col1 = 'P'
  3 where a.col2 in (
  4 select b.col1
  5 from b
  6 where b.col2 = a.col3
  7 and a.col3 = '2'
  8 )
  9 /

1 row updated.

Execution Plan

   0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=3)    1 0 UPDATE OF 'A'

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=1 Bytes=3)
   4    2       FILTER
   5    4         TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=1 Bytes=2)

As you can see that the way oracle executes the statements is different. They get you the same result ... which you already know. You just need to understand what
your sql statement is doing and *realize* whether it will amount to the same thing.

Anurag

"Simon" <sdobner_at_scu.edu.au> wrote in message news:pan.2002.07.19.12.11.47.180132.1430_at_scu.edu.au...
> G'day,
>
> Is there a difference (functional or otherwise) between putting where
clause
> components (which are compared to constants) in a subselect versus in the
> main where clause
>
> in the following example ( and in a much more complex example I have) the
> resulting update is identical, but I'm not convinced that this will
> always be the case. How do you prove that two statements are functionally
> identical (or not) ?
>
>
> update a
> set a.col1 = 'P'
> where a.col2 in (
> select b.col1
> from b
> where b.col2 = a.col3
> )
> and a.col3 = '2'
>
> V.
>
> update a
> set a.col1 = 'P'
> where a.col2 in (
> select b.col1
> from b
> where b.col2 = a.col3
> and a.col3 = '2'
> )
>
> Thanks
>
> SD
>
>
> Test tables and data follow.
>
> create table a (
> col1 varchar2(10),
> col2 varchar2(10),
> col3 varchar2(10) )
>
> create table b (
> col1 varchar2(10),
> col2 varchar2(10) )
>
> insert into a values ( '','A', '1')
> insert into a values ( '','A', '2')
> insert into a values ( '','B', '2')
>
> truncate table b
> insert into b values ( 'A','1')
> insert into b values ( 'B','2')
>
>
>
> --
> Simon Dobner
> Oracle Database Administrator
> Southern Cross University
> Military Rd, Lismore, NSW, 2480, Australia.
Received on Thu Jul 18 2002 - 22:39:55 CDT