12. Using the Query Optimizer : Specialized Statistics Processing : Display Optimizer Statistics : Floating Point Precision in Optimizer Statistics Display
 
Share this page                  
Floating Point Precision in Optimizer Statistics Display
You can specify the precision with which floating point numbers are displayed in the statistics by enabling the Set Precision Level to check box and entering a value in the corresponding edit control to determine the number of decimal digits in the text format of the floating point numbers.
For example, assume a table, t_float, is defined with a column named c_float of type float, and that the following statements are used to insert values (all of which are approximately 1.0):
insert into t_float values (0.99999998);
insert into t_float values (0.99999999);
insert into t_float values (1.0);
insert into t_float values (1.00000001);
insert into t_float values (1.00000002);
You can create statistics for this table using the optimization procedure described in Database Statistics.
With its default floating point precision, the standard output is show seven places after the decimal point. For greater precision, you can enable the Set Precision Level check box and enter a larger value.
For example, specifying a precision level of 14 generates output similar to the following, in which there is sufficient precision to maintain a visible difference in the values:
*** statistics for database demodb version: 00850
*** table t_float rows:5 pages:3 overflow pages:0
*** column c_float of type float (length:8, scale:0, nullable)
date:2000_02_24 15:15:30 GMT unique values:5.000
repetition factor:1.000 unique flag:Y complete flag:0
domain:0 histogram cells:10 null count:0.00000000000000 value length:8
cell: 0 count:0.00000000000000 repf:0.00000000000000 value:0.99999997999999
cell: 1 count:0.20000000298023 repf:1.00000000000000 value:0.99999998000000
cell: 2 count:0.00000000000000 repf:0.00000000000000 value:0.99999998999999
cell: 3 count:0.20000000298023 repf:1.00000000000000 value:0.99999999000000
cell: 4 count:0.00000000000000 repf:0.00000000000000 value:0.99999999999999
cell: 5 count:0.20000000298023 repf:1.00000000000000 value:1.00000000000000
cell: 6 count:0.00000000000000 repf:0.00000000000000 value:1.00000000999999
cell: 7 count:0.20000000298023 repf:1.00000000000000 value:1.00000001000000
cell: 8 count:0.00000000000000 repf:0.00000000000000 value:1.00000001999999
cell: 9 count:0.20000000298023 repf:1.00000000000000 value:1.00000002000000
This can be useful when statistics are output to a text file or input from a text file. For more information, see Statistics in Text Files. When reading statistics from a text file, the optimization process assumes that all cell values are in ascending order. You can use the Set Precision Level option to preserve sufficient precision for floating point numbers.