Re: Use of nulls in single character flag fields
Date: 1996/11/22
Message-ID: <32963ca7.1008840_at_dcsun4>#1/1
On Tue, 19 Nov 1996 08:11:40 -0500, Allen Kirby <akirby_at_att.com> wrote:
>ncronin wrote:
>>
>> Hi
>> We have some conflicting desgin views at our site. Some
>> people believe use of nulls for flags is fine as you can
>> use NVL(...) to convert when retrieving. I believe flags
>> should be non-null for clarity, better performance when
>> indexed and you don't have to remember to use NVL all the
>> time. Also I'm not sure if there is a performance penalty
>> from using NVL(...). Any views on this simple matter from
>> the database designers of you ??
>> Noel (ncronin_at_tcd.ie)
>
>Will it work? Of course it will. But I agree with you. Why complicate
>the system just to be cute? What's wrong with a field with values of
>'Y' or 'N'? Why would you force all your developers and report writers
>to remember to use NVL if you don't have to? Not to mention the
>possible performance/indexing problems.
>
In my opinion, a field with 2 or 3 values wouldn't be conventionally indexed anyway (so indexing shouldn't really be part of the question) but....
In at least one case though, you can definitely use NULLS in flags to increase performance and decrease storage. For example, I load my web server log files into the database. A flag has two values (T/F Y/N etc) and is not really a candiate for indexing when you have millions of records. When loading my log records, I want to be able to pull up log records that represent errors (eg: the HTTP status code is 5xx). Since very few of my records are errors, the ratio of TRUE to FALSE is very small (few TRUE records representing an error, many FALSE records representing success). I want to use an index to quickly access error records. I will always full scan for success records (since few error records, use an index, since many millions of success records, full scan).
Since Oracle does not index null values, I set the error_flag field to 'T' for errors and leave NULL for success. I have a very small index that points to all of the error records and has no overhead for the millions of success records. To find error records I "select * from log where error_flag='T'" which will use an index. To find success records I use 'select * from log where error_flag is null' which can't use an index (since nulls aren't there).
So, if you have a widely unbalanced 'flag' field, using NULL for the 'popular' value and some value for the 'unpopular' value can be very advantageous...... Especially if you want to pull up the 'unpopular' records very fast.
my 2cents.
>Sounds like someone is trying to design in some job security doing
>maintenance to me.
>--
>---
>Allen Kirby AT&T ITS Production Services
>akirby_at_att.com Alpharetta, GA.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Nov 22 1996 - 00:00:00 CET