Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Best Way to iterate over a big table, change row-by-row and write back?

    Hello,

    I want to change a table of 400000 on a row by row basis. There are some conversion rules, like if column a contains a certain value, column b will contain another certain value in the future. There are no rule-dependancies between the rows.

    What is the best way to do this with Oracle, trying to avoid "ORA-01555: snapshot too old" errors?

    My first idea was to use SELECT FOR UPDATE, immediately writing back the changed values after a row is read from the resultset. The problem is that the Oracle JDBC driver does not seem to support updatable result sets (don't know about PerlBD).

    My second idea was to SELECT the whole table with one connection, and use another connection to UPDATE rows in transactions of about 200 rows or so. With this solution I am running into ORA-01555 after about 80000 converted rows.

    My collegues are suggesting reading the whole table into memory, DROPing it, CREATEing it from scratch and INSERT each row after the rules are applied. I'd like to avoid this because of the errors that could creep in. Besides, system memory could run out.

    Any idea?

    Regards,

    Andreas

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

    Re: Best Way to iterate over a big table, change row-by-row and write back?

    Ideally you want to do this in a single transaction on all 400000 rows - breaking into smaller transactions causes its own problems, as you are finding. Instead of trying to make the transactions smaller, allow sufficient rollback space for the single transaction.

    If possible, it should be done as a single SQL statement:

    UPDATE table
    SET col = <complicated expression>
    WHERE ...;

    If that isn't possible due to the nature of the update, then the next best is to write the program in PL/SQL (not Java, Perl or any other client programming language). Use BULK COLLECT with LIMIT and FORALL to process arrays of a few 100 rows at a time. Again, process all the data as a single transaction:

    PHP Code:
    ...
    open cur;
    loop

      fetch cur bulk collect into v_rowid
    v_col1v_col2, ... limit 200;
      
    l_notfound := cur%notfound;

      for 
    i in 1..v_rowid.count loop
        
    -- process record
      end loop
    ;

      
    forall i in 1..v_rowid.count loop
        update tab set col1 
    v_col1(i), ...
        
    where ROWID v_rowid(i);

      exit 
    when l_notfound;

    end loop;

    commit;
    ... 

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Hello Tony,

    thanks for your answer.

    Formulating the conversion rules in PL/SQL will not be very easy, because I don't know PL/SQL and the rules involve many String operations. We have comma separated lists of values in a couple of VARCHAR columns - some if the values shall be deleted, some appended. I heard that PL/SQL is not very powerful in the String manipulation area.

    Apart from that problem, I would opt for the single UPDATE solution, too.

    But if the Conversion itself has to be done in a client langauge, what solution would you choose?

    Regards,

    Andreas

  4. #4
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by Goonie
    Hello Tony,

    thanks for your answer.

    Formulating the conversion rules in PL/SQL will not be very easy, because I don't know PL/SQL and the rules involve many String operations. We have comma separated lists of values in a couple of VARCHAR columns - some if the values shall be deleted, some appended. I heard that PL/SQL is not very powerful in the String manipulation area.

    Apart from that problem, I would opt for the single UPDATE solution, too.

    But if the Conversion itself has to be done in a client langauge, what solution would you choose?

    Regards,

    Andreas
    PL/SQL is probably one of the easier procedural languages I've learned and used. I believe it might have the string manipulation operators you'll need. It has; find a string or character within a string, soundex, length of a string, padding, etc. All the ref material you'll need is free online at Oracle's site. At least give it a once over.
    Oracle - DB2 - MS Access -

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not the person to ask about client languages. I'd say if you are going to use one, use the one you know best. It should have little bearing on the performance of the database operations. If you know C, maybe use Oracle Pro*C?

    I'd be interested to hear more about the sort of string operations you need to perform, though. PL/SQL certainly isn't the most sophisticated string parser (until 10G, when it has regular expressions), but "comma separated lists of values" are something it can handle with ease using the INSTR and SUBSTR functions.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by N-ary
    PL/SQL is probably one of the easier procedural languages I've learned and used. I believe it might have the string manipulation operators you'll need. It has; find a string or character within a string, soundex, length of a string, padding, etc. All the ref material you'll need is free online at Oracle's site. At least give it a once over.
    Agreed - and not a squiggly bracket to be seen!

  7. #7
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by andrewst
    Agreed - and not a squiggly bracket to be seen!
    OMG, I hope the JAVA script puppies don't read this
    Oracle - DB2 - MS Access -

  8. #8
    Join Date
    Feb 2004
    Posts
    4
    Well okay here is an example:

    One of our columns contains a set of the following values, separated by commas:

    1001
    1002
    1003
    2001
    2002
    2003

    Example: 1001,2001,2002

    To make it worse, in some cases there may be empty Strings embedded as a value, like in
    "1001,,2001" or
    "1001,2001,". These empty values should be ignored and can (but need not) be stripped.

    The rules for this column is as follows:

    - if one of the values 1001,1002 or 1003 is present, only 1001 (of the 1000 range) should be present after the conversion.
    - if one of the values 2001,2002 or 2003 is present, only 2001 (of the 2000 range) should be present after the conversion.

    Example: "1001,2001,2002" would convert to "1001,2001"

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Just for fun, here's a PL/SQL solution:

    PHP Code:
      1  declare
      
    2    v_input_string varchar2(100) := '1001,,2002,2003';
      
    3    v_table parse.varchar2_table;
      
    4    v_nfields integer;
      
    5    v_1001 varchar2(5);
      
    6    v_2001 varchar2(5);
      
    7    v_output_string varchar2(100);
      
    8  begin
      9    parse
    .delimstring_to_tablev_input_stringv_tablev_nfields);
     
    10    for i in 1..v_nfields
     11    loop
     12      
    if v_table(iin ('1001','1002','1003'then
     13        v_1001 
    := ',1001';
     
    14      end if;
     
    15      if v_table(iin ('1001','1002','1003'then
     16        v_2001 
    := ',2001';
     
    17      end if;
     
    18    end loop;
     
    19    v_output_string := substr(v_1001||v_2001,2);  -- Skip leading comma
     20    dbms_output
    .put_line(v_output_string);
     
    21end;
    SQL> /
    1001,2001

    PL
    /SQL procedure successfully completed
    It cheats a little, because it uses a handy "parse" package that I already wrote (comes in handy quite often) - see attachment.
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Of course, one of the nice things with PL/SQL is that you could encapsulate the string parsing logic above in a stored function and then just do this:

    UPDATE mytable
    SET column = my_function( column );

Posting Permissions

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