Re: Performance difference between varchar2 and number

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 26 May 2001 20:00:05 -0400
Message-ID: <crg0htc0gion5amdmrp9189cnk77s0j26t@4ax.com>

A copy of this was sent to Paul <paulhjli_at_hotmail.com> (if that email address didn't require changing) On Fri, 25 May 2001 19:30:12 -0000, you wrote:

>Hi,
>When I create a table with column, such as SSN. I am not sure which I
>should use, varchar2 or number. I only foucs performance. Does anybody
>know what difference bewteen using varchar2 or number?
>
>Thanks
>
>Paul

a number(10) would consume less space then a varchar2(10).

  1 create table t as
  2 select cast( rownum+9999999 as number(10) ) x, cast( rownum+9999999 as

                                                          varchar2(10) ) y

  3 from all_objects
  4* where rownum < 100
ops$tkyte_at_ORA8I.WORLD> /

Table created.

ops$tkyte_at_ORA8I.WORLD> select min(vsize(x)), max(vsize(x)), min(vsize(y)), max(vsize(y)),
  2 avg(vsize(x)), avg(vsize(y))
  3 from t;

MIN(VSZE(X)) MAX(VSZE(X)) MIN(VSZE(Y)) MAX(VSZE(Y)) AVG(VSIZE(X)) AVG(VSIZE(Y)) ------------ ------------ ------------ ------------ ------------- -------------

           2 5 8 8 4.96969697 8

it would take less time to compare (join), less storage in an index and so on to use a number.

there would be a disadvantage to using a varchar2 over a number if the data is truly numeric

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat May 26 2001 - 19:00:05 CDT