The optimizer only uses fresh data from the materialized views and only uses
those relationships that are based on ENABLED and VALIDATED primary, unique, or foreign key
constraints. In TRUSTED mode, the optimizer trusts that the data in the materialized view is fresh
and the relationships declared in dimensions and constraints are correct. In STALE_TOLERATED
mode, the optimizer uses materialized views that are valid but contain stale data as well as those
that contain fresh data.
If you set QUERY_REWRITE_ENABLED to FORCE, the optimizer will rewrite queries to use
materialized views even when the estimated query cost of the original query is lower.
If query rewrite occurs, the explain plan for the query will list the materialized view as one of
the objects accessed, along with an operation listed as ???MAT_VIEW REWRITE ACCESS.??? You can
use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to see if rewrite is possible for a query and
which materialized views would be involved. If the query cannot be rewritten, the procedure will
document the reasons.
EXPLAIN_REWRITE takes three input parameters??”the query, a materialized view name, and a
statement identifier??”and can store its output in a table. Oracle provides the create table command
for the output table in a script named utlxrw.sql in the $ORACLE_HOME/rdbms/admin directory.
The utlxrw.sql script creates a table named REWRITE_TABLE.
You can query REWRITE_TABLE for the original cost, rewritten cost, and the optimizer??™s decision.
Pages:
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928