Data Type for migration from Oracle to mongoDB

Working with Data
1

We are migrating the data from Oracle to mongoDB, tables have column with number data type e.g. number(19) what should be the data type mongoDB? int32, int64 or decmal128?

2

Welcome to the MongoDB Community @Saurabh_Shinge!

I believe the number(19) Oracle type is an 8 byte “big integer” which would be equivalent to an int64 in MongoDB.

For comparison, here are the sizes of numeric types from the BSON spec:

byte	1 byte (8-bits)
int32	4 bytes (32-bit signed integer, two's complement)
int64	8 bytes (64-bit signed integer, two's complement)
uint64	8 bytes (64-bit unsigned integer)
double	8 bytes (64-bit IEEE 754-2008 binary floating point)
decimal128	16 bytes (128-bit IEEE 754-2008 decimal floating point)

Regards,
Stennie

3

Thanks @Stennie_X.
So do you think using decimal128 instead of int64 will affect the database size and performance?

Regards,
Saurabh

4

Hi @Saurabh_Shinge,

Decimal128 is twice as big (16 bytes) as an int64, so your field values will be comparatively larger in an uncompressed format (i.e. in the WiredTiger cache). Performance depends on factors like the configuration, resources, and workload of your deployment. The best way to predict the outcome on your database size and performance would be for you to test in a representative environment.

I would choose a representation that is appropriate for the data you are storing. If you need accurate decimal floating-point representation or integers outside the range of an int64, Decimal128 would be appropriate. If you’re just migrating existing data, I think int64 would suffice.

I would try selecting some of your current maximum field values from the number(19) column and confirm they can be accurately represented in your target MongoDB field type.

Regards,
Stennie

1 Like