Re: null or value

From: Mike Haddon <m.haddon_at_tx.rr.com>
Date: Wed, 16 Sep 2009 18:54:47 -0500
Message-ID: <4AB17AC7.4000900_at_tx.rr.com>

Even though some databases (MySQL, SQL Server, etc,..), may represent NULL differently. Oracle has always defined NULL as the ABSENCE of a value. That is why "= NULL" and "!= NULL" won't work because nothing = NULL.

Mike

> 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?
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 16 2009 - 18:54:47 CDT