Hi,
- storage: it is the same, oracle stores what is entered whatever the precision/scale is. '1' is stored as 2 bytes in all the ways.
- performance: the more oracle knows about your data, the more it can estimate the number of rows returned by a query. So if you have only (10,2) numbers, it is better to declare them as (10,2) rather than (20,2)
- flexibility: it is easy to change (10,2) to (20,2) It is harder to change (20,2) to (10,2) as it has to check that no numbers have less that 10 precision
So , as sid by sdstuber, you should set them depending on your business values.
Regards,
Franck.
Main Topics
Browse All Topics
by: sdstuberPosted on 2009-02-07 at 06:38:08ID: 23578386
NUMBER is pretty much NUMBER regardless.
Behind the scenes Oracle will allocate enough bytes to stores whatever precision and scale you specify so there's little to no wasted space.
So, given that. I would use whatever precision and scale define your business values. Think of them as constraints and metadata.
I like to leave everything "effectively" unlimited unless there is a limit. 38 is large as you can go, so that's as close to "unlimited" as you can get.
I would use number(38,0) for your counters unless you have a business need to cap them at 10 digits.
Your 20,2 monetary seems fine for dollars and cents, again I would put 38 as the max (the upper limit) unless you have a business constraint of 20
For the fractions, I would just use number, but again with the same constraints. If you have a business definition of 10 decimal digits then define it so.
If you have performance concerns you "might" be able to get a little better performance using the binary_float or binary_double but they don't have the range of the NUMBER type.