Here is an example of a materialized view that we are creating:

Code:
CREATE MATERIALIZED VIEW LOG ON iet$wa_application
	WITH PRIMARY KEY, ROWID
	INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW iemv$wa_application
   USING INDEX
   ENABLE QUERY REWRITE AS
	SELECT 
		application_id,
		revision,
		MAX(update_date_time)
	FROM iet$wa_application
	GROUP BY
		application_id,
		revision;
Also we have our databases with optimizer_mode set to choose, which I just found out requires us to maintain stats. Which if we changed it to either all_rows or first_rows stats might not be needed. I don't know why but I am continuing to look. As you can see we specify query rewrite in the MV as well as query rewrite is turned on in the database. We want to make sure that the MV's are always up to date so I'm not sure if the sql to create the MV's is correct.