Compile-Time Changes for Some set Parameters

In version 15.0.2 and later, the SAP ASE server changes the compile-time behavior for some abstract plan set parameters when you use them to create stored procedures or run them in Transact-SQL batches.

In earlier versions of SAP ASE, the set parameters took effect after the stored procedure was executed or recompiled. SAP ASE 15.0.2 allows you to use optimizer set parameters at compile time to affect the optimizer in stored procedures or batches.

Note: This changed behavior may effect the composition of the result set. You should review the result set created by the 15.0.2 versions of the set parameters before using them in your production systems.

You must reset the set parameter before returning from the stored procedure or the execution of subsequent stored procedures may be affected. If you intend to propogate this change to subsequent stored procedures, use export_options parameter.

The optimizer options that you can export when you use set export_options on are:

  • addend_union_all

  • auto_query_tuning

  • basic_optimization

  • bushy_space_search

  • distinct_hashing

  • distinct_sorted

  • distinct_sorting

  • group_hashing

  • group_inserting

  • group_sorted

  • hash_join

  • hash_union_distinct

  • index_intersection

  • index_union

  • merge_join

  • merge_union_all

  • merge_union_distinct

  • multi_gt_store_index

  • nary_nl_join

  • nl_join

  • opportunistic_distinct

  • opportunistic_grouping

  • optgoal

  • opttimeout

  • order_sorting

  • parallel_query

  • query_tuning_mem_limit

  • query_tuning_time_limit

  • replicated_partitioning

  • showabstractplan

  • showbestplan

  • showcodegen

  • showcounters

  • showelimination

  • showexecio

  • showfinalplan

  • showhistograms

  • showliocosting

  • showlogprops

  • showlop

  • showmanagers

  • shownostats

  • showparallel

  • showpiocosting

  • showpllcosting

  • showsearchengine

  • store_index

  • streaming_sort

Parent topic: set