Re: Use of nulls in single character flag fields

In article <329037DF.56B9_at_tcdi.ie> ncronin <ncronin_at_tcdi.ie> writes:
>From: ncronin <ncronin_at_tcdi.ie>
>Subject: Use of nulls in single character flag fields
>Date: Mon, 18 Nov 1996 10:18:07 +0000
 

>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)

I explicitly make my flag fields not null, and I prefer them because it gives you more clarity about the data. As far as the performance is concerned queries using indexed field are more efficient if the indexed result set is small, relative to the whole data. In flag fields, e.g: the value could be either 'T' or 'F', 'T'=> for true and 'F'=> False, if for example half the rows have 'T' and half the rows have 'F' then using the index wouldn't be that helpful. But depending on the sort of data an index could be beneficial. More over if you use a function on an indexed field then that indexed won't be used any way, so using NVL is going to cost a bit.

Hope this helps

Saqib Zufiqar
Software Engineer
CresSoft Products Received on Tue Nov 19 1996 - 00:00:00 CET