The underlying storage for a materialized view is a table structure. You can partition materialized views like you can partition tables. When the database rewrites a query to run against materialized views, the query can take advantage of the same performance features from which queries running against tables directly benefit. The rewritten query may eliminate materialized view partitions. If joins back to tables or with other materialized views are necessary to retrieve the query result, then the rewritten query can take advantage of partition-wise joins.
Example 6-1 shows how to create a compressed partitioned materialized view that aggregates sales results to country level. This materialized view benefits from queries that summarize sales numbers by country level or higher to subregion or region level.
Example 6-1 Creating a compressed partitioned materialized view
CREATE MATERIALIZED VIEW country_sales PARTITION BY HASH (country_id) PARTITIONS 16 COMPRESS FOR OLTP PARALLEL NOLOGGING ENABLE QUERY REWRITE AS SELECT co.country_id , co.country_name , co.country_subregion , co.country_region , sum(sa.quantity_sold) country_quantity_sold , sum(sa.amount_sold) country_amount_sold FROM sales sa , customers cu , countries co WHERE sa.cust_id = cu.cust_id AND cu.country_id = co.country_id GROUP BY co.country_id , co.country_name , co.country_subregion , co.country_region;