You can use optdiag to customize the server-wide default values for selectivities to match the data for specific columns in your application. The optimizer uses range and in-between selectivity values when the value of a search argument is not known when a query is optimized.
The server-wide defaults are:
Range selectivity – 0.33
In-between selectivity – 0.25
You can use optdiag to provide values to be used to optimize queries on a specific column. The following example shows how optdiag displays default values:
Statistics for column: "city" Last update of column statistics: Feb 4 1998 8:42PM Range cell density: 0x3f634d23b702f715 # Range cell density: 0.0023561189228464 Total density: 0x3f46fae98583763d # Total density: 0.0007012977830773 Range selectivity: default used (0.33) # Range selectivity: default used (0.33) In between selectivity: default used (0.25) # In between selectivity: default used (0.25)
To edit these values, replace the entire “default used (0.33)” or “default used (0.25)” string with a float value. The following example changes the range selectivity to .25 and the in-between selectivity to .05, using decimal values:
Range selectivity: 0.250000000 In between selectivity: 0.050000000