Product Documentation

SQL Operations Guide

Previous Topic

Next Topic

Optimizer Configuration Options

The FairCom DB SQL query optimizer plays an important role in reducing the execution time of FairCom DB SQL queries. The role of the optimizer is to minimize the number of read requests that are made for the data and index files.

This minimization process is computed by the optimizer based on the cardinality (the number of records selected), selectivity (the fraction of the records selected based on an operator), and a set of index key values. For many operations on records or indexes, a cost, in terms of time units, is used to decide between the use of a record or index-oriented scan.

Users may wish to disable the cardinality, selectivity and/or costing features to force particular optimizations of FairCom DB SQL. Having these features enabled or disabled will induce the optimizer to perform queries using different strategies to select the resulting dataset.

Three FairCom DB SQL configuration file keywords were added to control the optimizer's usage of cardinality, selectivity and costing of record and index operations: NO_CARDINALITY, NO_SELECTIVITY and NO_COSTS. These keywords must be used with the SQL_OPTION configuration command.

  • NO_CARDINALITY - disables the cardinality logic that informs the optimizer about the number of rows in a data or index file.
  • NO_SELECTIVITY - disables the selectivity logic that informs the optimizer about the percentage of rows returned when applying given search criteria.
  • NO_COSTS - disables the logic that returns to the optimizer the cost in units of time for given record and index operations. The optimizer will use this information to decide whether to use a record or an index scan.

Note: Costs are not implemented in the current version of FairCom DB SQL. Behavior may change in future releases.

To disable cardinality, selectivity, and costing logic, add the following keywords to the FairCom DB SQL configuration file:

Example

SQL_OPTION NO_CARDINALITY

SQL_OPTION NO_SELECTIVITY

SQL_OPTION NO_COSTS

TOCIndex