Reason to use DECIMAL(31,0)

Question

What is the reason that some people from Oracle background are using DECIMAL(31,0) for integers. In MySQL it is not efficient.

Answer 1

Oracle implements the "INTEGER" ANSI datatype as a synonym for NUMBER(38)

For more details see "Learn Oracle: Datatypes for SQL and PL/SQL, Numbers"

However, the following table should be used as a mapping between datatype in Oracle and MySQL:

"Oracle® Database SQL Developer Supplementary Information for MySQL Migrations"

Oracle and MySQL Compared > Data Types > Comparing Data Types

The main difference, as explained here and here is that Oracle NUMBER datatype is variable-length format while MySQL DECIMAL(n) datatype used to be represented as strings that require one byte per digit or sign character (before version 5.0.3)

(Oracle NUMBER Datatype) Internal Numeric Format

Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle Database does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a given value, can be calculated using the following formula:

ROUND((length(p)+s)/2))+1

where s equals zero if the number is positive, and s equals 1 if the number is negative.

Zero and positive and negative infinity (only generated on import from Oracle Database, Version 5) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.

and

(MySQL) DECIMAL Data Type Changes

The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and any remaining digits left over require some fraction of four bytes. [...]

For example, a DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require four bytes. A DECIMAL(20,6) column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and three bytes for the remaining five digits. The six fractional digits require three bytes.