Re: Size of NUMBER field

Oracle uses variable length numbers so they will take up as much space as they need.

Only specify the length for logical reasons. i.e. Only specify lengths where there is a specific business need to enforce a field size. Usually this is not the case. From a programming point of view any restriction can lead to a potential problem because it is only a matter of time before the restriction needs to be relieved.

Beware of setting your own limits thinking they a business limits. For instance, common sense may lead you to set a maximum size for a house number of 4 digits. But why, does it matter if you set no limit. This type of validation is usually done at the front end before it gets to the database anyway.

Constraints are a better way to limit size.

Doug Cowles wrote:

> I read somewhere that a NUMBER with unspecified precision
> takes up 22 bytes, but upon testing it with a table, I find this isn't
> so.
> If I enter a 1 digit number, the row becomes 6 bytes long.
> If I enter a 5 digit number, the row becomes 8 bytes long.
> If I enter an 8 digit number, the row becomes 9 bytes long.
> If I enter a 12 digit number, the row becomes 11 bytes long -
> Etc.,
> I got these by analyzing the table and select avg_row_len on a table
> with only
> 1 row, and one column, a NUMBER with no precision.
>
> A physical model was just reviewed and the suggestion was made to change
>
> any unspecified NUMBER fields to have length and/or precision.
> I guess I wondering though that if NUMBER doesn't hog 22 bytes
> automatically,
> and there are no conflicts with the application, it seems it's not
> important
> as I thought it was. Any thoughts?
>
> - Dc.
Received on Wed Feb 10 1999 - 03:17:06 CST