I have a 10 billion records table so I need to improve space allocation. My table has only fields of type floating point (single-precision). What is the best choice,
BINARY_FLOAT or something else ?
It depends on your data (and this assumes that you don't care about the different behaviors of the two data types). You can put together a test with sample data that is similar to what you're actually storing to find out what approach will use less space.
I'll create a table
If I populate it with 100,000 rows of data in increments of 0.001
then I can see the minimum, maximum, and average size required to store the values
If you run that, you'll see that the number column is averaging 3.89 bytes/ row (with a min of 2 and a max of 4) while every
But is that the whole story? Nope. If we use different sample data, we get very different results. Let's run the same test but divide everything by 3
Now, if you run the same
Depending on your data, you may find that one or the other approach uses more space. Of course, where there are substantial differences in space utilization, there is the potential for more substantial rounding issues when you move to floating point numbers.
Note as well that the results may be version-dependent. I did my test on an 11.2 database where the documentation indicates that
You can need space used on