Re: Update

  • From: "Ranko Mosic" <ranko.mosic@xxxxxxxxx>
  • To: jaromir@xxxxxxxxxxxx
  • Date: Tue, 13 Mar 2007 13:39:35 -0400

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


Other related posts: