| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Materialized Views
> 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.
Sorry, should also have added that in DBA_MVIEWS, there's a
field called REWRITE_CAPABILITY, and this has a value of
TEXTMATCH:
SQL> list
1 select mview_name, rewrite_enabled, rewrite_capability
2* from dba_mviews
SQL> /
MVIEW_NAME R REWRITE_C ------------------------------ - --------- FACT_DIM04_LVL02 Y TEXTMATCH TEST01 Y GENERAL
Here's what the manual says about this:
TEXTMATCH: The defining query of the materialized view contained restrictions on the use of query rewrite.
Also, should it matter, we're on 8.17, Sun Solaris 2.7 Received on Thu Jan 31 2002 - 13:40:24 CST
 |
 |