Designing for speed: BINARY_FLOAT vs. NUMBER vs. VARCHAR
raindogFeb 16 2012 — edited Feb 17 2012We have a database that consists almost exclusively of integer numbers. All (except identity columns) have values less than 1,000 and all are positive.
All day long, people number crunch with queries and never change the data. The output is often floating point, but we don't need more than 2 or 3 decimal places of accuracy - a lot of it is average, quartiles, correlation, etc. - basic stats.
I am not worried at all about INSERT/UPDATE speed - the data set is only modified once a week on the weekend and could run 48 hours for all we care (it takes less than 30 minutes at present).
So what are the best data types to use for the columns?
In general:
(1) for integers, is BINARY_FLOAT or NUMBER(3) faster? I've read that BINARY_FLOAT is faster than NUMBER because the CPU can directly operate on it. The loss of precision is not significant in our case.
(2) our identity columns (user IDs) are 10-digit numbers - the numeric value is just used for PK/FK relationships. Is BINARY_FLOAT, NUMBER(10), or VARCHAR2(10) faster?
(3) We also have a bunch of 2-digit number lookup columns for descriptions, strings, etc. BINARY_FLOAT, NUMBER(2), or VARCHAR(2)?
I'm assuming that "storage smaller in the DB" would in the main equate to faster, because more of the DB would be cached in memory.
Thank, brain trust!