Re: null or value
- From: chet justice <chet.justice@xxxxxxxxx>
- To: toon.koppelaars@xxxxxxxxxxx
- Date: Wed, 16 Sep 2009 15:06:47 -0400
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@xxxxxxxxxxx> 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@xxxxxxxx>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@xxxxxxxxxxx
> 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]
Other related posts:
