Materialized View and Session Settings

[Sameer]

Hi,

I have problem with the materialized view. I have set QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY at instance level. My queries do not make use of materialized view unless AGAIN I set these parameters at SESSION level. I also have a simple time frame DIMENSION so that my other combination queries will make use of MV.
Why I have to set the parameters at SESSION level again when they are set at INSTANCE level?

SQL> CREATE MATERIALIZED VIEW TRANS_MV
  2  BUILD IMMEDIATE
  3  REFRESH ON DEMAND
  4  ENABLE QUERY REWRITE
  5  AS
  6  SELECT /*+ FACT(trans) */a.region_id, e.mmyyyy, SUM(amount) 
  7  FROM region a , ort b, branch c , trans d, time_hr e
  8  WHERE a.region_id = b.region_id
  9  AND b.plz = c.plz
 10  AND c.PLZ = d.branch_id
 11  AND d.DATUM = e.day
 12  GROUP BY a.region_id, e.mmyyyy;
     
Materialized view created.

Elapsed: 00:05:44.06
SQL> disc
SQL> scott/tiger
SQL> Connected.

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced


SQL> SELECT a.region_id, e.yyyy, SUM(amount)           
  2  FROM region a , ort b, branch c , trans d, time_hr e
  3  WHERE a.region_id = b.region_id                     
  4  AND b.plz = c.plz                                   
  5  And c.plz = d.branch_id                             
  6  AND d.datum = e.day                                 
  7  GROUP BY a.region_id, e.yyyy;                     

36 rows selected.

Elapsed: 00:00:05.32

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

Session altered.

Elapsed: 00:00:00.00
SQL> 

SQL> SELECT a.region_id, e.yyyy, SUM(amount)           
  2  FROM region a , ort b, branch c , trans d, time_hr e
  3  WHERE a.region_id = b.region_id                     
  4  AND b.plz = c.plz                                   
  5  AND c.PLZ = d.branch_id                             
  6  AND d.datum = e.day                                 
  7  GROUP BY a.region_id, e.yyyy;                     

36 rows selected.

Elapsed: 00:00:00.02

Thanks

Sameer