Re: Speedup the dml ??

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 15 Apr 2000 22:05:17 +0200
Message-ID: <38F8CB7D.3E6A725D_at_0800-einwahl.de>


What are the key definitions of a and b? I guess you do not have a unique key on a (c1, c2, c3, c4) but only on a (c1, c2, c3, c4, c5)?

Martin

feib wrote:
>
> In article <38F7A7AB.AD98E6D9_at_0800-einwahl.de>, Martin.Haltmayer_at_0800-einwahl.de says...
> > First of all, your update statement would also update the rows of a which do not
> > have a pendant in b. c5 would become null there. If this is intention - fine. If
> > not you must add something like
> >
> > ...
> > where (a.c1, a.c2, a.c3, a.c4) in (
> > select b.c1, b.c2, b.c3, b.c4
> > from b
> > )
> >
> > to your update statement.
> >
> > I checked three things at my NT 4.0, SP 6a, Pentium III 650 MHz, 128 MB:
> >
> > a) the performance of your update statement (without my correction): 650 s
> > update a
> > set c5 = ( select c5
> > from b
> > where a.c1=b.c1
> > and a.c2=b.c2
> > and a.c3=b.c3
> > and a.c4=b.c4 );
> >
> > b) my update statement with correction: 73 s
> > SQL> update --+ all_rows
> > 2 (
> > 3 select
> > 4 a.c5
> > 5 , b.c5 as new_c5
> > 6 from
> > 7 testa a
> > 8 , testb b
> > 9 where 1 = 1
> > 10 and a.c1=b.c1
> > 11 and a.c2=b.c2
> > 12 and a.c3=b.c3
> > 13 and a.c4=b.c4
> > 14 ) x
> > 15 set x.c5 = x.new_c5
> > 16 /
> >
> > c) your statement with correction: 83 s
> >
> > SQL> update --+ all_rows
> > 2 testa a
> > 3 set c5 = ( select c5
> > 4 from testb b
> > 5 where a.c1=b.c1
> > 6 and a.c2=b.c2
> > 7 and a.c3=b.c3
>
> Martin,
>
> I rewrite the statement with EXISTS as follows
> to compare these statements:
>
> 1)
>
> SQL>list
> 1 update /*+ ALL_ROWS */ tst_cmrln a
> 2 set a.mrtotcl = ( select b.mrtotcl
> 3 from tst_lnmr b
> 4 where b.brno = a.brno
> 5 and b.apno = a.apno
> 6 and b.srno = a.srno
> 7 and b.chkdg = a.chkdg )
> 8 where (brno,apno,srno,chkdg) in
> 9 ( select brno,apno,srno,chkdg
> 10* from tst_lnmr )
> 59881 rows updated.
>
> Elapsed: 00:01:02.78
>
> Execution Plan
> ----------------------------------------------------------
> 0 UPDATE STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1148 Card=16
> 3 Bytes=27547)
>
> 1 0 UPDATE OF 'TST_CMRLN'
> 2 1 MERGE JOIN (Cost=1148 Card=163 Bytes=27547)
> 3 2 SORT (JOIN) (Cost=471 Card=53019 Bytes=2756988)
> 4 3 VIEW (Cost=242 Card=53019 Bytes=2756988)
> 5 4 SORT (UNIQUE) (Cost=242 Card=53019 Bytes=2597931)
> 6 5 TABLE ACCESS (FULL) OF 'TST_LNMR' (Cost=26 Card=
> 53019 Bytes=2597931)
>
> 7 2 SORT (JOIN) (Cost=677 Card=61209 Bytes=7161453)
> 8 7 TABLE ACCESS (FULL) OF 'TST_CMRLN' (Cost=138 Card=61
> 209 Bytes=7161453)
>
> 9 0 TABLE ACCESS (BY INDEX ROWID) OF 'TST_LNMR' (Cost=2 Card=1
> Bytes=49)
>
> 10 9 INDEX (RANGE SCAN) OF 'I_LNMR' (NON-UNIQUE) (Cost=1 Card
> =1)
>
> Statistics
> ----------------------------------------------------------
> 30 recursive calls
> 60981 db block gets
> 85113 consistent gets
> 3273 physical reads
> 14705548 redo size
> 406 bytes sent via SQL*Net to client
> 914 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 3 sorts (disk)
> 59881 rows processed
>
>
> 2)
>
> SQL> l
> 1 update /*+ ALL_ROWS */ tst_cmrln a
> 2 set a.mrtotcl = ( select b.mrtotcl
> 3 from tst_lnmr b
> 4 where b.brno = a.brno
> 5 and b.apno = a.apno
> 6 and b.srno = a.srno
> 7 and b.chkdg = a.chkdg )
> 8 where EXISTS ( select 1
> 9 from tst_lnmr b
> 10 where b.brno = a.brno
> 11 and b.apno= a.apno
> 12 and b.srno=a.srno
> 13* and b.chkdg=a.chkdg)
> SQL> /
>
> 59881 rows updated.
>
> Elapsed: 00:00:48.10
>
> Execution Plan
> ----------------------------------------------------------
> 0 UPDATE STATEMENT Optimizer=HINT: ALL_ROWS (Cost=139 Card=307
> 9 Bytes=360243)
>
> 1 0 UPDATE OF 'TST_CMRLN'
> 2 1 FILTER
> 3 2 TABLE ACCESS (FULL) OF 'TST_CMRLN' (Cost=139 Card=3079
> Bytes=360243)
>
> 4 2 INDEX (RANGE SCAN) OF 'I_LNMR' (NON-UNIQUE) (Cost=1 Ca
> rd=1 Bytes=49)
>
> 5 0 TABLE ACCESS (BY INDEX ROWID) OF 'TST_LNMR' (Cost=2 Card=1
> Bytes=49)
>
> 6 5 INDEX (RANGE SCAN) OF 'I_LNMR' (NON-UNIQUE) (Cost=1 Card
> =1)
>
>
> Besides, I write the statement follow you like
>
>
> SQL> l
> 1 update --+ ALL_ROWS
> 2 ( select a.mrtotcl,b.mrtotcl as new_mrtotcl
> 3 from tst_cmrln a,tst_lnmr b
> 4 where 1=1
> 5 and a.brno = b.brno
> 6 and a.apno = b.apno
> 7 and a.srno = b.srno
> 8 and a.chkdg = b.chkdg ) x
> 9* set x.mrtotcl = x.new_mrtotcl
> SQL> /
> set x.mrtotcl = x.new_mrtotcl
> *
> ERROR at line 9:
> ORA-01779: cannot modify a column which maps to a non key-preserved table
>
> Elapsed: 00:00:00.02
> SQL>
>
> Why I got the ORA-01779 ??
>
> Best Regards ,
> Agi Chen
>
>
>
>
> Received on Sat Apr 15 2000 - 22:05:17 CEST

Original text of this message