REVERT_TO_V15_OPTIMIZER Option

Setting this option ON forces the query optimizer to mimic SAP Sybase IQ  15.x behavior.

Allowed Values

ON, OFF

Default

  • ON in all 16.0 databases upgraded from 15.x
  • OFF in all newly created 16.0 databases

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. If permitted, can be set for an arbitrary other user or role, or for all users via the role.  Takes effect immediately.

Remarks

SAP Sybase IQ 16.0 supports several new join and grouping algorithms that leverage Hash and Hash-Range partitioned tables, as well as a few other new algorithms.  By default, all of these new algorithms are considered by the optimizer and will be selected where valid and appropriate. Setting REVERT_TO_V15_OPTIMIZER='ON' disables all 16.0 changes to the optimizer cost models.  It also disables all of these new join and grouping algorithms, unless they are valid and are specifically requested via a positive value for either the AGGREGATION_PREFERENCE option, the JOIN_PREFERENCE option, or a join condition hint string.

The REVERT_TO_V15_OPTIMIZER option is normally used for internal testing and manually tuning queries. Only experienced DBAs should use it.

Note: An error will result if your query references an RLV-enabled table and REVERT_TO_V15_OPTIMIZER='ON'.