Alter table modify column vs. multiply values query

My application interacts with an Oracle database in which a table has 1 million records. The problem is:

In that table we have a column for numeric integer types.We want to store float values (2 digits).We could do that by multiplying with 100 (from the application) and store them in that column or modify the column data type.The question is :

What takes less time? The alter table modify column or the query (update table set column = 100*val).

Solution

A NUMBER in Oracle has no intrinsic "format", i.e. its internal representation will not depend upon its precision or scale. The NUMBER 123456 for example will be represented internally the same way if it is declared as a NUMBER(9,2) or INTEGER:

SQL> SELECT dump(CAST(123456 AS INTEGER)) int,
  2         dump(CAST(123456 AS NUMBER(9,2))) num
  3    FROM dual;

INT                       NUM
------------------------- --------------------
Typ=2 Len=4: 195,13,35,57 Typ=2 Len=4: 195,13,35,57

The only difference in a column between an INTEGER(7) and a NUMBER(9,2) is that the INTEGER(7) has a stronger check constraint. Both columns will represent the data internally in the exact same way.

This is why you can increase the precision of your column of a non-empty column without problem, and the ALTER TABLE will only modify the metadata (dictionary tables) and thus should be instant with very little redo.

SQL> create table test (id number(7));

Table created.

SQL> insert into test values (123456);

1 row created.

SQL> alter table test modify (id number(9,2));

Table altered.

SQL> select * from test;

        ID
----------
    123456