Re: HOW TO MAKE ORACLE USE MATERIALISED VIEW IN STAR SCHEMA

Michiel wrote:

> Hi there,
> We created a data warehouse with a large fact table and dimension tables.
> To increase performance we built a materialised view that aggregates order
> data.
> I expect Oracle to use the aggregate when all requested columns of the fact
> table in the query are available in the aggregate. Unfortunately,
> Oracle only seems to use the aggregate when no dimension columns are requested.
>
> The base fact table is WTF_ORDERREGEL
> This is the create statement:
>
> The state of the view seems OK:
>
> MVIEW_NAME REWRITE_ENAB REWRITE_CAP STALENESS COMPILE_ST
> ----------------------- ------------ ----------- --------- ----------
> WTA_ORDER_PERIODE_MAAND_TST Y GENERAL FRESH VALID
>
> Optimizer mode is ALL_ROWS
>
>
>
> I would be very pleased to get some help.
> Thanks in advance,
> Michiel Brunt
>

init.ora parameter

query_rewrite = TRUE Received on Mon Mar 18 2002 - 22:40:02 CST