Re: Speedup the dml ??
Date: Sat, 15 Apr 2000 11:50:03 +0800
Message-ID: <MPG.136267c29adfee12989684_at_news.gcn.net.tw>
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 - 05:50:03 CEST