What happens when I run a very large delete?

Help
1

In the case of Oracle, the maximum size of a transaction seems to be limited by the maximum size of an undo segment that can store undo. That is, the maximum size is not related to the memory size.
However, the behavior appears to be very different for singlestore. In the case of RowStore, the maximum size can naturally be thought of in relation to memory size.

I’m curious what happens when a very large transaction occurs on a table using columnstore. How is the maximum transaction size determined, and how does singlestore handle that transaction for rollback? I’m particularly curious about the very large delete .

1 Like
2

For columnstore tables, you can delete more data than will fit in RAM; you can delete any amount of rows from very large tables in a single SQL DELETE statement. Delete works by marking rows as deleted in a delete bitmap.

If all rows in a segment are deleted, the whole segment will be marked as gone.

When the background merger runs, segments with a lot of deleted rows will be compacted into new segments, with the actual deleted data completely gone.

Details, including terminology, are given here.

3 Likes