Hi, I ran it on one range ( we have dozens of ranges which run in parallel; sub_svc_parm is over 1 billion rows table; there is unique index on (sub_svc_id, parm_id) and we had to manually parallelize DML so whole process finishes under 2 hours ) This is the statement: merge into sub_svc_parm ssp using ( SELECT /*+ FIRST_ROWS */ b.sub_svc_id, d.destination_group_id val FROM sub_svc a, sub_svc b, sub_svc_parm c, RATE_CENTRE_TEMP_&1 d WHERE a.SUB_ID = b.SUB_ID AND a.SVC_ID = 10000 AND b.SVC_ID = 10008 AND a.SUB_SVC_ID = c.SUB_SVC_ID AND c.parm_id = 10230 AND SUBSTR(c.val,1,6) = d.npanxx) v on (ssp.sub_svc_id=v.sub_svc_id and ssp.parm_id = 12650) when matched then update set ssp.val=v.val when not matched then insert (sub_svc_id) values ( NULL ); Timing looks promising - once the original script finishes ( CURSOR/UPDATE) I will compare timing with MERGE. Thanks for your help guys. Ranko. On 3/13/07, jaromir nemec <jaromir@xxxxxxxxxxxx> wrote:
Hi Alexander, >> Additional problem here ist the join condition >> AND SUBSTR(c.val,1,6) = d.npanxx >> where the key preserving information is aparently lost even if the >> column npanxx is declared as unique. > > as long as I can understand from the above query+update (without > further looking at DDL) the main problem here will be with > sub_svc_parm table. Looks like it holds unique constraint on > (sub_svc_id, parm_id) and part of the updatable join view will look like > > update ( > select b.val,... > from sub_svc_parm a, sub_svc_parm b... > where a.sub_svc_id=b.sub_svc_id > and a.parm_id=10230 > and b.parm_id=12650 > ... > ) set b.val=... > > this alone will make this to be a non-key preserved view from Oracle's > perspective I completely agree. My point was, that even if you get rid of those problems with literal constraints, there is an *additional* problem with key preserving in this case caused by the substr in the join condition. A function on a FK column seems to stop transfering the key preserving information. this works (xpk is unique) update (select x1.xatt att1, x2.xatt att2 from x1,x2 where x1.xfk = x2.xpk) set att1 = att2; this fails with ora-01779 update (select x1.xatt att1, x2.xatt att2 from x1,x2 where x1.xfk||'' = x2.xpk) set att1 = att2; somehow like the olds day "suppress index" feature:) Regards, Jaromir -- //www.freelists.org/webpage/oracle-l
-- Regards, Ranko Mosic Contract Senior Oracle DBA B. Eng, Oracle 10g, 9i Certified Database Professional Phone: 416-450-2785 email: mosicr@xxxxxxxxxx http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html -- //www.freelists.org/webpage/oracle-l