Create Materialized Views

After you populate the query workload table, use the appropriate tool for the Oracle Database version to create materialized views.

The SQL Access Advisor generates recommendations on improving the performance of the specified fact tables. The SQL Access Advisor can also help determine appropriate indexing schemes. The SQL Access Advisor displays the SQL code it uses to create the appropriate materialized views. Before enabling SQL Access Advisor to create the materialized views, review the following:

• The creation of a materialized view can fail if the SQL code includes a CAST statement.

• The CREATE MATERIALIZED VIEW statement mustn't specify the same query that you provided as a workload table. If the statement does specify the same query, then the materialized views can't reflect the true performance gain. However, if the query is run frequently, then creating a materialized view might still be worthwhile.

• Add a forward slash (/) to the end of the CREATE MATERIALIZED VIEW statement after the SQL statement. If the forward slash isn't included, the SQL*Plus worksheet doesn't recognize it as a valid statement.