optimizer_switch Helper Functions

Syntax

optimizer_switch_on() optimizer_switch_off() optimizer_switch_choice("on" | "off")

Description

The above procedures can be used to check which optimizer_switch options are on or off. The result set is sorted according to optimizer_switch option name.

Example

SELECT @@optimizer_switch\G *************************** 1. row *************************** index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on, index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on, derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on, in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on, subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on, semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on, optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on, orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on, condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on, not_null_range_scan=off  call sys.optimizer_switch_on(); +---------------------------------+------+ | option | opt | +---------------------------------+------+ | condition_pushdown_for_derived | on | | condition_pushdown_for_subquery | on | | condition_pushdown_from_having | on | | derived_merge | on | | derived_with_keys | on | | exists_to_in | on | | extended_keys | on | | firstmatch | on | | index_condition_pushdown | on | | index_merge | on | | index_merge_intersection | on | | index_merge_sort_union | on | | index_merge_union | on | | in_to_exists | on | | join_cache_bka | on | | join_cache_hashed | on | | join_cache_incremental | on | | loosescan | on | | materialization | on | | optimize_join_buffer_size | on | | orderby_uses_equalities | on | | outer_join_with_cache | on | | partial_match_rowid_merge | on | | partial_match_table_scan | on | | rowid_filter | on | | semijoin | on | | semijoin_with_cache | on | | split_materialized | on | | subquery_cache | on | | table_elimination | on | +---------------------------------+------+   call sys.optimizer_switch_off(); +-------------------------------+------+ | option | opt | +-------------------------------+------+ | engine_condition_pushdown | off | | index_merge_sort_intersection | off | | mrr | off | | mrr_cost_based | off | | mrr_sort_keys | off | | not_null_range_scan | off | +-------------------------------+------+

Notes

sys.optimizer_switch_on() is a shortcut for sys.optimizer_switch_choice("on") .

sys.optimizer_switch_off() is a shortcut for sys.optimizer_switch_choice("off") .

See Also

• optimizer-switch Documentation for optimizer_switch

_{This page is licensed: CC BY-SA / Gnu FDL}