Re: null or value

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: Randy.Steiner@xxxxxxxx
  • Date: Thu, 17 Sep 2009 01:30:36 -0600


Everyone else is going to respond to this question focusing on the issues of NULL-ness or NOT NULL-ness, etc...

I'd like to step back a bit and reflect on a different concern, one of design:  Is this a data warehouse application, and the proposed ACTIVE_FLAG column an indicator of which "version" of a record is supposed to "active" when there are multiple versions, such as...

         ID           EFF_DT      ACTIVE_FLAG
=========== ================ ================
     123456      02-Aug 2009                Y
     123456      17-Jun 2007                N
     123456      23-Dec 2005                N
     123457      11-Mar 2009                Y
     123457      08-Oct 2006                N
     123458      11-Jan 2005                Y
     123459      01-Jul 2008                Y
     123459      12-May 2004                N

...(and so on)...

So, the row with the latest EFF_DT value for each distinct ID value has ACTIVE_FLAG = 'Y', while all the other (older) rows with the same ID value have ACTIVE_FLAG = 'N'?  And each time a newer row is added for a particular ID value, then the existing row with ACTIVE_FLAG = 'Y' will need to be updated?

If this is the case, then please know that this method of using an ACTIVE_FLAG to identify the "current" or "active" row will become one of the biggest ETL performance problems in your data warehouse, and even worse it will prevent you from making use of the time-variant nature of your data in order to set tablespaces containing older data to READ ONLY.  Thus, as your data warehouses grows in volume, it will continue to be necessary to backup the entire database on a regular basis, and the entire database will continue to reside on expensive "tier 1" storage, and your loins will become barren, and locusts will blanket the earth, and...  sorry, got kind of carried away there...

But if this is not the case, then never mind...... :-)

Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   =
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt

Steiner, Randy wrote:

I have a table with 1 – 2 million records.  I need to add a column to indicate if the records is active or not.  I would guess that only 1,000 of the records would not be active.  Should I make one of the values null?  So I could put a Y or Null?  Or put Y or N?

Would a b-tree or bitmap index do any good?


Generally I would want to see all the records that are active






Other related posts: