RE: null or value

If you are generally only interested in the active records (which are the vast majority of the records) then an index would not help you (a full table scan is in that case a better access path). As it is only a 1 character column the additional space usage by such a column is also negligible, so space usage is also not a reason for making one of the values null.

If you do have some queries which need only the records that are not active, then a normal index on the indicator column would help (but make sure that the query does not use a bind variable for the predicate on the indicator column and that you have a histogram for that column). Setting the indicator column to null for the active records in combination with a normal index would also speed up those queries and result in a smaller index. However, it makes it less intuitive for someone to write a query against that table. As an alternative, you could just use the Y and N values and create a function based index, which translates the Y values to NULL and leaves the N value intact.

regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
e-mail: freek.dhooge_at_uptime.be
tel. +32 (0)3 451 23 82
http://www.uptime.be
disclaimer

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Steiner, Randy [Randy.Steiner_at_nyct.com] Sent: 16 September 2009 20:43
To: oracle-l_at_freelists.org
Subject: null or value

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

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