On 11/30/07, Joshua Olson <joshua at waetech.com> wrote: > > -----Original Message----- > > From: Matt Warden > > Sent: Friday, November 30, 2007 6:31 PM > > > > When I finished this list, I would turn around and tell you to forget > > about it and add an dte_archived field (rather than a yes/no bit). > > Null means active, non-null means archived. At any time you could > > implement a batch job that takes (sysdate - dte_archived) > x records > > and moves them to another table or data store, possibly sitting on > > cheaper storage. > > All, > > Agreed, but be careful of one thing. Some databases don't index NULL values > in a manner that makes querying for NULL vs. non-NULL as fast as it could > be. It's almost certainly better to add a flag AND the date field if you > are generally only interested in whether or not an item is flagged, and not > necessarily when it was flagged. Joshua, I did consider this, but ignored the issue because I don't think the dbms would use an index for this field anyway. Indexing a column with only two distinct values is unlikely to be useful unless the percentage of unarchived is very small. I think you may also need a dbms like Oracle that supports persistent bitmap indexes. Of course, without an execution plan, I'm just guessing... -- Matt Warden Cincinnati, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.