Tips for creating materialized views
- Most frequently used columns
If you have a few sets of columns that you use frequently in the queries (found by analysis), then create materialized views with those columns.
- Most restrictive column
If there is a column that participates in a filter clause and usually filters out most of the table (for example, temporal columns), then use this column for the ORDER BY clause in the materialized view creation.
- Materialized view index
Create thin materialized views (with as few columns as possible) that contain the most restrictive columns (for example, temporal columns) for use as indexes.
- Fewer materialized views
For the best performance, create as few materialized views per each table as possible. Each materialized view that you create for a base table causes the system to analyze the performance of using each view over the base table, which can add time to the query performance.