Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    ROMANIA
    Posts
    3

    Unanswered: about compressing or table size, purge

    I have a problem with Informix. I have a large table (about 100.000 rows). I need to delete about 70.000 from the table running a sql query like
    delete from mytable where idcount<70000

    It deletes but ... the records are still there. Is there a command like purge or something? Maybe a compress command or something that could help me?
    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    alter index <idxname> to cluster;

    OR

    alter fragment on table <table_name> init in <new_dbspace>;

    However make sure that you have enough logical-log
    space or turn the database logging off for this task
    if possible.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  3. #3
    Join Date
    Mar 2002
    Posts
    112

    Re

    Please check it out , your transaction isn't rollback

  4. #4
    Join Date
    May 2003
    Location
    ROMANIA
    Posts
    3

    about

    I think that there is no rollback. I have read a lot from the internet and I have found that the only way to really purge the deleted records are to export the current info, drop the table and restore .

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    This is impossible,

    if you delete rows out of an infrmix database they are deleted.

    why don't you try it this way:

    begin work;
    delete from table where count < 70000;
    commit;

    You could always first check how many rows and what rows will be deleted. Use
    select count(*) from table where count < 70000

    Unlike Oracle, Informix will fill up this deleted space with the next inserts in your table. No space is wasted.
    No online purge is available in informix. There is always an exclusive lock on the table when you do a reorganization.
    ALTER FRAGMENT is the best and easiest way.
    rws

  6. #6
    Join Date
    May 2003
    Location
    ROMANIA
    Posts
    3

    continue

    Because I'm new to informix I cannot say that it isn't true. Can you write a complete command for alter fragment.

    All that I do is

    delete from events where id<50.000

  7. #7
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Are you sure you put a dot?
    50.000 or 50000?

    Makes a big difference...

    You can find the complete SQL syntax at the syntax guide. Go to
    http://www.ibm.com/software/data/informix
    library
    Documentation on Informix Products.
    Informix Dynamic Server (Your Version).

    An example would be, if your table is not fragmented...

    ALTER FRAGMENT ON events INIT IN dbspace;

    where dbspace is the location where you want to put the table.
    If your feeling is that it's a big table, you could fragment it over different dbspaces. 100000 rows is not so big though. Depends a bit on the size of the row.
    rws

Posting Permissions

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