RE: null or value
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "Randy.Steiner@xxxxxxxx" <Randy.Steiner@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Sep 2009 09:07:23 +0200
Randy,
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@xxxxxxxxx
tel. +32 (0)3 451 23 82
http://www.uptime.be
disclaimer
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Steiner, Randy [Randy.Steiner@xxxxxxxx]
Sent: 16 September 2009 20:43
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
