Re: null or value

From: Julio Aguilar-Chang <jachang_at_lanl.gov>
Date: Wed, 16 Sep 2009 13:13:46 -0600
Message-ID: <4AB138EA.5030100_at_lanl.gov>


I must agree with Chet. If the two possible values are active or inactive, then populate the column with one of those two values. If you have a null, then you don't know whether that was done on purpose, or did the app not populate the column correctly - you just don't know why there is a null.

Julio.

chet justice wrote:
> As I'm not a real DBA (I only play one on occasion at work), I won't
> (can't) get into which type of index would be better.
>
> However, I do object from a data perspective, to defaulting the
> inactive rows to NULL. NULL = UNKNOWN correct? In this case, it's
> not unknown, it's an inactive record. Use 'N' or whatever identifier
> you choose.
>
> Or am I just being a tad obsessive?
>
> chet
>
> On Wed, Sep 16, 2009 at 2:56 PM, Toon Koppelaars
> <toon.koppelaars_at_rulegen.com <mailto:toon.koppelaars_at_rulegen.com>> wrote:
>
> Assuming that by far the majority of those rows fall into the
> "Inactive" category, I think there are a few options:
>
> 1) Add a nullable column to that table, and put a 'Y' (or
> whatever) into it to indicate the row is an Active one. Put NULL's
> in all others. Then create a regular b-tree index on that column
> for quick access of the Active rows.
> By not putting 'N' into all the inactive rows, you'll ensure this
> index is going to remain small (compared to other indexes on that
> table).
>
> 2) (assuming there's an PK on that index), create a new table, and
> only store the PK-values in that table to indicate the ones that
> are active. So PK of this new-table = PK of the big table. New
> table has small set of PK's that can be used to drive nested-loops
> to the big one.
>
> 3) Variation on 1: *do* put 'N'-s in for the inactive rows, but
> create a function based index that has no-entries for these rows.
> Eg. index 'case [column] when 'Y' then 'Y' else null end
>
>
>
> Toon
>
>
>
> On Wed, Sep 16, 2009 at 8:43 PM, Steiner, Randy
> <Randy.Steiner_at_nyct.com <mailto:Randy.Steiner_at_nyct.com>> 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
>
>
>
> Thanks
>
> Randy
>
>
>
>
>
>
> --
> Toon Koppelaars
> RuleGen BV
> +31-615907269
> Toon.Koppelaars_at_RuleGen.com
> www.RuleGen.com <http://www.RuleGen.com>
> TheHelsinkiDeclaration.blogspot.com
> <http://TheHelsinkiDeclaration.blogspot.com>
>
> (co)Author: "Applied Mathematics for Database Professionals"
> www.RuleGen.com/pls/apex/f?p=14265:13
> <http://www.RuleGen.com/pls/apex/f?p=14265:13>
>
>
>
>
> --
> chet justice
> http://oraclenerd.com [blog]
> http://twitter.com/oraclenerd [twitter]
>

-- 



--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 16 2009 - 14:13:46 CDT

Original text of this message