Oracle Data Types – 5 FAQs About Number
I get a lot of email, and comments on blog entries, asking questions about many different topics. I am going to try to answer some of the more common questions as FAQs. Today I am starting with some NUMBER FAQs. I am specifically talking about Oracle and I am specifically talking about the NUMBER data type, not numerics in general. I’ve written about NUMBERs before.
Q: What is the difference between an INTEGER and a NUMBER.
A: INTEGER is a subtype of NUMBER. INTEGER is declared as NUMBER(38,0). That means it is a “constrained” NUMBER. At its most basic level, there is no difference between a NUMBER and an INTEGER. It is, however, constrained to 38 digits. Since 38 digits is the largest a NUMBER can be, it is not much of a constraint precision-wise. The difference comes from the scale being 0. If you store 3.5 in an INTEGER, Oracle will automatically ROUND() it to 4. With INTEGER, you will always be assured of a whole number.
Q: Is integer faster than number?
A: No. If anything an INTEGER will be slower than a NUMBER. Because an INTEGER is a NUMBER with an added constraint, it will take additional CPU cycles to enforce the constraint. In reality, I have never measured any difference in speed and I think it would take a HUGE (as in billions) number of operations to ever be noticeable.
NOTE: If you need to ensure whole numbers, an INTEGER (or other constrained NUMBER precision) is the better way to go. Enforce data constraints in the database not in your application.
Q: Does an INTEGER use less space than a NUMBER? Does NUMBER(3,0) use less space than an INTEGER?
A: No. A NUMBER, regardless of constraint, will always store data using the same amount of space for a given number. Let’s take NUMBER(5,0) as a test case. From a simple perspective (there is a way to calculate storage but I am just speaking generally here), If you store 1 you will store less data than if you store 99999. The same is true if you are using NUMBER(). The only difference is that you can never store greater than 99999 when a NUMBER is constrained to NUMBER(5,0).
Q: Should I store floating point numbers in a NUMBER or in a FLOAT?
A: Float is actually a SUBTYPE of NUMBER, specifically an unconstrained NUMBER. REAL is a subtype of FLOAT. Oracle also provides high performance floating point number data types in BINARY_DOUBLE and BINARY_FLOAT. With the BINARY_* data types, you may lose precision during operations on the values.
If the precision and scale limits of a NUMBER meet your needs, I would say to use a NUMBER, unless you absolutely need the performance. In my experiments, the performance gains were not dramatic. I would think you would need to perform many millions, or even billions, of operations for the results to be noticeable.
Q: When should I use NUMBER and when should I use VARCHAR2?
A: If you need to perform mathematical operations on a value, use NUMBER. If you are not doing math (or if the values are not used as mathematical values), store the value in a VARCHAR2.
Example: You have a data warehouse and you store an account number and a sale price. Sale price may be added or multiplied so it is NUMBER. Account number may be all numeric digits but it makes no sense to perform mathematical operations against it. Store account number as VARCHAR2.
Even if your application will not perform math against the sale price, store it as NUMBER because it makes sense to perform math against it. This is pretty much a common sense thing to me.
There is always an exception. Surrogate keys (as in sequences) – store them as a NUMBER. While you won’t be adding and subtracting them, there are benefits to storing them a a NUMBER. For one thing, you will not have to convert from numeric (result of a sequence) to string to store them.
Hope this helps,
You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.