char(1) vs Number(1)
CrazyKarma
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?
joel garry
Yes, not having a boolean datatype is a bit of a frustration in
Oracle. See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595
Oh wait, everything I know is wrong: http://download.oracle.com/docs/html/A95298_01/define3.htm
jg
--
@home.com is bogus.
http://en.wikipedia.org/wiki/Everything_You_Know_Is_Wrong
Noons
char(1) will be more efficient spacewise, but be absolutely sure you
won't use it as "0"/"1".
Use instead "N"/"Y".
The last thing you want is Oracle doing implicit conversions of a char
(1) to number when comparing it to "1". In simple terms, don't use
this: WHERE BOOL_COL = 1.
Use instead WHERE BOOL_COL = 'Y'.
The reason is very simple: the first form will cause an implicit CAST
of BOOL_COL to NUMBER data type to compare against 1.
That will cause any indexes including BOOL_COL in its native format to
not be used. So: either use the second form of the WHERE or make sure
any indexes using the column will be FBI.
Mark D Powell
> On Nov 30, 9:59 am, CrazyKarma <ska...@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
>
> Oh wait, everything I know is wrong:http://download.oracle.com/docs/html/A95298_01/define3.htm
>
> jg
> --
> @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 --
joel garry
> On Nov 30, 1:58 pm, joel garry <joel-ga...@home.com> wrote:
>
>
>
> > On Nov 30, 9:59 am, CrazyKarma <ska...@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
> > --
> > @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 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
--
@home.com is bogus.
Insufficient tax revenue? Blame the intertubes.
http://www.signonsandiego.com/news/2009/dec/01/online-tax-advantage-shortchanges-state/
CrazyKarma
Thanks for the feedback. The objective of these columns is to do
some set operations ( & - or). The issue that I am not too keen is
decoding (1 = Y & 0 = F) statement when loading couple of millions
rows each day. I have 5 of these columns whose values come as bit
values. totally agree on the readability aspect.
I agree with Noons. Usage is exactly the intended way.
CK
Mark D Powell
> 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
> --
> @home.com is bogus.
>
> - 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
CrazyKarma
"Maybe we should have mentioned using bitand on a number column so one
column can hold multiple bit switches. "
is there a function to convert that back to decimal? ex: 011001 = 48.
now I need to convert 48 back to 011001. is that doable in oracle?
Thanks
CK
CrazyKarma
BINARY..is there a function like that in Oracle?
joel garry
> In other words in pure plain simple SQL function to convert DECIMAL to
> BINARY..is there a function like that in Oracle?
http://oraqa.com/2008/02/29/how-to-convert-binaryoctalhex-to-decimal-base-ten-number-in-sql/
http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkytehexdec.html
Google is my friend!
jg
--
@home.com is bogus.
1901: US Supreme Court rules ex-governor of California owns Mexican
land grant.
1903: US government force-marches Cupeños Indians off their land.
2009: Indians use profits from casino to buy it back.
http://www.signonsandiego.com/news/2009/dec/02/pala-indian-band-buy-warner-springs-ranch/
CrazyKarma
representing the binary in one column and then BITAND to identify
which bit was set. This will help me decouple table schema to new
additions of lookup types. I guess Mark was alluding to this earlier.
Thanks all
CK