Soft Deletes via Partitioning

Community friend Gwen Shapira tweeted this the other day

This is a common design pattern in Oracle databases (and many others) use a DELETED_FLAG style column to handle “soft deletes.” Instead of physically removing rows, you mark them as deleted with a flag. This allows applications to retain history, enable easy recovery, and avoid the risks of hard deletes. The trade-off, of course, is that queries need to filter on this flag consistently, or else the “deleted” rows can still sneak into results.

I then replied with

which as a throwaway comment seems to suggest that this is a trivial conversion to make. But (like most things) there is some nuance involved and some things to be aware of if you plan to undertake such a conversion.

I’ll start with a standard table where we have implemented a soft-delete strategy.


SQL> create table t as
  2  select owner, object_name, object_type, min(object_id) object_id, 'N' deleted_ind
  3  from dba_objects d
  4  where object_id is not null
  5  group by owner, object_name, object_type;

Table created.

SQL>
SQL> update t set deleted_ind = 'Y'
  2  where mod(object_id,10) != 1;

78928 rows updated.

SQL>
SQL> alter table t add constraint pk primary key ( object_id);

Table altered.

SQL> alter table t add constraint uq unique ( owner, object_name, object_type);

Table altered.

As the table grows this might become cumbersome. So we will convert the table into a list-partitioned table, where deleted rows are physically stored in a different segment. We isolate active rows into one partition and “deleted” rows into another, making it easier for the optimizer to prune partitions and skip unnecessary data. We can then drop the original table and use a view to expose only the active rows.



SQL> create table t_soft
  2  partition by list (deleted_ind)
  3  ( partition p_active values ('N'),
  4    partition p_deleted values ('Y')
  5  )
  6  as select * from t;

Table created.

SQL>
SQL> alter table t_soft add constraint pk1 primary key ( object_id);

Table altered.

SQL>
SQL> create unique index uq1 on t_soft
  2  ( owner, object_name, object_type);

Index created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create or replace view t
  2  as select * from t_soft
  3  where deleted_ind = 'N';

View created.

We’re done right? Not so fast.

Just partitioning something isn’t a silver bullet. Once you move into list partitioning, enforcing unique constraints becomes more complicated. In Oracle, unique keys must either include the partitioning key or be declared as global. Your business logic might require uniqueness across all rows (regardless of whether they’re deleted or not), or it might want to only enforce uniqueness for only the “active” rows. This raises some challenges.

Since a soft deletion is an update, such an update would require a row to migrate across partitions. By default, that is not allowed, so we need to handle that
Let’s assume we want uniqueness (OWNER, OBJECT_NAME, OBJECT_TYPE) to be for active rows only. That also won’t work with our current solution


SQL> update t
  2  set deleted_ind = 'Y'
  3  where owner = 'SCOTT'
  4  and object_type = 'TABLE'
  5  and object_name = 'EMP';
update t
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> alter table t_soft enable row movement;

Table altered.

SQL> update t
  2  set deleted_ind = 'Y'
  3  where owner = 'SCOTT'
  4  and object_type = 'TABLE'
  5  and object_name = 'EMP';

1 row updated.

SQL> insert into t
  2  values ('SCOTT','EMP','TABLE',1000000,'N');
insert into t
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.UQ1) violated

To work around this, I can alter the index definition to bring along the primary key (OBJECT_ID) but only when the rows are soft-deleted. Now I can allow “duplicate” rows that are deleted, but only a single active row. You can see this by querying both T and T_SOFT for the same object name.


SQL> drop index uq1;

Index dropped.

SQL> create unique index uq1 on t_soft
  2  ( owner, object_name, object_type,  case when deleted_ind = 'Y' then object_id end );

Index created.

SQL> insert into t
  2  values ('SCOTT','EMP','TABLE',1000000,'N');

1 row created.

SQL> insert into t
  2  values ('SCOTT','EMP','TABLE',1000001,'N');
insert into t
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.UQ1) violated

SQL> select * from t
  2  where owner = 'SCOTT'
  3  and object_type = 'TABLE'
  4  and object_name = 'EMP';

