Home » SQL & PL/SQL » SQL & PL/SQL » Query Rewrite Not selected (ORACLE 10.2.0.1)
Query Rewrite Not selected [message #428257] Tue, 27 October 2009 10:43 Go to next message
remyo
Messages: 1
Registered: October 2009
Junior Member
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

Re: Query Rewrite Not selected [message #428307 is a reply to message #428257] Tue, 27 October 2009 18:10 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check DBMS_MVIEW.EXPLAIN_REWRITE to see if it provides any reason for failure to rewrite.
Previous Topic: Multi Table Outer Join Help
Next Topic: total rowcount group by second
Goto Forum:
  


Current Time: Sun Dec 04 02:57:02 CST 2016

Total time taken to generate the page: 0.16176 seconds