Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    4

    Lightbulb Unanswered: SQL stored procedure for oracle buffs

    HI
    I am writing a stored procedure to compare two tables Transform and Input.I need to match 2 fields "NAX" and "PBX" between the two tables.
    If I find a match ,then I need to multiply the field called "ConstMultiplier"
    in table Transform times the value in the field called "Revenue" in table Input and put their product into a new field in table Input called "RelativeNBX".
    The thing is both these tables contain more than 5 million rows of data.

    Any suggestions.
    Thanks a lot people

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: SQL stored procedure for oracle buffs

    i would do it in the following way, my code fragment is indicating.
    If I got you right, then the (only ?) matching criteria between the tables is pbx=nax: sorting 5 millions of rows you will need an index on these columns, else you might run into problems.
    you should also be aware of the fact, that the cursor for update is more or less locking the whole table - so this should be rather an exlusive job.

    hope my hints helped.
    r

    create or replace procedure compare is
    begin

    cursor cr_input is
    select * from input
    where <criteria???>
    order by pbx
    for update;

    cursor cr_transform is
    select * from transform
    where <criteria???>
    order by nax;

    vInput cr_input%rowtype;
    vTransform cr_transform%rowtype;

    open cr_input;
    open cr_transform;

    fetch cr_input into vInput;
    fetch cr_transform into vTransform;

    vStillData := cr_input%FOUND OR cvAkt%FOUND;

    while vStillData loop

    IF vInput.pbx < vTransform.nax THEN
    -- no match, we need the next input
    fetch cr_input into vInput;

    ELSIF vInput.pbx = vTransform.nax THEN
    -- the keys match.
    -- her we have to muliple the factors ...

    If I find a match ,then I need to multiply the field called "ConstMultiplier"
    in table Transform times the value in the field called "Revenue" in table Input
    and put their product into a new field in table Input called "RelativeNBX".

    update input
    set relativenbx=vTransform.constmultiplier*vInput.reve nue
    where current of cr_input;

    fetch cr_input into vInput;
    fetch cr_transform into vTransform;

    ELSE -- vInput.pbx > vTransform.nax
    -- no match, we need the next transform record
    fetch cr_transform into vTransform;

    END IF;
    vStillData := cr_input%FOUND OR cvAkt%FOUND;
    END LOOP;

    CLOSE cr_input;
    CLOSE cr_transform;

    -- commit/rollback ???
    commit work;

    end compare;
    /
    show errors

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL stored procedure for oracle buffs

    If there is always only 0 or 1 matching Transform record for each Input, then you can do this in a single UPDATE statement:

    UPDATE Input I
    SET I.RelativeNBX = I.Revenue *
    ( SELECT T.ConstMultiplier
    FROM Transform T
    WHERE T.NAX = I.NAX
    AND T.PBX = I.PBX
    )
    WHERE EXISTS
    ( SELECT 1
    FROM Transform T
    WHERE T.NAX = I.NAX
    AND T.PBX = I.PBX
    );

  4. #4
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: SQL stored procedure for oracle buffs

    I agree on your approach - the only real difference to my approach ist:
    I did the more complicated cursor loop with intention, so that under no circumstances a JOIN between input and transform (both more than 5 million rows as I understand) takes place.
    maybe an "explain plan" on your statements gives a better hint on what the implications really are.

    rgds, r


    Originally posted by andrewst
    If there is always only 0 or 1 matching Transform record for each Input, then you can do this in a single UPDATE statement:

    UPDATE Input I
    SET I.RelativeNBX = I.Revenue *
    ( SELECT T.ConstMultiplier
    FROM Transform T
    WHERE T.NAX = I.NAX
    AND T.PBX = I.PBX
    )
    WHERE EXISTS
    ( SELECT 1
    FROM Transform T
    WHERE T.NAX = I.NAX
    AND T.PBX = I.PBX
    );

  5. #5
    Join Date
    Jul 2002
    Posts
    4
    ok I have written some syntax .
    I hope you guys can check to see if there is error in some of my methods.


    cursor cr_input is

    select input.cc ,input.year,input.app,input.spcat,input.sm,
    input.les,input.region,input.fes,input.revenue,inp ut.sc,
    transform.channel1 ,transform.channel2,transform.channel3,
    transform.channel4,transform.channel5,transform.ch annel6
    from input,transform
    where
    input.cc = transform.cc and
    input.year = transform.year and
    input.app = transform.app and
    input.region = transform.region and
    input.fes = transform.fes and
    input.sc = transform.sc and
    input.sm = transform.sm

    for insert;



    cursor cr_copyinput is
    select * from input
    where Not In --------------------//is this right(i need to find values in
    ------------------------- table Input not in table Transform )
    (input.cc = transform.cc and
    input.year = transform.year and
    input.app = transform.app and
    input.region = transform.region and
    input.fes = transform.fes and
    input.sc = transform.sc and
    input.sm = transform.sm)

    for insert;



    vinput cr_input%rowtype;
    vcopyinput cr_copyinput%rowtype;


    open cr_copyinput;
    fetch cr_copyinput into vcopyinput;

    vstillinputdata :=cr_copyinput%Found OR cvAKT%Found;

    while vstillinputdata loop
    insert into output values (vcopyinput.cc ,vcopyinput.year,
    vcopyinput.app ,vcopyinput.region,
    vcopyinput.fes,vcopyinput.sc ,
    vcopyinput.sm ,"1");
    //if not found put in "1"

    fetch cr_copyinput into vcopyinput;
    vstilldata := cr_input%Found OR cvAKT%Found; {Is this redundant}
    endloop



    open cr_input;
    fetch cr_input into vinput;


    vstillinputdata :=cr_copyinput%Found OR cvAKT%Found;

    vstilldata := cr_input%Found OR cvAKT%Found;

    while vstilldata loop


    insert into output values (vinput.cc ,vinput.year,
    vinput.app ,vinput.region,
    vinput.fes,vinput.sc ,
    vinput.sm ,(vinput.revenue * vinput.channel1);

    insert into output values (vinput.cc ,vinput.year,
    vinput.app ,vinput.region,
    vinput.fes,vinput.sc ,
    vinput.sm ,(vinput.revenue * vinput.channel2);

    insert into output values (vinput.cc ,vinput.year,
    vinput.app ,vinput.region,
    vinput.fes,vinput.sc ,
    vinput.sm ,(vinput.revenue * vinput.channel3);

    insert into output values (vinput.cc ,vinput.year,
    vinput.app ,vinput.region,
    vinput.fes,vinput.sc ,
    vinput.sm ,(vinput.revenue * vinput.channel4);

    insert into output values (vinput.cc ,vinput.year,
    vinput.app ,vinput.region,
    vinput.fes,vinput.sc ,
    vinput.sm ,(vinput.revenue * vinput.channel5);

    insert into output values (vinput.cc ,vinput.year,
    vinput.app ,vinput.region,
    vinput.fes,vinput.sc ,
    vinput.sm ,(vinput.revenue * vinput.channel6);

    fetch cr_input into vinput;
    vstilldata := cr_input%Found OR cvAKT%Found;

    endloop

    close cr_input;
    close cr_copyinput;
    commit work;
    end compare;
    /
    show errors

  6. #6
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Seems an awful lot of code there for a simple update. I could be missing something, but...
    If nax and pbx are indexed (preferably unique) and assuming you don't want to join the two tables, I'd do it like this:

    Code:
    DECLARE  
    
      CURSOR c1 is
        select nax,
               pbx 
          from input;
    
      commit_every number := <NUMBER_OF_UPDATES_PER_COMMIT>;
      i number;
      
    BEGIN
      
      FOR c IN c1 LOOP
      
        update transform
          set relativenbx = constmultiplier * c.revenue
          where nax = c.nax
            and pbx = c.pbx;
      
       if i % commit_every = 0 then
         commit;
       end if;
       i++;
      
      END LOOP;
      
      commit;    
      
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •