RE: null or value

From: Powell, Mark D <mark.powell_at_EDS.COM>
Date: Wed, 16 Sep 2009 15:04:42 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9045AFB68_at_usahm208.amer.corp.eds.com>



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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Toon Koppelaars

	Sent: Wednesday, September 16, 2009 2:56 PM
	To: Randy.Steiner_at_nyct.com
	Cc: oracle-l_at_freelists.org
	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_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
	
	


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 16 2009 - 14:04:42 CDT

Original text of this message