Optimizer Statistics

The optimizer uses two sets of statistics when determining a join strategy on the base tables and on non-base-table inputs such as other QueryDef objects.

For base-table inputs, the optimizer looks at:

• The number of records in the base table. This tells the optimizer how large the tables are, which affects which join strategy is used.
  
  
• The number of data pages in the base table. The more data pages that need to be read from disk, the more costly the query is.
  
  
• The location of the table. Is the table in a local ISAM format or is it located in an ODBC database? Each distinct ODBC server is given a number. This helps the optimizer and remote post-processor do their work.
  
  
• The indexes on the table. When looking at indexes, the optimizer is concerned with:
  • Selectivity. If a particular index returns only three matching values out of 100,000, it’s considered highly selective. A unique index is the most highly selective index available, because every value is distinct. It’s always best to use highly selective indexes.
    
    
  • Number of index pages. As with data pages, the more index pages that must be read from disk, the more costly the query is.
    
    
  • Whether Null values are allowed in the index. Null values in an index may rule out the use of an index-merge join.
    
    
  • Whether duplicates are allowed in the index. Duplicates in an index affect its selectivity.

For non-base-table inputs, the optimizer looks at:

• Record counts. As with base tables, the number of records in the input query affects which join strategy is chosen.
  
  
• Input costs. Because the input is another query, the cost of executing that query must be factored into the total cost.

  Note The record count and input costs are estimates.

• Location of inputs. Each distinct ODBC server is given a number. This helps the remote post-processor do its work.
  
  
• Record ordering. An ordered input may be a candidate for a lookup join.