Re: char(1) vs Number(1)
Date: Tue, 1 Dec 2009 16:48:30 -0800 (PST)
Message-ID: <16a9418a-d367-4d5a-868d-c4ed34fcca96_at_d10g2000yqh.googlegroups.com>
On Dec 1, 12:19 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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 faith-
> based 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 athttp://www.orafaq.com/faq/what_is_the_difference_between_varchar_varc...
> 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-s...- Hide quoted text -
>
> - Show quoted text -
No real disagreement with what you said. I took the mention of a million row test to indicate table storeage so a reference to OLAP seemed a little bit too far from the question to me. I could be wrong since that has been known to happen.
Maybe we should have mentioned using bitand on a number column so one column can hold multiple bit switches.
My preference is also to use varchar2 indicator columns to indicate conditions being true or not, that is, I also like Y | N values though I have used a value and NULL where only a small percentage of the column rows would have a value at any one time since this provides the maeans to create a sparse index to use to get those target rows.
Mark D Powell Received on Tue Dec 01 2009 - 18:48:30 CST