Hi,

> I have got a small table with about 5.000 records (large
> rows) and approx. three inserts a day. Sometimes records
> from this table have to be deleted and my customer wants
> that these are not physically deleted, but only marked
> as 'deleted'.
>
> Of course this means that the where clause of many queries
> have to be modified:
> SELECT x FROM Y WHERE IsDeleted = 'N'.
>
> I am wondering what is more expensive for the engine: to
> scan the whole table or work with an index on the IsDeleted
> column.

This column will only have values "N" and "Y" - resulting in
very poor selectivity... I wouldn't index that column.

> As I said, the table is quite small, but there are lots of
> selects on it (users, SPs, report generators).
>
> I am sure some of you have already made some experiences with
> such a scenario and I would be glad if you could share them
> with me ;-)

btw, perhaps - in order to avoid changing all queries, you can
rename the table (creating a new one, changing FKs etc) and
create a view with the same name as the table, but has a
WHERE IsDeleted = 'N' clause appended.


With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."