Re: null or value

From: chet justice <chet.justice_at_gmail.com>
Date: Wed, 16 Sep 2009 15:06:47 -0400
Message-ID: <8311a5b60909161206x1017e253m2b2f09e6074c7088_at_mail.gmail.com>



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> 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>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
> TheHelsinkiDeclaration.blogspot.com
>
> (co)Author: "Applied Mathematics for Database Professionals"
> 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:06:47 CDT

Original text of this message