RE: null or value
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Sep 2009 15:04:42 -0400
Reading the OP to say that only active rows are of interest and that
only 1,000 out of 1,000,000 - 2,000,000 rows are INACTIVE then no index
in existence will help find the active rows faster unless that index is
built on other columns of interest to the query.
-- Mark D Powell --
Phone (313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Toon Koppelaars
Sent: Wednesday, September 16, 2009 2:56 PM
To: Randy.Steiner@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: null or value
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
Other related posts:
