Re: char(1) vs Number(1)

From: joel garry <>
Date: Tue, 1 Dec 2009 09:19:15 -0800 (PST)
Message-ID: <>

On Dec 1, 6:12 am, Mark D Powell <> wrote:
> On Nov 30, 1:58 pm, joel garry <> wrote:
> > On Nov 30, 9:59 am, CrazyKarma <> 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.  See
> > Oh wait, everything I know is wrong:
> > jg
> > --
> > is bogus.
> 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.

You are most likely correct, but the OP does not disambiguate. The garden path of "why would one want 0/1 storage in an app" leads to many strange places.

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

I agree, but in thinking about why I could only come up with faithbased  reasons within the constraint of the OP (that is, single character, two value), so I didn't say it. William Robertson's and vnomat's comments at are interesting.

Noon's points about indices and implicit conversions really emphasizes the correct answer of "what exactly do you intend to do with this?"

I still have problems believing integer comparisons aren't faster than character, regardless of the truth. But I've been convinced for decades that Y and N are better flags than 1 and 0 from an apps viewpoint. The Enterprise stuff I work on has global parameters for language-specific yes/no flags.


-- is bogus.
Insufficient tax revenue?  Blame the intertubes.
Received on Tue Dec 01 2009 - 11:19:15 CST

Original text of this message