Materialized Views

From: 1e4.3c46d53a.18467 <1e4.3c46d53a.18467_at_temp>
Date: 31 Jan 2002 14:26:11 -0500
Message-ID: <3c599a53.f5.18467@IBIXWEBF>

Hopefully I'm doing something wrong, because so far Materialized Views are useless. Here's my Create:

create materialized view
FACT_DIM04_LVL02
enable query rewrite
as
select
d04.DKEY
, d04.LVL02_DESCR
, f.KEY01
, f.KEY03
, f.YEAR_MONTH
, f.SCENARIO
, sum(f.AMOUNT) "AMOUNT"

from FACT1 f
, DIM04 d04

where (f.KEY04         = d04.KEY04)
  and (f.ATTR01        = 'O')
  and (d04.LVL01_DESCR = '9999')
  and (f.SCENARIO IN ('ACT', 'PLAN'))

group by
d04.DKEY
, d04.LVL02_DESCR
, f.KEY01
, f.KEY03
, f.YEAR_MONTH
, f.SCENARIO

This works. I now do

analyze table FACT_DIM04_LVL02 compute statistics ;

So far so good. Now let's do an EXPLAIN using the exact sames DDL used to create the MV:

explain plan

   set STATEMENT_ID = 'FACT04'
   for
select
  d04.DKEY
, d04.LVL02_DESCR
, f.KEY01
, f.KEY03
, f.YEAR_MONTH
, f.SCENARIO
, sum(f.AMOUNT) "AMOUNT"

from FACT1 f
, DIM04 d04

where (f.KEY04         = d04.KEY04)
  and (f.ATTR01        = 'O')
  and (d04.LVL01_DESCR = '9999')
  and (f.SCENARIO IN ('ACT', 'PLAN'))

group by
d04.DKEY
, d04.LVL02_DESCR
, f.KEY01
, f.KEY03
, f.YEAR_MONTH
, f.SCENARIO

This gives:

Query Plan

SELECT STATEMENT Cost = 602
TABLE ACCESS FULL FACT_DIM04_LVL02 Great. However, now let's add one tiny little additional where clause to the query:

explain plan

   set STATEMENT_ID = 'FACT04'
   for
select /*+ REWRITE */
  d04.DKEY
, d04.LVL02_DESCR
, f.KEY01
, f.KEY03
, f.YEAR_MONTH
, f.SCENARIO
, sum(f.AMOUNT) "AMOUNT"

from FACT1 f
, DIM04 d04

where (f.KEY04         = d04.KEY04)
  and (f.ATTR01        = 'O')
  and (d04.LVL01_DESCR = '9999')
  and (f.SCENARIO IN ('ACT', 'PLAN'))
  and (1 = 1)

group by
d04.DKEY
, d04.LVL02_DESCR
, f.KEY01
, f.KEY03
, f.YEAR_MONTH
, f.SCENARIO

Notice that I added the simplest predicate possible (1 = 1), *AND* I've added a hint telling the optimizer to use the new MV. However, Oracle does not recognize the MV, instead it goes back to the base tables:

Query Plan

SELECT STATEMENT Cost = 4130
SORT GROUP BY
HASH JOIN

      TABLE ACCESS BY INDEX ROWID DIM04
        INDEX RANGE SCAN DIM04_LVL02
      TABLE ACCESS FULL FACT_DIM04_LVL02

In other words, the MV only seems to work if you're using the *EXACT* query that was used to create the MV, which makes it almost useless.

Yes, I'm using CBO and have set QUERY REWRITE to true.

Any ideas out there? TIA. Received on Thu Jan 31 2002 - 13:26:11 CST