I am using Oracle Database 12c Enterprise Edition Release 184.108.40.206.0 - 64bit Production and I am trying to decide on using
NUMBER for a whole number field that will hold a
LONG from Java.
Long.BYTES = 8 Long.SIZE = 64 Long.MAX_VALUE = 9223372036854775807 / 2^63-1
I was lead to a relatively old blog post from an answer on Stack Overflow that suggests that you should never really use
INTEGER because it is has some signification performance issues compared to using
NUMBER. Even for just ID columns.
INTEGER is always slower then NUMBER. Since integer is a number with added constraint. It takes additional CPU cycles to enforce the constraint. I never watched any difference, but there might be a difference when we load several millions of records on the INTEGER column. If we need to ensure that the input is whole numbers, then INTEGER is best option to go. Otherwise, we can stick with NUMBER data type.
This article also states that "INTEGER is equivalent to NUMBER(38,0)" which I already knew. What I have not been able to determine if it is equivalent with the possible performance concerns that are raised.
- I do want to ensure that all the values are nothing but whole numbers.
- I will be working with millions of rows on a regular basis as well as joining on this column because it will be a foreign key to hundreds of tables.
The Oracle documentation says that
SMALLINT all map to
So I am planning on using
NUMBER(38,0) like I always have, lots of legacy databases and tables I am importing data from actually store this
ID information in a
VARCHAR(50) and the data is tremendously horrid trash because of people adding prefix/suffix characters to indicate state; like
z343234 means disabled. So I am trying to clean this data up in the new systems and not allow the perversions that the previous developers/business analyst made. I am not concerned with saving the corrupt flags and other data, just the numeric part.
I work with so many different database engines I can not keep up with what the latest idioms are.