materialized views (snapshots)...

We are using Oracle Enterprise Edition 8.1.6 on Solaris and Win 2K. I have so far unsuccessfully tried to get Oracle to use a materialized view when answering a query that does not explicitly mention the view. Instead, the query processor always uses the base tables specified in the from clause.

The queries are relatively simple: they select sales data at the year level which is pre-computed in the MV but can also be obtained by selecting the sales data at the days level from the base tables.

I am using the "create materialized view" syntax to specify the view, the "create dimension" syntax to tell Oracle about the dimensions as well as the hierarchies within the dimensions. Finally, I am also enabling query rewrite (within the create materialized view as well as with the "alter session set query_rewrite_enabled = true" command).

Has anybody been able to the query rewrite feature to work? Any input or suggestions are greatly appreciated.

Joachim Received on Thu Jul 26 2001 - 17:06:29 CDT