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. ----- Original Message ----- From: "Tomasz Myrta" <[EMAIL PROTECTED]> To: "Alexander Priem" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 9:03 AM Subject: Re: [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'. > > <cut> > I think the best choice for your case is using conditional indexes. It > should be much better than indexing 'deleted' field. I don't know on > which exactly fields you have to create this index - you have to check > it by yourself - what do you have in "where" clause? > > Example: > create index some_index on your_table(id_field) where not deleted; > > > Regards, > Tomasz Myrta > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly