2011-04-15

NUMBER(38) versus INTEGER

Someone ask me why his table compare application showed that a NUMBER(38) column was of a distinct datatype from INTEGER in Oracle.



Actually they are the same in the current Oracle DB versions but the INTEGER is specified as NUMBER (*,0).
I'm sure that the compare application uses the user_tab_columns to check it and the data_precision is null for the INTEGER type column.

COLUMN_NAME  DATA_TYPE  DATA_LENGTH  DATA_PRECISION  DATA_SCALE
----------------------------------------------------------------
NUMBER38       NUMBER       22           38              0
INT            NUMBER       22         (Null)            0

The following DML are equivalents and could be used to "alter the type to INTEGER" so the compare application would not see that as distinct again.

ALTER TABLE tab MODIFY(NUMBER38 INTEGER);
ALTER TABLE tab MODIFY(NUMBER38 NUMBER(*,0));

2 comments:

  1. Hi.

    I know you are discussing it in terms of table columns, but I thought it was worth pointing out that they are very different if you are discussing the difference between PL/SQL variables of that type. INTEGER types are incredibly slow compared to NUMBER types in PL/SQL.

    Bye the way, I think you forgot to do "SET TAB OFF" before running your query. The formatting of you SQL*Plus output is all messed up. :)

    Cheers

    Tim...

    ReplyDelete
  2. Hi Tim

    I fixed the output, thanks.

    Personally in PL/SQL I never noticed INTEGER slower than NUMBER because I normally use PLS_INTEGER for integer variables (and PLS_INTEGER is assured that increase performance).
    In 11g I would use SIMPLE_INTEGER (with native compilation) for even better performace when possible: it doesn't allow nulls and there is no bounds checking (2147483647 +1 = -2147483648).

    ReplyDelete

Os comentários são moderados.
The comments are moderated.