So if I understand correctly I could ditch the 'deleted' field entirely and
use just the 'deleteddate' field. This 'deleteddate' field would be NULL by
default. It would contain a date value if the record is considered
'deleted'.

The index would be 'create index a on tablename(deleteddate) where
deleteddate is null'.

I could then access 'current' records with a view like 'create view x_view
as select * from tablename where deleteddate is null'.

Is that correct? This would be the best performing solution for this kind of
thing, I think (theoretically at least)?

Kind regards,
Alexander Priem.

Near, but not exactly. You don't need field deleted - it's true.


Your example:
create index a on tablename(deleteddate) where deleteddate is null
we can translate to:
create index a on tablename(NULL) where deleteddate is null
which doesn't make too much sense.

Check your queries. You probably have something like this:
select * from tablename where not deleted and xxx

Create your index to match xxx clause - if xxx is "some_id=13", then create your index as:
create index on tablename(some_id) where deleteddate is null;


Regards,
Tomasz Myrta


---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings

Reply via email to