Re: Update

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_at_db-nemec.com> 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
>
>
>
> --
> http://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_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
--
http://www.freelists.org/webpage/oracle-l