Re: Help - clueless Oracle newbie in distress..

In article <46b9dbb9.0111131416.5887ab2f_at_posting.google.com>, sorby_at_my-deja.com says...
>
>Hi,
>
>I'm investigating the side-effects of changing a numeric field
>(integer 4) to alpha-numeric length 5 on Oracle (don't know what
>version, but it's on Unix. I'm just a dumb programmer who never gets
>to see his code running over Oracle).
>
>It's a primary key field so I expect the corresponding index(es) to
>grow. I assume just 2 bytes are currently being used to store the
>numeric field but the new alpha field will require 5 bytes?
>

it is not 2 bytes -- Oracle stores numbers in a platform independent format. Numbers (ints are numbers in Oracle -- all numeric fields are "numbers") are stored in a non-fixed width field that will consume a different number of bytes depending on the data that is in there.

For example:

scott_at_ORA817DEV.US.ORACLE.COM> desc t

 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            NUMBER(38)
 Y                                            VARCHAR2(4)

scott_at_ORA817DEV.US.ORACLE.COM> select x, y, vsize(x), vsize(y), vsize(x)-vsize(y)
  2 from t;

         X Y VSIZE(X) VSIZE(Y) VSIZE(X)-VSIZE(Y)

---------- ---- ---------- ---------- -----------------
         0 0             1          1                 0
         9 9             2          1                 1
        99 99            2          2                 0
       999 999           3          3                 0
      9999 9999          3          4                -1

So, for a 4 digit number, most of the time the NUMBER and the VARCHAR2(4) take the same space. Sometimes the varchar2 is "smaller" and sometime the number is "smaller".

The next effect will be -- they are virtually the same *in this case*.

>When we re-org the database can I expect the contents of the numeric
>field to vapourise or am I expecting too much for the contents to be
>converted to alpha-numeric automatically?
>

not sure exactly what you mean there?

>Thanks in anticipation!
>
>--
>Sorby
>"Never knowingly understood"
>http://www.wedgie.freeserve.co.uk

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp