QUERY_REWRITE_ENABLED

Property	Description
Parameter type	String
Syntax	QUERY_REWRITE_ENABLED = { false | true | force }
Default value	If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then true If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0 or lower, then false
Modifiable	ALTER SESSION, ALTER SYSTEM
Real Application Clusters	Multiple instances can have different values.

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database.

Values:

· false

Oracle does not use rewrite.

· true

Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.

· force

Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is important.

To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

Note:

1. This parameter can be altered at session level. It needs to be set to TRUE to enable the use of functional indexes and for stored outlines.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘QUERY_REWRITE_ENABLED’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE		ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
query_rewrite_enabled	TRUE	FALSE	TRUE	IMMEDIATE	TRUE		FALSE	allow rewrite of queries using materialized views if enabled

Oracle initializatoin parameters