Creating Precomputed Result Sets

To use precomputed result sets for your queries, issue the set materialized_view_optimization command for the session.

The syntax is:
 create {precomputed result set | materialized view} 
	prs_name [(alternative_column_name 
	[ [constraint constraint_name] 
		unique (column_name,...)]

	[{immediate | manual} refresh]
	[{populate | nopopulate}]
	[enable | disable]
	[{enable | disable} use in optimization]
	[lock { datarows | datapages | allpages}]
	[on segment_name]
	[partition_clause]

as query_expression  
You may specify the following in precomputed result sets:
  • Partitions
  • Segments
  • Indexes (functional indexes not allowed)
  • Unique keys (you must include the unique key constraint when you create precomputed result sets for immediate refresh)

If you drop the base table, the precomputed result set is changed to disabled.See Reference Manual: Commands.