Factors That Affect the Query Plan

When the optimizer determines the query plan, it assigns a cost to each possible plan and then chooses the plan with the lowest cost. Some of the factors that the optimizer uses to determine the cost of each query plan are as follows:

• The number of I/O requests that are associated with each filesystem access
• The CPU work that is required to determine which rows meet the query predicate
• The resources that are required to sort or group the data
• The amount of memory available for the query (specified by the DS_TOTAL_MEMORY and DS_MAX_QUERIES parameters)

To calculate the cost of each possible query plan, the optimizer:

• Uses a set of statistics that describes the nature and physical characteristics of the table data and indexes
• Examines the query filters
• Examines the indexes that could be used in the plan
• Uses the cost of moving data to perform joins locally or remotely for distributed queries