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 kinds of 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.

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.