Thread: Can inherited tables help in this case?

Hi,

We have a table, lets say of products.  Some products get deleted from
time to time, using a soft delete (i.e. they have a date deleted
attribute which is set when the the record is deleted.  We never
physically delete the records.  Is there a way that table inheritance
can be used to help us speed up queries?  In other words, if we had a
table of products and a table of deleted_product which inherits from
products, can we create initially a record in the products table and
when it is marked as deleted make it a deleted_product record instead
of a product record so we select on products only?

Avi

On Thursday 19 Jun 2003 6:37 am, Avi Schwartz wrote:
> Hi,
>
> We have a table, lets say of products.  Some products get deleted from
> time to time, using a soft delete (i.e. they have a date deleted
> attribute which is set when the the record is deleted.  We never
> physically delete the records.  Is there a way that table inheritance
> can be used to help us speed up queries?

You might find a partial index to be of use. Something like:

CREATE INDEX my_index ON products (prod_id) WHERE delete_date IS NULL;

--
  Richard Huxton