Delete rows from partitioned database where table is too large to load into memory

You can’t directly delete rows of data from a partitioned database, so the method I have seen is:

• Load table into memory using where clause on date

• Delete rows from table in memory

  MEDevel.com: Open-source for Healthcare and Education

  Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

  Visit Medevel

• Resave table to disk, overwriting old data

But this assumes that the partition can be loaded into memory. If it can’t, what can I do then?

I have looked at the dbmaint.q script but that doesn’t seem to have solutions to modify rows, just columns.

>Solution :

Your best bet would be to load the specific column(s) on which you will determine which rows to delete, and find the indices of the rows to retain. Then iterate through each column and delete entries in each column based on those indices. Something like:

/equivalent to delete from myTab where date=2022.01.02, myCol=`xx
keep_idx:where not`xx=get`:2022.01.02/myTab/myCol;

/iterate through each col, remove/index and write back down
{c set @[;keep_idx]get c:` sv`:2022.01.02/myTab,x}each get`:2022.01.02/myTab/.d

However there’s some caveats you need to handle:

• You may lose any attribute that was on the original column, you would have to check for attributes and restore afterwards
• If compression was used on the original write you should maintain the same compression on the rewrite