[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.

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.



More information about the thelist mailing list