Re: char(1) vs Number(1)

From: joel garry <joel-garry_at_home.com>
Date: Tue, 1 Dec 2009 09:19:15 -0800 (PST)
Message-ID: <87bef069-d857-4d5c-b702-7efda34a6cf4_at_a39g2000pre.googlegroups.com>

On Dec 1, 6:12 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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.

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 http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types 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.

jg

--
_at_home.com is bogus.
Insufficient tax revenue?  Blame the intertubes.
http://www.signonsandiego.com/news/2009/dec/01/online-tax-advantage-shortchanges-state/

Received on Tue Dec 01 2009 - 11:19:15 CST