ODXSQL - Commands - SET Options

OPTIMIZATION

OPTIMIZATION options

The Optimization option sets the selection optimization for oaselect and oaexecsql. The optimization settings define which methods OmniAccess may use to optimize a query. Optimization settings can be set through the options parameter of oaselect, oaexecsql, or oaopencusor, the WITH clause of an SQL statement, or an SQL Set statement. Multiple optimization settings can be defined.

Setting Optimization does not guarantee that the optimization will be performed. It only instructs OmniAccess that this method may be used if appropriate.

• Use REQUIRED_OPTIMIZATION to guarantee a specific optimization setting.
• Use the Explain Plan to assist in determining which settings can and will be used in a specific query, or why something does not work.

OPTIMIZATION
Required.

options
Required. A comma separated list of optimization options.

Optimization Settings

MDKQUAL -- Use MDK indexes, if possible, to qualify rows and provide counts.

NO_MDKQUAL -- Do not use MDK indexes to qualify rows. Other allowed optimizations may still be used.

ASKQUAL -- Use ASK indexes, if possible, to qualify rows and provide counts.

NO_ASKQUAL -- Do not use ASK indexes to qualify rows. Other allowed optimizations may still be used.

ASKORDERBY -- Use ASK indexes to satisfy an ORDER BY clause, including using bitmap intersections based on MDK qualifications.

NO_ASKORDERBY -- Do not use ASK indexes to satisfy an ORDER BY clause, including using bitmap intersections based on MDK qualifications.

ASKRETRIEVAL -- Use ASK indexes to return detail data, including using bitmap intersections based on MDK qualifications. Note: Data returned from ASK indexes that are not installed with the NT (No Translate) option, may be upshifted.

NO_ASKRETRIEVAL -- Do not use ASK indexes to return detail data, including using bitmap intersections based on MDK qualifications. This is the default.

AGGREGATION -- Use ASK indexes to satisfy aggregations, GROUP BY clauses and HAVING clauses, including using bitmap intersections based on MDK qualifications.

NO_AGGREGATION -- Do not use ASK indexes to satisfy aggregations, GROUP BY clauses and HAVING clauses, including using bitmap intersections based on MDK qualifications.

STARSCHEMA -- Use ASK indexes to satisfy aggregations in the fact table of a Star Schema, including handling GROUP BY clauses referencing dimension tables, and including using bitmap intersections based on MDK qualifications. Aggregation indexes must contain the foreign key to all dimension tables referenced in the GROUP BY clause.

NO_STARSCHEMA -- Do not use ASK indexes to satisfy aggregations in the fact table of a Star Schema, including handling GROUP BY clauses referencing dimension tables, and including using bitmap intersections based on MDK qualifications.

Example

>SET OPTIMIZATION ASKQUAL, ASKRETRIEVAL

Top