Re: char(1) vs Number(1)

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 1 Dec 2009 06:12:23 -0800 (PST)
Message-ID: <c51f88c9-2d38-4d1a-af39-cb431d6828a1_at_l13g2000yqb.googlegroups.com>

On Nov 30, 1:58 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Nov 30, 9:59 am, CrazyKarma <ska..._at_gmail.com> wrote:
>
> > Quick check on whether char(1) is more efficient than number (1).
> > Basically storing a bit value of either 0 or 1. since this is a
> > numeric maybe its better to store this as number(1).
>
> > Did a simple test for 1million rows, the IO cost is the same but the
> > number of bytes is double with number(1).
> > This makes me think that char(1) is more efficient in storage.
>
> > any thoughts?
>
> Yes, not having a boolean datatype is a bit of a frustration in
> Oracle.  Seehttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6...
>
> Oh wait, everything I know is wrong:http://download.oracle.com/docs/html/A95298_01/define3.htm
>
> jg
> --
> _at_home.com is bogus.http://en.wikipedia.org/wiki/Everything_You_Know_Is_Wrong

I do not think OLAP data types which are not the same as database data types which in turn are not the same as pl/sql data types of the same name applies to the question at hand. Example maximum length of a database varchar2 is 4000 bytes while in pl/sql a varchar2 may be up to 32K in length.

I would suggest using varchar2 over character even for char(1) since when you include the internal null indicator/length bytes both a char (1) and a varchar2(1) take two bytes of space.

HTH -- Mark D Powell -- Received on Tue Dec 01 2009 - 08:12:23 CST