Remember to consider partial indexes:
eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL
Chris
----- Original Message -----
Sent: Friday, August 29, 2003 2:52 PM
Subject: [PERFORM] Indexing question
Hi all,
I have some tables (which can get pretty large) in which I want to record 'current' data as well as 'historical' data. This table has fields 'deleted' and 'deleteddate' (among other fields, of course). The field 'deleted' is false be default. Every record that I want to delete gets the value true for 'deleted' and 'deleteddate' is set to the date of deletion.
Since these tables are used a lot by queries that only use 'current' data, I have created a view with a where clause 'Where not deleted'. Also, I have indexed field 'deleted'.
I did this this because I read somewhere that fields that can contain NULL values will NOT be indexed.
Is this true?
Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL by default and to a DATE in the case of a deleted record? I could then index the field 'deleteddate' and create a view with where clause 'Where deleteddate is null'.
Would this give the same performance as my current solution (with an indexed boolean field 'deleted') ?
I cannot test this myself at the moment as I am still in a design phase and do not have a real server available yet...
Thanks in advance,
Alexander Priem