Therefore, a database or server
failure during this procedure may cause the local materialized views to be out of sync with each
other. In this case, simply rerun this procedure after the database has been recovered. As an
alternative, you can create refresh groups, as described in the next section.
Using the SQL Access Advisor
As of Oracle Database 10g, you can use the SQL Access Advisor to generate recommendations
for the creation and indexing of materialized views. The SQL Access Advisor may recommend
specific indexes (and types of indexes) to improve the performance of joins and other queries. The
SQL Access Advisor may also generate recommendations for altering a materialized view so that
it supports query rewrite or fast refreshes. You can execute the SQL Access Advisor from within
Oracle Enterprise Manager or via executions of the DBMS_ADVISOR package.
NOTE
For best results from the DBMS_ADVISOR package, you should
gather statistics about all tables, indexes, and join columns prior
to generating recommendations.
To use the SQL Access Advisor, either from Oracle Enterprise Manager or via DBMS_
ADVISOR, perform the following steps:
1. Create a task.
2. Define the workload.
3. Generate recommendations.
4. View and implement recommendations.
You can create a task in one of two ways: by executing the DBMS_ADVISOR.CREATE_TASK
procedure or by using the DBMS_ADVISOR.QUICK_TUNE procedure (as shown in the next
section).
Pages:
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909