managing deleted rows with views


managing deleted rows with views


Our data warehouse marks deleted records (soft deletes) and then removes them from the tables at a later time. Ideally, we would like the ability to make the delete process seamless from the users by using a view that they would access that only contains active records. I've used this method before with another DBMS that supported bit map indexes on a active/not-active column. I tried implementing, but without a secondary index, table scan were occurring. Here's my dilemma: I don't want to add an index to the TD tables because that would prevent using mload and we don't have enough time to drop/re-create an index. Is there a way to achieve this?

Thanks in advance for any ideas and suggestions.


Re: managing deleted rows with views

A FTS may be unavoidable given the cardinality of the index values. An index value with a high cardinality may be more efficient to resolve via a FTS instead of traversing the index.

Regardless, make sure that you are qualifying on the active/inactive column with an equality comparison. (e.g. Active_Record > 0 or Active_Record = 1). Inequality will nearly always force a table scan.