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

A couple of things;

First - have you create a DIMENSION to
tell Oracle that when considering rewrite the key columns of the dimension tables
are equivalent to the name columns ?

Second - have you tried adjusting the
setting for

    query_rewrite_integrity ?

My strategy for sorting out the problem would be to test the easiest case first, viz:.

1. Create the view on a prebuilt table, with never refresh
2. set query_rewrite_integrity to trusted

then worry about getting it to work with refreshable views and enforced/stale

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Michiel wrote in message ...


>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:

>