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,

LewisC

Technorati : , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

6 Comments »

 
  • JT says:

    I don’t think I agree with this part in the last Q “If you are not doing math (or if the values are not used as mathematical values), store the value in a VARCHAR2.”

    If its a number, store it as a number. Period. Otherwise you get stuff like 1, 0001, 0000001, etc.

    Then you get developers asking why they’re getting a full table scan on select * from foo where trim(leading ’0′ from a) = 1

    Treating numbers as strings almost always leads to headaches later.

    My only gripe – Good info!

  • [...] Oracle Data Types – 5 FAQs About Number | Database Geek Blog [...]

  • Lewi says:

    JT,

    I completely disagree with that though. If you aren’t doing math, why are you trimming 0′s? The fact that the value has leading zeroes means that it is part of the data.

    Account numbers have leading zeroes. An account number with 3 leading zeroes is different that one with 2 leading zeroes. If it were stored as a number, they would appear to be the same.

    I have run into exactly that situation where the modeler decided to create account numbers as number because they were numeric. We had to retrofit varchar2 all over the place.

    I stand by that: if you aren’t doing math, don’t store it as a number.

    Thanks for the comment.

    LewisC

  • pras99 says:

    Hello, what is better for a primary key column, for example table departments with primary key dep_id? varchar or number????

    What you advice me??

    pd: I’m newbie in Oracle DB.

  • Ben says:

    If ’001′ and ’00001′ represent different accounts, in my opinion it should be called an “account code” or “account id” rather than “account number”, given that it’s not a number.

    Also, if you don’t want alphabetic characters to appear in your account numbers but you store as varchar, you really should be adding an additional check constraint, which is a pain if what you really want is a number.

    If I had account numbers that really were numeric, but I wanted to format with leading zeros in some contexts, I’d store them as number and do lpad(accountnumber, , ’0′), perhaps in a calculated column.

    Cheers,
    Ben

  • Ben says:

    p.s. I can’t post here from Firefox, it tells me I failed math :(

    p.p.s. In my previous post, the middle argument to the lpad should be the desired length (I formatted in angle brackets so it got swallowed).