Re: Implementation of boolean types.

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 15 Jul 2005 13:15:23 +0200
Message-ID: <db85sc$ahd$1@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 Received on Fri Jul 15 2005 - 06:15:23 CDT