Re: null or value

Null does not mean "unknown" necessarily. True, in some cases that's what it means. In other cases it can mean 'non applicable'. However, it really means whatever you define it to mean. For example, if you have table CUSTOMER, column CREDIT_STATUS, you can make the rule that when CREDIT_STATUS is NULL that means 'BAD CREDIT'. As long as all parts of the application 'know' that null means 'BAD CREDIT', then that's what it means. I, would, of course never do this, but I'm just trying to illustrate a point.

The usage of NULL to prevent entries into an index is a common thing to do. I would also consider using a bitmapped index. Just my 2 cents. Mike

On Wed, Sep 16, 2009 at 12:06 PM, chet justice <chet.justice_at_gmail.com>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> 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