Results 1 to 4 of 4

Thread: row count

  1. #1
    Join Date
    Nov 2003
    Location
    Moldova
    Posts
    25

    Unanswered: row count

    Hi !
    I want to delete about 30 000 000 rows from table.
    I can't do this in one transaction.
    Additionaly, table has not any unique identifier.
    In MS SQL I'd like to use "SET ROWCOUNT"
    But INFORMIX ?
    What must I do ?

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Unhappy

    30 million rows, in one transaction?

    Well, "I'm not surprised." Can you imagine what the system would have to do, what it would have to copy and keep and then throw-away, in order to roll-back that?!

    Time to cook up a new strategy. Your computer will love you for it.

    For bulk operations like that, you simply need to arrange things so that you purge about 100,000 records per transaction and then commit. Or maybe 10,000. You'll also need to section your data in some way so that each delete-query only passes through a portion of the database: some way of apportioning it so that: you don't uselessly troll through already-visited territory and yet, the fact that you're deleting a bunch of records won't cause any candidates to be overlooked.

    Transactions are important, on the one hand, because they imply locking and buffering which is very critical for all kinds of bulk-data operations. But they also imply "rolling data back," which can quickly balloon into rollback-files of unmanageable size. (They can also lock-out other users, in the manner of a 9-zillion pound gorilla with huge feet.) So you need to devise a strategy that balances between these issues.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Rodion:

    30 million rows to delete ?!!!

    Ok... By the way what is the overall rows present in the table? What I'm trying to say is that what remains after this mammoth delete operation is less that what has been pruned off, it would be better to copy the balance to a flat file using UNLOAD statement or to another base table; and ultimately drop the original table itself in one shot.

    Or you may decide to go on rowid basis to implement the delete job. You need to generate a flat file with the specification similar to below:

    output to 'deljob' without headings select 'delete from tablex where rowid=',rowid,' ;' from tablex ;

    Once this flat file is ready you can split it using OS utility. e.g.

    split -l100000 deljob

    will generate 300 files approximately, assuming you have around 30 million lines in deljob file.

    Submit the split files to dbaccess for execution.

    Regards,
    Shriyan

    "Vision is the art of seeing things invisible."

  4. #4
    Join Date
    Nov 2003
    Location
    Moldova
    Posts
    25

    Thumbs up Thank You

    Thank You
    I solved this problem


    CREATE PROCEDURE My_delete(
    rowcount INTEGER, _date datetime year to second
    )
    RETURNING datetime year to second

    define _time datetime year to second;


    DEFINE count INTEGER;

    LET count = 0;
    FOREACH cur1
    FOR SELECT time INTO _time
    FROM My_table
    where time < _date

    delete from My_table
    where current of cur1;

    LET count = count + 1;
    IF count >= rowcount THEN
    EXIT FOREACH;
    END IF;
    END FOREACH;
    END PROCEDURE;

Posting Permissions

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