1) for additional
restrictions related to fast refreshes of complex aggregates.
NOTE
You can specify an order by clause in the create materialized view
command. The order by clause will only affect the initial creation of
the materialized view; it will not affect any refreshes.
Using Materialized Views to Alter Query Execution Paths
For a large database, a materialized view may offer several performance benefits. You can use
materialized views to influence the optimizer to change the execution paths for queries. This
feature, called query rewrite, enables the optimizer to use a materialized view in place of the
table queried by the materialized view, even if the materialized view is not named in the query.
For example, if you have a large SALES table, you may create a materialized view that sums the
SALES data by region. If a user queries the SALES table for the sum of the SALES data for a region,
Oracle can redirect that query to use your materialized view in place of the SALES table. As a
result, you can reduce the number of accesses against your largest tables, thus improving the
system performance. Further, because the data in the materialized view is already grouped by
region, summarization does not have to be performed at the time the query is issued.
NOTE
You must specify enable query rewrite in the materialized view
definition for the view to be used as part of a query rewrite operation.
To use the query rewrite capability effectively, you should create a dimension that defines the
hierarchies within the table??™s data.
Pages:
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926