Re: Implementation of boolean types.

From: Matthias Hoys <idmwarpzone_NOSPAM__at_yahoo.com>
Date: Fri, 15 Jul 2005 13:46:58 +0200
Message-ID: <42d7a233$0$323$ba620e4c@news.skynet.be>

"Holger Baer" <holger.baer_at_science-computing.de> wrote in message news:db85sc$ahd$1_at_news.BelWue.DE...

> Matthias Hoys wrote:

>> "Eagle Fan" <eagle.f_at_gmail.com> wrote in message
>> news:1121421448.738223.140180_at_f14g2000cwb.googlegroups.com...
>>
>>>Nod. Seems char(1) takes less space than number;
>>>SQL> select dump(x) from tt1;
>>>
>>>DUMP(X)
>>>--------------------------------------------------------------------------------
>>>Typ=2 Len=2: 193,2
>>>
>>>SQL> desc tt1
>>>Name Null? Type
>>>----------------------------------------- --------
>>>----------------------------
>>>X NUMBER
>>>
>>>SQL> select dump(x) from tt2
>>> 2 ;
>>>
>>>DUMP(X)
>>>--------------------------------------------------------------------------------
>>>Typ=96 Len=1: 49
>>>
>>>SQL> desc tt2
>>>Name Null? Type
>>>----------------------------------------- --------
>>>----------------------------
>>>X CHAR(1)
>>>
>>>But how about the speed when do equal search, order by or other
>>>operations?
>>>
>>
>>
>> Well it can only be '1' or '0'. An index + up-to-date statistics
>> (possibly with histograms if distribution is skewed) should make queries
>> ultra-fast, independent of the data type.

>
> A common misconception. Since it can only be 0 or 1, given a equal 
> distribution
> it's very unlikely to use an index. If it's highly skewed, a FBI might 
> help if
> using a function that returns NULL for the value you're not interested in. 
> Since
> NULLs are not indexed, this might lead to a very small index.
>
> Cheers,
> Holger

I agree that if the data is evenly distributed, no index will (should) be used. But with data skewing, a normal B-Tree index (+ histograms) could be useful, right ?
And would there be any difference in performance between a NUMBER(1) and CHAR(1) datatype ? Because this was the original question. Received on Fri Jul 15 2005 - 06:46:58 CDT