[thelist] database structure for speedier queries

Matt Warden mwarden at gmail.com
Fri Nov 30 18:49:35 CST 2007

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.


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

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list