RE: Storing single numbers in the database
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <sbecker6925@xxxxxxxxx>
- Date: Thu, 7 Jun 2007 07:23:14 +0200
Hi Sandy
> Does it really matter if it's stored as NUMBER(1) or CHAR(1)?
> What are the ramifications, if any, of defining the column as
> CHAR(1)?
You already got plenty of opinions... From my part I'm inclined to use
NUMBER(1) in such a situation. But at the end, it's matter of opinion
;-)
What I would like to add to the discussion is something about
performance...
If you choose a NUMBER(1) to store a kind of boolean value that you know
will change, I suggest using 1 and 2 instead of 0 and 1. In fact,
internally, while a 0 is stored in 1 byte, 1 and 2 are stored in 2
bytes. This can simply be seen with the following query. I.e. in some
cases an update from 0 to 1 could cause row migration.
SQL> SELECT dump(0,16), dump(1,16) FROM dual;
DUMP(0,16) DUMP(1,16)
--------------- -----------------
Typ=2 Len=1: 80 Typ=2 Len=2: c1,2
If you choose a CHAR(1) than go for a VARCHAR2(1). For some unknown
reasons (at least to me), VARCHAR2 are a bit faster than CHAR. Here an
example based on two tables storing exactly the same data:
SQL> desc t_char
Name Null? Type
----------------------- -------- ----------------
C1 CHAR(1)
C2 CHAR(1)
C3 CHAR(1)
C4 CHAR(1)
C5 CHAR(1)
C6 CHAR(1)
C7 CHAR(1)
C8 CHAR(1)
C9 CHAR(1)
C10 CHAR(1)
SQL> desc t_varchar
Name Null? Type
----------------------- -------- ----------------
C1 VARCHAR2(1)
C2 VARCHAR2(1)
C3 VARCHAR2(1)
C4 VARCHAR2(1)
C5 VARCHAR2(1)
C6 VARCHAR2(1)
C7 VARCHAR2(1)
C8 VARCHAR2(1)
C9 VARCHAR2(1)
C10 VARCHAR2(1)
SQL> select blocks, num_rows
2 from user_tables
3 where table_name in ('T_VARCHAR','T_CHAR');
BLOCKS NUM_ROWS
---------- ----------
35 10388
35 10388
SQL> declare
2 l_count pls_integer;
3 begin
4 for i in 1..1000 loop
5 select count(*) into l_count
6 from t_varchar
7 where c1 = 'A' or c2 = 'B' or c3 = 'C' or c4 = 'D' or c5 = 'E'
8 or c6 = 'F' or c7 = 'G' or c8 = 'H' or c9 = 'I' or c10 = 'J';
9 end loop;
10 end;
11 /
Elapsed: 00:00:04.76
SQL> declare
2 l_count pls_integer;
3 begin
4 for i in 1..1000 loop
5 select count(*) into l_count
6 from t_char
7 where c1 = 'A' or c2 = 'B' or c3 = 'C' or c4 = 'D' or c5 = 'E'
8 or c6 = 'F' or c7 = 'G' or c8 = 'H' or c9 = 'I' or c10 = 'J';
9 end loop;
10 end;
11 /
Elapsed: 00:00:05.78
HTH
Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
