Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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, NUMBER or BINARY_FLOAT or something else ?

share|improve this question
1  
Do the numbers have to be stored precisely? I ask because BINARY_FLOAT cannot store all numbers precisely - see docs.oracle.com/cd/B12037_01/server.101/b10759/… –  Phil Apr 10 at 0:32
    
I don't care about absolute precision. –  João Paraná Apr 10 at 10:00

2 Answers 2

up vote 3 down vote accepted

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 num_test with two different columns, one declared as a number and one as a binary_float.

create table num_test (
  num_col number,
  flt_col binary_float
);

If I populate it with 100,000 rows of data in increments of 0.001

begin
  for i in 1 .. 100000
  loop
    insert into num_test( num_col, flt_col )
      values( i/1000, i/1000 );
  end loop;
end;

then I can see the minimum, maximum, and average size required to store the values

select min( vsize(num_col) ), max( vsize(num_col) ), avg( vsize(num_col) ),
       min( vsize(flt_col) ), max( vsize(flt_col) ), avg( vsize(flt_col) )
  from num_test;

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 binary_float required 4 bytes of storage. So there is a small savings to using number in this case.

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

begin
  for i in 1 .. 100000
  loop
    insert into num_test( num_col, flt_col )
      values( i/1000/3, i/1000/3 );
  end loop;
end;

Now, if you run the same vsize query, you'll see that the number column takes up to 21 bytes of storage and averages 7.69 bytes for the 200,000 rows. The flt_col is still using a fixed 4 bytes for every row.

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 binary_float always takes 4 bytes of storage. In 10.2, the documentation indicates that binary_float took 5 bytes due to the inclusion of an extra length indicator. It's not obvious to me why a 32-bit value would ever need a length byte so I'm hard-pressed to understand why an earlier version of Oracle might have required the fifth byte, but the documentation is consistent that it did.

share|improve this answer

You can need space used on INDEX too, so to calculate space used on TABLE and INDEX we can execute this statement below:

SELECT T, table_name, sum(bytes)  as sum_bytes FROM 
(
    SELECT 'TABLE' as T, segment_name table_name, owner, bytes 
    FROM dba_segments WHERE segment_type = 'TABLE' 
) 
WHERE owner in UPPER('&owner') 
GROUP BY T, table_name, owner
HAVING SUM(bytes) > 100000
UNION ALL
  SELECT T, table_name, sum(bytes)  as sum_bytes FROM 
(
  SELECT 'INDEX' as T, i.table_name, i.owner, s.bytes 
  FROM dba_indexes i, dba_segments s 
  WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type = 'INDEX' 
) 
WHERE owner in UPPER('&owner') 
GROUP BY T, table_name, owner
HAVING SUM(bytes) > 100000; 
share|improve this answer
    
Did you mean to add this answer to a different question? I'm not sure that I see the relationship to the question... –  Justin Cave Apr 10 at 17:44
    
Ok @justin-cave this was an outspread of my original question. My table have a index on number column –  João Paraná Apr 10 at 17:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.