Tuesday, February 16, 2010

Support and Historical Notes for OPTIMIZER_SECURE_VIEW_MERGING

Note that the documentation for this parameter is incorrect in a number of the manuals.
In 10g / 11g the parameter can be TRUE or FALSE with the following behavior:


FALSE
This is similar to behavior in earlier releases such as 9.2.
Oracle allows views and PLSQL functions in a query to be merged / moved around during query optimization in order to try and get the lowest cost execution plan even if the views / functions have different owners.

TRUE
This is the default value.
Oracle does not use view merging or predicate move-around/pushing in some cases where the query has user-defined functions and views (regular, inline, unnested,etc) if the views / functions are owned by someone other than the person running the query.

If OPTIMIZER_SECURE_VIEW_MERGING = FALSE then individual users can be given permission to merge other users views etc.. by granting them the MERGE ANY VIEW privilege.

The default value of this parameter is NOT affected by the setting of OPTIMIZER_FEATURES_ENABLE.