Re: Update with join

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 14 Jan 1999 13:05:18 GMT
Message-ID: <369fe6b1.84200644@192.86.155.100>

A copy of this was sent to and123456_at_my-dejanews.com (if that email address didn't require changing) On Wed, 13 Jan 1999 18:44:12 GMT, you wrote:

>In SQL Server you can update with a join like:
>Update table1
>From table1 t1, table2 t2
>Set t1.col = t2.col
>Where t1.id = t2.id;
>
>Oracle does not support Update with join. The closest thing is Update with
>subquery:
>Update table1 t1
>Set col = (Select col from table2 where id=t1.id);
>
>However, if there is no row in table2, col in table1 will be set to NULL.
>Using Update table1 t1 Set col = (Select col from table2 where id=t1.id)
>Where exists (Select 1 from table2 where id=t1.id); will access table2 (or
>index) twice which is not quite efficient.
>

but the row you want is in the buffer cache (i mean the exists will have the exact row you want right there for the "set col = ( ... )" to use) so it is 'efficient'.

You can update certain key preserved views, for example:

SQL> create table t1 ( id int primary key, col varchar2(25) ); Table created.

SQL> create table t2 ( id int primary key, col varchar2(25) ); Table created.

SQL> create or replace view t1_t2
  2 as
  3 select t1.id t1_id, t1.col t1_col, t2.id t2_id, t2.col t2_col   4 from t1, t2
  5 where t1.id = t2.id
  6 /
View created.

SQL> insert into t1 values ( 1, 'data' ); SQL> insert into t1 values ( 2, 'more data' );

SQL> insert into t2 values ( 1, null );
SQL> insert into t2 values ( 2, null );
SQL> insert into t2 values ( 3, 'some data' );

SQL> select * from t2;

ID COL

---------- -------------------------
         1
         2
         3 some data

SQL>

SQL>
SQL> update t1_t2
2 set t2_col = t1_col
3 /

2 rows updated.

SQL>
SQL> select * from t2;

ID COL

---------- -------------------------
         1 data
         2 more data
         3 some data

>Anyone has better ideas? I believe using cursor in PL/SQL will make it even
>slower because you individually select and update each record, right?

don't use the cursor, and don't be afraid to use the exists, it won't be slow (full scan on T2, index prob into T1 for the exists, buffer read for the set). Even if you joined T2 to T1, it would be full scan T2, indexed read into T1 to find the mate (or not) and then an application of the SET clause from memory (6 one way, 1/2 dozen the other). In testing, the joined view update is marginally faster then the where exists, but the where exists is more straightforward and flexible.

As another test of this, I set up t1 and t2 again with 15,000+ rows (one row in t2 for each of t1 just to exaggerate the index access 2 times). As you can see from the explain plans, the queries behave as i describe, but if you look at the

statistics, they behaved very similiarly in access data (very close to the same number of block gets and such)

SQL> insert into t1 select rownum, null from all_objects; 15626 rows created.

SQL> insert into t2 select rownum, substr(object_name,1,25) from all_objects; 15626 rows created.

SQL> commit;
Commit complete.

SQL> set autotrace on
SQL> update t1
2 set col = (select col from t2 where t2.id = t1.id ) 3 where exists ( select null from t2 where t2.id = t1.id ) 4 /
15626 rows updated.

Execution Plan

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

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'T1'
   4    2       INDEX (UNIQUE SCAN) OF 'SYS_C0034118' (UNIQUE)
   5    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   6    5     INDEX (UNIQUE SCAN) OF 'SYS_C0034118' (UNIQUE)

Statistics

        277  recursive calls
      72615  db block gets
      58991  consistent gets
          0  physical reads
   10304184  redo size
        502  bytes sent via SQL*Net to client
        881  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15626  rows processed

SQL> set autotrace off

SQL> rollback;
Rollback complete.

SQL> set autotrace on
SQL> update t1_t2 set t1_col = t2_col
2 /
15626 rows updated.

Execution Plan

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

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T1'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   5    4         INDEX (UNIQUE SCAN) OF 'SYS_C0034118' (UNIQUE)

Statistics

          7  recursive calls
      71587  db block gets
      58931  consistent gets
          2  physical reads
   10189328  redo size
        502  bytes sent via SQL*Net to client
        793  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      15626  rows processed




>-----------== Posted via Deja News, The Discussion Network ==----------


>http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 14 1999 - 07:05:18 CST