HOW TO MAKE ORACLE USE MATERIALISED VIEW IN STAR SCHEMA

From: Michiel <mbrunt_at_inergy.nl>
Date: 18 Mar 2002 13:12:38 -0800
Message-ID: <fa261386.0203181312.48aacf1d@posting.google.com>

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:

CREATE MATERIALIZED VIEW WTA_ORDER_PERIODE_MAAND_TST TABLESPACE SYSTEM
PARALLEL
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
SELECT

		  AANNEEMPERIODE_KEY             ,
		  AANNEEMMAAND_ISO_KEY           ,
		  AANBIEDER_SERVICEVERANTW_KEY   ,
		  AANBIEDER_VERKOOPVERANTW_KEY   ,
		  AANBIEDER_CONTRACTVERANTW_KEY  ,
		  AANBIEDLOCATIE_PROCES_KEY      ,
		  AANPAKPERIODE_KEY              ,
		  AANPAKMAAND_ISO_KEY            ,
		  AANPAKLOCATIE_PROCES_KEY       ,
		  BOEKPERIODE_KEY                ,
		  FRANKEERWIJZE_KEY              ,
		  KLANT_AANBIEDER_KEY            ,
		  MARKTREGIME_KEY                ,
		  NOC_CORRECTIECODE_KEY          ,
		  ORDERSTATUS_KEY                ,
		  ORDERSOORT_KEY                 ,
		  PRODUCTAFSPRAAK_KEY            ,
		  PRODUCTAFSPR_BEOORDELING_KEY   ,
		  PRODUCT_PRIJS_KEY              ,
		  PRODUCT_AANGEBODEN_KEY         ,
		  REGISTRATIEPERIODE_KEY         ,
		  REGISTRATIEMAAND_ISO_KEY       ,
		  BTW_PERCENTAGE                 ,
		  SUM(BDR_Z_COUL) AS BDR_Z_COUL  ,
		  SUM(BEDRAG) AS BEDRAG          ,                         
		  SUM(BTW_BEDRAG) AS BTW_BEDRAG  ,                     
		  SUM(AANTAL_PRIJSINFO) AS AANTAL_PRIJSINFO,
		  SUM(AANTAL_METING) AS AANTAL_METING,                  
		  SUM(GEWICHT_KG_PRIJSINFO) AS GEWICHT_KG_PRIJSINFO,           
		  SUM(GEWICHT_KG_METING) AS GEWICHT_KG_METING,
		  SUM(KIX_KORTING) AS KIX_KORTING,
		  SUM(V48_KORTING) AS V48_KORTING,
		  SUM(MACHINE_KORTING) AS MACHINE_KORTING,                
		  SUM(KWANTUM_KORTING) AS KWANTUM_KORTING,
		  SUM(TIJDPLAATS_KORTING) AS TIJDPLAATS_KORTING , 
		  COUNT(*) AS CNT_STER  ,
		  COUNT(BDR_Z_COUL) AS CNT_BDR_Z_COUL  ,
		  COUNT(BEDRAG) AS CNT_BEDRAG          ,                         
		  COUNT(BTW_BEDRAG) AS CNT_BTW_BEDRAG  ,                     
		  COUNT(AANTAL_PRIJSINFO) AS CNT_AANTAL_PRIJSINFO,
		  COUNT(AANTAL_METING) AS CNT_AANTAL_METING,                  
		  COUNT(GEWICHT_KG_PRIJSINFO) AS CNT_GEWICHT_KG_PRIJSINFO,           
		  COUNT(GEWICHT_KG_METING) AS CNT_GEWICHT_KG_METING,
		  COUNT(KIX_KORTING) AS CNT_KIX_KORTING,
		  COUNT(V48_KORTING) AS CNT_V48_KORTING,
		  COUNT(MACHINE_KORTING) AS CNT_MACHINE_KORTING,                
		  COUNT(KWANTUM_KORTING) AS CNT_KWANTUM_KORTING,
		  COUNT(TIJDPLAATS_KORTING) AS CNT_TIJDPLAATS_KORTING  
FROM  WTF_ORDERREGEL
GROUP BY AANNEEMPERIODE_KEY             ,
		  AANNEEMMAAND_ISO_KEY           ,
		  AANBIEDER_SERVICEVERANTW_KEY   ,
		  AANBIEDER_VERKOOPVERANTW_KEY   ,
		  AANBIEDER_CONTRACTVERANTW_KEY  ,
		  AANBIEDLOCATIE_PROCES_KEY      ,
		  AANPAKPERIODE_KEY              ,
		  AANPAKMAAND_ISO_KEY            ,
		  AANPAKLOCATIE_PROCES_KEY       ,
		  BOEKPERIODE_KEY                ,
		  FRANKEERWIJZE_KEY              ,
		  KLANT_AANBIEDER_KEY            ,
		  MARKTREGIME_KEY                ,
		  NOC_CORRECTIECODE_KEY          ,
		  ORDERSTATUS_KEY                ,
		  ORDERSOORT_KEY                 ,
		  PRODUCTAFSPRAAK_KEY            ,
		  PRODUCTAFSPR_BEOORDELING_KEY   ,
		  PRODUCT_PRIJS_KEY              ,
		  PRODUCT_AANGEBODEN_KEY         ,
		  REGISTRATIEPERIODE_KEY         ,
		  REGISTRATIEMAAND_ISO_KEY       ,
		  BTW_PERCENTAGE                 

;

Query:
SELECT

A_WTD_KLANT.RELATIENAAM,
A_WTD_KLANT.KLANTNAAM,
sum(A_WTF_ORDERREGEL.AANTAL_METING)

FROM
WTF_ORDERREGEL A_WTF_ORDERREGEL,
WTD_KLANT A_WTD_KLANT
WHERE
A_WTF_ORDERREGEL.KLANT_AANBIEDER_KEY=A_WTD_KLANT.KLANT_KEY GROUP BY
A_WTD_KLANT.RELATIENAAM,
A_WTD_KLANT.KLANTNAAM
.

The plan shows that the BASE table is read, instead of the aggregate.

Execution Plan

---

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=609768 Card=102367 30 Bytes=532309960)
1 0 SORT (GROUP BY) (Cost=609768 Card=10236730 Bytes=532309960 )
2 1 MERGE JOIN (Cost=141726 Card=10236730 Bytes=532309960) 3 2 SORT (JOIN) (Cost=17210 Card=453069 Bytes=20388105) 4 3 TABLE ACCESS (FULL) OF 'WTD_KLANT' (Cost=3914 Card=4 53069 Bytes=20388105)
5 2 SORT (JOIN) (Cost=124516 Card=10236730 Bytes=71657110) 6 5 TABLE ACCESS (FULL) OF 'WTF_ORDERREGEL' (Cost=17514 Card=10236730 Bytes=71657110)

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 Received on Mon Mar 18 2002 - 15:12:38 CST