I got the following Materialized view with query rewrite enabled
(sorry statement is rather long)
/* Formatted on 10-27-2009 4:37:46 (QP5 v5.114.809.3010) */
CREATE MATERIALIZED VIEW mv2
ENABLE QUERY REWRITE
AS
SELECT eanl.ods_src_rowid
, eanl.ods_src_change_date
, eanl.ods_src_commit_date
, eanl.ods_insert_run_id
, eanl.ods_insert_date
, eanl.ods_last_update_run_id
, eanl.ods_last_update_date
, eanl.ods_delete_run_id
, eanl.ods_delete_date
, eanl.mandt
, eanl.anlage
, eanl.sparte
, eanl.vstelle
, eanl.ablsperr
, eanl.bapertyp
, eanl.anschrei
, eanl.spebene
, eanl.drckstuf
, eanl.anlart
, eanl.bezug
, eanl.ablesartst
, eanl.nodisconct
, eanl.service
, eanl.deregstat
, eanl.inforel
, eanl.etimezone
, eanl.zzinkoop
, eanl.zzinstgrp
, eanl.zzgucontr
, eanl.zzwarmte
, eanl.zzmtschnr
, eanl.zzobjcode
, eanl.zzean_aansl
, eanl.zzean_prof
, eanl.zzean_verbr_hg
, eanl.zzean_verbr_lg
, eanl.zzstatus_aansl
, eanl.zzdoorlaatwaarde
, eanl.zzafsluitreden
, eanl.zzafsluitwijze
, eanl.zzpgosnr
, eanl.zz_opdr_status
, eanl.zzafsluitbaarh
, eanl.siebel_comm_name
, eanl.zzcapacity_tarif
, eanl.zzcapacity_phys
, eanl.zzusage_type
, eanl.zzphysical_stat
, eanl.zz_ean_odn
, eanl.zz_ean_overdr_pt
, eanl.zz_generator_id
, eanl.zz_certiq_ean
, eanl.zz_elec_adres_zp
, eanl.zz_intrn_adr_zp
, eanl.zzschakelregime
, eanl.erdat
, eanl.ernam
, eanl.aedat
, eanl.aenam
, eanl.begru
, eanl.loevm
, trs.mandt trs_mandt
, trs.int_ui trs_int_ui
, trs.dateto trs_dateto
, trs.timeto trs_timeto
, trs.datefrom trs_datefrom
, trs.timefrom trs_timefrom
, trs.ext_ui trs_ext_ui
, trs.uistrutyp
, trs.erdat trs_erdat
, trs.ernam trs_ernam
, trs.aedat trs_aedat
, trs.aenam trs_aenam
, trs.begru trs_begru
, trs.loevm trs_loevm
, ins.mandt ins_mandt
, ins.int_ui ins_int_ui
, ins.anlage ins_anlage
, ins.dateto ins_dateto
, ins.timeto ins_timeto
, ins.datefrom ins_datefrom
, ins.timefrom ins_timefrom
, ins.euirole_tech
, ins.euirole_dereg
, ins.erdat ins_erdat
, ins.ernam ins_ernam
, ins.aedat ins_aedat
, ins.aenam ins_aenam
, ins.begru ins_begru
, ins.loevm ins_loevm
FROM owner_osp.osp_euitrans trs
, owner_osp.osp_euiinstln ins
, owner_osp.osp_eanl eanl
WHERE ins.int_ui = trs.int_ui
AND eanl.anlage = ins.anlage
-----------------------------------------
-----------------------------------------
The following statement is being rewritten so the rights etc are correct.
SELECT eanl.anlage, COUNT(1) OVER (PARTITION BY eanl.anlage) aantal
FROM owner_cds.cds_osp_euitrans trs
, owner_cds.cds_osp_euiinstln isn
, owner_cds.cds_osp_eanl eanl
WHERE eanl.anlage = isn.anlage
AND isn.int_ui = trs.int_ui
Plan
SELECT STATEMENT ALL_ROWSCost: 27,604 Bytes: 126,397,392 Cardinality: 11,490,672
2 WINDOW SORT Cost: 27,604 Bytes: 126,397,392 Cardinality: 11,490,672
[b]1 MAT_VIEW REWRITE ACCESS FULL MAT_VIEW REWRITE OWNER_CDS.MV2 [/b]Cost: 2,937 Bytes: 126,397,392 Cardinality: 11,490,672
==========================================================
This statement isn't rewritten
SELECT trs.ext_ui ean_code ,
eanl.anlage anlage_code ,
eanl.sparte energiesoort_code ,
eprov.externalid AS netbeheerder_eancode ,
eanl.zzean_prof profiel_code ,
eanl.zzean_verbr_hg zzean_verbr_hg ,
eanl.zzean_verbr_lg zzean_verbr_lg ,
eanl.vstelle verbruiksplaats ,
eanl.ablesartst opnamebesturing ,
gos.eancode gos_eancode ,
SUBSTR(gos.regiocode,3,1) regio ,
ROW_NUMBER() OVER (PARTITION BY eserv.int_ui ORDER BY eanl.loevm, eserv.loevm, eserv.service_start DESC, eserv.vertrag) AS rnum
FROM owner_osp.osp_euitrans trs ,
owner_osp.osp_euiinstln isn ,
owner_osp.osp_zgos gos ,
owner_osp.osp_eservice eserv,
owner_osp.osp_eservprov eprov,
owner_osp.osp_eanl eanl
WHERE trs.dateto = '99991231'
AND trs.begru <> 'B'
AND isn.begru <> 'B'
AND eanl.begru <> 'B'
AND eanl.sparte IN ('E', 'G', 'K', 'S', 'WW')
AND isn.int_ui = trs.int_ui
AND isn.dateto = '99991231'
AND eanl.anlage = isn.anlage
AND isn.int_ui = eserv.int_ui
AND SUBSTR(eserv.serviceid,2,1) = 'N'
AND eserv.serviceid = eprov.serviceid
AND eanl.zzpgosnr = gos.pgosnr (+)
However if i delete this part My MV is being used.:
ROW_NUMBER() OVER (PARTITION BY eserv.int_ui ORDER BY eanl.loevm, eserv.loevm, eserv.service_start DESC, eserv.vertrag) AS rnum
I didnt expect this behaviour nor can i find any documentation on this problem. Could someone help me out ?
Regards
Remy Ottenheim
[Updated on: Tue, 27 October 2009 10:48] Report message to a moderator
|