What is the difference between precision and scale?

oracle types

What is the difference between precision and scale in Oracle? In tutorials they usually leave scale empty and set precision to 6 when creating a primary key.

What do precision and scale stand for?

koljaTM

Precision 4, scale 2: 99.99

Precision 10, scale 0: 9999999999

Precision 8, scale 3: 99999.999

Precision 5, scale -3: 99999000

can you please explain the behavior of negative scales?

looks like rounds/ignores that many integer values left of decimal

keep in mind that precision is always includes the scale portion. e.g: Precision 4, scale 2 - will fail any number > 99.9999..; try: select cast (99.99999 as NUMBER(4,2)) from dual; //OK; select cast (100.9 as NUMBER(4,2)) from dual; //FAIL;

@JamaDjafarov 99.99999 fails as can be seen below: ` 21:53:54 CB900@XYZ > select cast (99.99999 as NUMBER(4,2)) from dual; select cast (99.99999 as NUMBER(4,2)) from dual * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column 21:52:32 CB900@ASCEND1 > select version from v$instance; VERSION --------------------------------------------------- 12.1.0.2.0 `

@Phalgun select cast(99.9999 as NUMBER(4,2)) from DUAL; is a bad example because the truncation will round the number up from 99 to 100, which is then too large for a NUMBER(4,2). Try select cast(88.8888 as NUMBER(4,2)) from DUAL; instead to see the response is 88.89.

Ayman

Precision is the total number of digits, can be between 1 and 38. Scale is the number of digits after the decimal point, may also be set as negative for rounding.

Example: NUMBER(7,5): 12.12345 NUMBER(5,0): 12345

More details on the ORACLE website:
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

And Scale is the number of digits to the right (positive) or left (negative) of the decimal point, not just after the decimal point.

Look at koljaTM's example. Precision is how many significant digits, which may have a value (i.e., not just "0" as place holders). Scale indicates how those fall with respect to the decimal point. Scale can be negative, indicating what precision ABOVE 0 you DON'T care about. NUMBER(1,-4): allows only 10 values: 00000, 10000, 20000...90000

Precision is NOT JUST the total number of digits. As David Aldridge explained in his comment to manojlds it is the mantissa - how many significant digits you care about. Scale thought of as the exponent is a more accurate, albeit esoteric, explanation. Any negative scale will have no digits after the decimal point, and will have that many 0s as place holders to the left of the decimal point. NUMBER(1,-4) will have 5 digits, but only the first, in the 10,000s place will have a value you care about.

manojlds

Precision is the number of significant digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

Scale is the number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from -84 to 127.

In your case, ID with precision 6 means it won't accept a number with 7 or more significant digits.

Reference:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

That page also has some examples that will make you understand precision and scale.

that mean the last bumber will be 1000000?

+1: I think the key to understanding this is to understand the internal number format -- mantissa and exponent. Precision places a limit on the possible length of the mantissa, and scale places a limit on the possible minimum of exponent.

@DavidAldridge I echo you. I think you should consider posting it as an answer regarding the mantissa and exponent. A number is actually stored in a variable length format.

What about precision for CHAR datatype?

Community

Maybe more clear:

Note that precision is the total number of digits, scale included

NUMBER(Precision,Scale) Precision 8, scale 3 : 87654.321 Precision 5, scale 3 : 54.321 Precision 5, scale 1 : 5432.1 Precision 5, scale 0 : 54321 Precision 5, scale -1: 54320 Precision 5, scale -3: 54000

ylerjen

Scale is the number of digit after the decimal point (or colon depending your locale)

Precision is the total number of significant digits

https://i.stack.imgur.com/9kRBv.png

Bablu Gope

precision: Its the total number of digits before or after the radix point. EX: 123.456 here precision is 6.

Scale: Its the total number of digits after the radix point. EX: 123.456 here Scaleis 3

yogi

If value is 9999.988 and Precision 4, scale 2 then it means 9999(it represents precision).99(scale is 2 so .988 is rounded to .99)

If value is 9999.9887 and precision is 4, scale is 2 then it means 9999.99

No, precision is the number of significant digits to store. In both your cases the precision stored is 6 and the scale is 2.

What is the difference between precision and scale?

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US