All Forums UDA
Elle 16 posts Joined 11/07
30 Jun 2009
managing deleted rows with views

Hello,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.Linda

robpaller 159 posts Joined 05/09
30 Jun 2009

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.

You must sign in to leave a comment.