| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Storing single numbers in the database
This was too long to come through yesterday. Given the comments on the
thread I'm thinking it is still relevant, presuming you avoid implicit
conversions in your code. This is *not* to be taken as advice regarding the
char versus varchar2 bit of the thread, I just used the types asked about by
the OP. With a nod to Mark Bobak for taking the time to run a test, the
relative sorting time will be small in absolute difference and the small
difference will be dominated by internal storage size. There will be a small
range of number of rows where the size difference may push numeric into
non-memory sorting while character/string storage will still be in memory,
so there is potentially a huge win for character in the event you are
chronically at that number of rows being sorted. Far more important is the
the true purpose of the column and whether it fits the "nulls drop out"
usage.
I wonder if the developer is talking about a character interface and whether
the real concern is about doing the type conversion in his code to ensure he
avoids an implicit type conversion talking to Oracle. Extra lines of code in
applications have their own weight in memory size, execution cost,
readability and maintenance; from what we know in this case (barring the
huge potential advantage on index lookups if the "nulls drop out" usage is
germaine) there is no way to know whether economy in the application code
dominates. Finally, notice the glaring omission of testing multibyte
character sets in my results below. (Tildes are nulls.)
mwf
From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Tuesday, June 05, 2007 4:57 PM
Since Oracle doesn't really have bit flags, char(1) is equally small as a
number for 0 and 1. Hmm, well let's check:
SQL> create table test63 (cval char(1), nval number);
insert into test63 values (null,null);
insert into test63 values ('1',1);
insert into test63 values ('0',0);
SQL> break on report
SQL> compute sum of vsize(cval) on report
SQL> compute sum of vsize(nval) on report
SQL> column cval format a4
SQL> select a.*,vsize(cval),vsize(nval) from test63 a
CVAL NVAL VSIZE(CVAL) VSIZE(NVAL)
---- ---------- ----------- -----------
~ ~ ~ ~
1 1 1 2
0 0 1 1
----------- -----------
sum 2 3
Well - I'd say vsize of a null should be zero (given that the definition is the internal storage size, which paradoxically is in fact knowable for a NULL), but that aside it seems that character '1' only takes up one byte while numeric 1 takes up two bytes. (That might be a release specific bug on the test database I have laying around.) So if in fact the real purpose of the column is to store only 0 and 1, character is in fact smaller. If you have just those two values, then you don't have problems such as '9' > '10' (which even Oracle blew in several releases of OEM so you couldn't install on 10g because '10' < '9' was true in an XML evaluation you couldn't change.) Now usually bit flags like this have some logical purpose. If this is in fact a new feature, the developer might actually do well to use NULL and 0 (not worrying about whether it is char or number, since the 0 is special cased as a single byte in numbers. HMM - if you're using some UTF multi-byte set, the numbers are smaller. Using NULL and 0 in this fashion to represent "Handled" and "Not Handled" - if that is the purpose of the bit flag - then allows quick lookup of the rows that "need work" since the handled rows disappear from the index.
So I've waxed on a long time about this innocous question. I hope (aside from a rehash of the disappearing index refererence of NULL and storage size issues) this raises the question for you: What is this column for? The whole answer is more than "The column is for storing either '0' or '1'" in every case I have ever seen. So as the DBA, you'll need to get a little more information about the purpose of the column to give the best answer about how it should be stored.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 06 2007 - 08:48:18 CDT
 |
 |