Help on number data type in Oracle 10G 1

[cmmrfrds]

I am working with a vendor product where they store numeric fields in various ways using the Number data type.

The variations.
Number
Number(38)
Number(12,2)
Number(18)
Integer

I built a summary table using data from various tables in the product and was not careful when I defined the Number fields in the new table. Here is my problem. The reveune fields in the product were defined as Number(12,2) and in my table they are just Number. When I display the results in the new table they look fine, but when I do a calculation they appear to give a floating number as opposed to looking like a currency amount with 2 digits precision. Now, I can make this work by rounding it to 2 digits precision, but I am thinking of going back and defining the fields differently in the table. That is, Number(12,2) instead of just Number. The question is can this be done with out unloading and reloading the table? If I need to reload the table it becomes a lot of work where being able to alter the data type would be relatively easier.

 

[SantaMufasa]

cmmrfrds,

If there are data already in the NUMBER column, then you must do some extra steps to reduce the precision of that column. I'll illustrate both the problems and the solution:

SQL> desc cmmr
 Name                    Null?    Type
 ----------------------- -------- ------
 X                                NUMBER

SQL> select * from cmmr

         X
----------
   45.6678

SQL> alter table cmmr modify x number(12,2);
alter table cmmr modify x number(12,2)
                        *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

SQL> alter table cmmr add y number(12,2);

Table altered.

SQL> update cmmr set y = x;

1 row updated.

SQL> select * from cmmr;

         X          Y
---------- ----------
   45.6678      45.67

SQL> alter table cmmr drop column x;

Table altered.

SQL> alter table cmmr rename column y to x;

Table altered.

SQL> select * from cmmr;

         X
----------
     45.67

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

Thank you. That is what I was afraid of.

How is the best way to do this?

Do I make a copy of the table.
Then, zero out the columns where the data precision must change.
Then, Alter the column to correct precision.
Then, update the copy with the data from the original table.
Then, rename the copy to the original name.
Does it complicate the process if the table is partitioned?

Or, is there a more simple way?

 

[SantaMufasa]

Cmmrfrds,

You can do all of the above tasks you listed above, but here are a couple of things to consider:[ul][li]There is never a need to "zero out" columns in Oracle, especially to create the numeric precision you want. In Oracle, you should not use "Zero" simply as a place holder for "no value". In Oracle, NULL represents "no value". We should use "Zero" (0) only to represent an actual numeric value halfway between -1 and +1: (i.e., "We sell the last 75 copies of 'Pirates of the Caribbean', thus our inventory for that item = '0'.")

Additionally, regardless of whether we enter a value of "0.00" or "0.0" or "0" in Oracle, Oracle actually stores the same internal value, which = "0".

By default, in Oracle, newly added columns are already NULL, therefore we need to do no initialising for NULLs.

[/li][li]If you have massive amounts of data in other columns of the table, then creating a second table represents (a possibly significant amount of) "extra" work and, at least temporarily, unnecessary consumption of tablespace.

So, if you have typical amounts of Oracle's natural "free space/growth space" factors in your table, then using a temporary "extra column" to hold the values that you are trying to expand (especially for the relatively small consumption for numeric values), you are WAAAAY better off by using the "in-place" method of ALTERing a column's precision.

[/li][li]Perhaps the most significant downside of using a separate table to make this change is that you lose all of your indexes on the original table when you delete it and rename the new table. You must then go through the hassle of re-creating all of the indexes that drop when you drop the original table.

[/li][li]Depending upon your paritioning scheme, in-place ALTERation of your table for this purpose should be more efficient than the "COPY table - Modify new table's column - DROP original table - Rename new table" methodology.[/li][/ul]Let us know any additional questions or thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]