User-supplied condition selectivity

The simplest form of condition hint is to supply a selectivity value that will be used instead of the value the optimizer would have computed.

Selectivity hints are supplied within the text of the query by wrapping the condition within parentheses. Then within the parentheses, after the condition, you add a comma and a numeric value to be used as the selectivity.

This selectivity value is expressed as a percentage of the table’s rows, which satisfy the condition. Possible numeric values for selectivity thus range from 100.0 to 0.0.

• The following query provides an estimate that one and one half percent of the ship_date values are earlier than 1994/06/30:

  SELECT  ShipDate
  FROM  SalesOrderItems
  WHERE ( ShipDate < '2001/06/30', 1.5 )
  ORDER BY ShipDate DESC
  

• The following query estimates that half a percent of the rows satisfy the condition:

  SELECT *
  FROM Customers c, SalesOrders o
  WHERE (o.SalesRepresentative > 1000.0, 0.5) 
    AND c.ID = o.customerID
  

Fractional percentages enable more precise user estimates to be specified and can be particularly important for large tables.

SQL Anywhere Studio supports user-supplied selectivity estimates.

Adaptive Server Enterprise does not support user-supplied selectivity estimates.