OWNER                          OBJECT_NAME                              OBJECT_TYPE              OBJECT_ID D
------------------------------ ---------------------------------------- ----------------------- ---------- -
SCOTT                          EMP                                      TABLE                      1000000 N

SQL> select * from t_soft
  2  where owner = 'SCOTT'
  3  and object_type = 'TABLE'
  4  and object_name = 'EMP';

OWNER                          OBJECT_NAME                              OBJECT_TYPE              OBJECT_ID D
------------------------------ ---------------------------------------- ----------------------- ---------- -
SCOTT                          EMP                                      TABLE                       442851 Y
SCOTT                          EMP                                      TABLE                      1000000 N

I’m still indexing every single row in the table here (which might be what I want for performance reasons) but perhaps I don’t care about the deleted rows and I just want to incur that index overhead for the active rows. You may be thinking that partial indexes might come into play here, but they can’t be used to enforce uniqueness.



SQL> drop index uq1;

Index dropped.

SQL>
SQL> alter table t_soft modify partition p_active indexing on;

Table altered.

SQL> alter table t_soft modify partition p_deleted indexing off;

Table altered.

SQL>
SQL> create unique index uq1 on t_soft
  2  ( owner, object_name, object_type,deleted_ind) local
  3  indexing partial;
create unique index uq1 on t_soft
                           *
ERROR at line 1:
ORA-14226: unique index may not be PARTIAL

And just in case you are thinking to Do-It-Yourself by setting an index partition to unusable, whilst that appears to get your closer, the moment you try touch the deleted rows partition you are going to have some dramas



SQL> create unique index uq1 on t_soft
  2  ( owner, object_name, object_type,deleted_ind) local;

Index created.

SQL>
SQL> alter index uq1 modify partition p_deleted unusable;

Index altered.

SQL>
SQL> insert into t
  2  values ('SCOTT','EMP2','TABLE',1000001,'N');

1 row created.

SQL>
SQL> insert into t
  2  values ('SCOTT','EMP2','TABLE',1000002,'Y');
insert into t
*
ERROR at line 1:
ORA-01502: index 'MCDONAC.UQ1' or partition of such index is in unusable state

Even though this table is partitioned, the “classic” non-partitioned table technique of using a function based index to map unwanted values to NULL, could be an option that you want to explore



SQL> create unique index uq1 on t_soft
  2  ( case when deleted_ind = 'N' then owner end,
  3    case when deleted_ind = 'N' then object_name end,
  4    case when deleted_ind = 'N' then object_type end
  5    );

Index created.

SQL>
SQL> insert into t
  2  values ('SCOTT','EMP3','TABLE',1000010,'N');

1 row created.

SQL>
SQL> insert into t
  2  values ('SCOTT','EMP3','TABLE',1000011,'Y');

1 row created.

SQL> insert into t
  2  values ('SCOTT','EMP3','TABLE',1000020,'N');
insert into t
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.UQ1) violated

Partitioning with soft deletes could be an excellent mechanism for managing large datasets, but this a nuanced area. What works for one system may be a poor fit for another, so carefully test and a understand the trade-offs before moving to production. We haven’t even touched on Oracle’s row archival facility here as an option, which just goes to show how important it it to research all of your options, and test them to see how best meet your requirement.

3 responses to “Soft Deletes via Partitioning”

John Nagtzaam

October 3, 2025 at 3:24 pm

What is wrong with physical deletes? If you want auditing, create a separate table for it including triggers or enable auditing on the table or flashback…

Rich Soule

October 3, 2025 at 8:39 pm

Oracle Flashback Time Travel (Flashback Data Archive) is the real solution here. The table is just a table and developers just use it. The whole “let’s engineer a software solution for our lack of hardware” introduces technical debt that’s requires 100% of developers to write 100% correct code 100% of the time. Now your indexes just work, your constraints just work, nobody has to make sure the table isn’t referenced directly and only the view is used, you don’t have to worry about moving rows between partitions, etc.

Right?

MS

October 7, 2025 at 6:10 pm

Getting back to the original problem, you could just add a VPD policy to hide deleted rows for those people/applications that shouldn’t see them. Then you wouldn’t need to bother about creating views and you could still do all the partitioning stuff above in exactly the same way if you really wanted to.