Query plans
The optimizer uses the cost and selectivity information to help determine the best query plan for a query.
In particular, the optimizer uses this information to
obtain the following query and cost estimates:
- Number of rows to retrieve from a table
This estimated number of rows is based on the selectivity of each filter within the WHERE clause of the query.
- Amount of resources that the query requires
The cost is an estimate of the total cost of resource usage for executing the query filter.
The following user-defined functions are Boolean expressions:
- Built-in operator functions:
- relational-operator functions, such as lessthan()
- Boolean built-in operator functions, such as like() and matches()
- End-user functions that return a BOOLEAN value
Because these user-defined functions are Boolean expressions,
they can act as filters in queries. You can optimize these Boolean-expression
functions as follows.
| Type of Optimization | Description |
|---|---|
| Negator function | Calculate the NOT condition of the Boolean expression |
| Selectivity and cost functions | Provide an estimate of the number of rows that the filter will return |
Both the cost and selectivity of a UDR can dramatically affect the performance of a particular query plan. For example, in a join between tables, it is often advantageous to have the tables with the most selective filters as the outer tables to reduce the number of rows that flow through the intermediate parts of the query plan.