RE: Storing single numbers in the database

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 7 Jun 2007 07:23:14 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAFB23EA6@MSXVS04.trivadis.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 07 2007 - 00:23:14 CDT