Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, September 24, 2007

OPT_PARAM hint in 10gr2

Metalink Note 377333.1 describes opt_param hint which was introduced in 10gR2 but remains undocumented in 10gr2 documentation. This hint behaves the same way as setting a parameter (e.g, using alter session) except that the effect is for the statement only.

Thankfully it is documented in 11g documentation here:

The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED. For example, the following hint sets the parameter STAR_TRANSFORMATION_ENABLED to TRUE for the statement to which it is added:

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

Parameter values that are strings are enclosed in single quotation marks. Numeric parameter values are specified without quotation marks.

No comments: