Discussions

Expand all | Collapse all

OMNISCI DB system HDD space is not reclaiming after the delete query

  • 1.  OMNISCI DB system HDD space is not reclaiming after the delete query

    Posted 10-01-2019 08:28
    hi all

     We request your advise on the below points .


    point 1: what is the best way  for a massive delete from a table.

    point 2: how we can reclaim the space from HDD after the deletion process.

    point 3: if there is no straight way for point 2  can you pls suggest a better workaround. 

    point4:our major worry is if, the data is not being deleted from OS level ,on a long run ,we have increase our storage without any gain.

    point5: we would likes to know how the "optimize table"  command will help the performance improvement after deletion


    thanks in advance.
    #Core


  • 2.  RE: OMNISCI DB system HDD space is not reclaiming after the delete query

    Posted 10-02-2019 04:14
    Hi @Ishika [[Unknown]],

    1) On databases like Oracle or SqlServer, on a massive delete, I prefer to use an IAS, because it is more performant and to get a more compact table/indexes. On Omnisci the delete operation is a bulk one in his nature, so it's extremely performant, but it doesn't remove the deleted records; it just build a bitmap to track the one that has been deleted.
    In the process, the metadata isn't updated, so if you remove all rows where the field_1 values > 100, the system still believe the filed is containing values between 1 and 400; this could degrade the performance of subsequent queries

    2) Do you mean that after delete of half the records, occur a shrinking of files containing table's data? The space now isn't reclaimed; also running optimize table doesn't help 

    3) As a workaround you can do an INSERT AS SELECT (or a CTAS) on a new table, followed by ALTER TABLE RENAME, then a DROP or a truncate of the old table. You can schedule a script doing that dialy or weekly

    4) you worries are justified

    5) the optimize table command, under some circumstances helps you to reuse the disk space after a massive delete on subsequent INSERTs, and rebuild statistics on table's chunks needed by the optimizer
    ​​


  • 3.  RE: OMNISCI DB system HDD space is not reclaiming after the delete query

    Posted 10-03-2019 01:49
    @Ishika [[Unknown]],

    after some internal discussion, I can anticipate that table will get a new parameter that will reclaim the space after massive delete in the upcoming . The development is in an stage, and I will warn you when it will be ready



  • 4.  RE: OMNISCI DB system HDD space is not reclaiming after the delete query

    Posted 10-03-2019 06:29
    Hi,

    I think this feature will really boost the use case of OmniSci.


  • 5.  RE: OMNISCI DB system HDD space is not reclaiming after the delete query

    Posted 10-03-2019 07:22
    hi @Candido Dessanti
      thanks a lot for your feedback 


    ​​