Home » Server Options » Replication » Materialized View
Materialized View [message #29584] Fri, 12 March 2004 04:57 Go to next message
luca martino
Messages: 2
Registered: March 2004
Junior Member
A materialized view declared  as below does make any locks on the table of from clause?

CREATE MATERIALIZED VIEW SLAMUSER04ADM.GUI_DETT_RICH_FMOBILE_ PCTFREE 40 PCTUSED 60 INITRANS 4 MAXTRANS 255

STORAGE(

INITIAL 64 K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

TABLESPACE SLAMTBP

LOGGING

NOCACHE

NOPARALLEL

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(

BUFFER_POOL DEFAULT

)

REFRESH COMPLETE

WITH ROWID

USING DEFAULT LOCAL ROLLBACK SEGMENT

DISABLE QUERY REWRITE AS

SELECT CLI.NOME_CLIENTE_SLAM AS NOME_CLIENTE ,

CLI.ID_CLIENTE_SLAM AS CODICE_CLIENTE ,

CLI.PARTITA_IVA_SLAM AS PIVA ,

CLI.COD_FISCALE_SLAM AS CODICE_FISCALE ,

CLI.SEG_CLIENTE AS SEGMENTO ,

CTR.ID_CONTRATTO_SLAM AS CODICE_CONTRATTO ,

GS.GRUPPO_SERVIZIO AS SERVIZIO ,

OFT.DESCRIPTION AS OFFERTA ,

TO_CHAR(CTR.DATA_FIRMA_CONTRATTO_SLAM,'DD/MM/YYYY') AS DATA_FIRMA_CTR ,

TO_CHAR(CTR.DATA_ATT_CONTR_SLAM,'DD/MM/YYYY') AS DATA_ATTIVAZ_CTR ,

RI.ID_INTERAZIONE_SLAM AS ID_INTERAZIONE,

RI.ID_CASE_SLAM AS ID_CASE,

CET.CONTR_EVENT_TYPE_DESC AS EVENTO_CONTRATTUALE ,

PRJ.PROJECT_TYPE_DESC AS TIPO_PROGETTO ,

DECODE(NVL(RI.FLAG_ADESIONE_LEONARDO,0),1,'SI','NO') AS FLAG_LEONARDO,

DECODE(NVL(RI.FLAG_CLIENTE_HOLDING_SLAM,0),1,'SI','NO') AS FLAG_HOLDING,

DECODE(NVL(RI.FLAG_GREENWICH_SLAM,0),1,'SI','NO') AS FLAG_GREENWICH,

TO_CHAR(RI.DATA_FIRMA ,'DD/MM/YYYY') AS DATA_FIRMA ,

TO_CHAR(RI.DATA_INIZIO_INTERAZIONE_SLAM,'DD/MM/YYYY') AS DATA_CREAZIONE_INTERAZ,

WFS.WF_STATUS_DESC AS STATO ,

(RI.CASE_CONCLUSION_SLAM || '-' || RI.CASE_SUBCONCLUSION_SLAM) AS ESITO,

IST.INDICATOR_STATISTIC_VALUE AS KPI,

T.THRESHOLD_MAX AS KPO,

TOT_SIM_STD_SLAM AS TOT_SIM, TOT_SIM_MNP_SLAM AS TOT_SIM_MNP,

TOT_SIM_WPN_SLAM AS TOT_SIM_WPN, TOT_SIM_MNP_WPN_SLAM AS TOT_SIM_WPN_MNP,

TOT_SIM_GREENWICH_MNP_SLAM AS TOT_SIM_GRE_MNP, TOT_SIM_GREENWICH_SLAM AS TOT_SIM_GRE,

TOT_HANDSET_NOLEGGIO AS TOT_HDS_NOL,TOT_HANDSET_VENDITA AS TOT_HDS_VEN,

FLAG_WPN_SLAM AS TOT_WPN

FROM REPORT_CLIENTE CLI ,REPORT_CONTRATTO CTR, REPORT_INTERAZIONE_FMOBILE RI ,

OFFER_TYPE OFT , PROJECT_TYPE PRJ ,CONTR_EVENT_TYPE CET ,GRUPPO_SERVIZIO GS,

WF_STATUS WFS , WF_HEADER WFH ,THRESHOLD T ,INDICATOR_STATISTIC IST

WHERE CTR.ID_CLIENTE_SLAM = CLI.ID_CLIENTE_SLAM

AND RI.ID_CLIENTE_SLAM = CLI.ID_CLIENTE_SLAM

AND RI.ID_CONTRATTO_SLAM= CTR.ID_CONTRATTO_SLAM

AND ( WFS.WF_STATUS_ID <> '13' OR CTR.DATA_ATT_CONTR_SLAM IS NOT NULL )

AND OFT.OFFER_ID = CTR.OFFER_ID_SLAM

AND RI.OBJECT_HEADER_ID = WFH.OBJECT_HEADER_ID

AND WFH.WF_STATUS_ID = WFS.WF_STATUS_ID

AND RI.CONTR_EVENT_TYPE_ID = CET.CONTR_EVENT_TYPE_ID

AND IST.BASED_OBJECT_HEADER_ID = RI.OBJECT_HEADER_ID

AND IST.INDICATOR_ID IN (SELECT FE.INDICATOR_ID FROM FE_INDICATOR_TYPES FE

WHERE FE.FE_E2E_FLAG=1 AND FE.INDICATOR_CLASS_TYPE_ID=8)

AND IST.THRESHOLD_ID = T.THRESHOLD_ID

AND PRJ.PROJECT_TYPE_ID = RI.TIPOLOGIA_PROGETTO_SLAM

AND GS.SPECIFICA = RI.SPECIFICA_SLAM

 

 

 

 
Re: Materialized View [message #29597 is a reply to message #29584] Fri, 12 March 2004 13:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It is just a select statement like any other. So just like other SQL, it only locks if you have "for update" in the sql.
Re: Materialized View [message #29617 is a reply to message #29597] Mon, 15 March 2004 02:59 Go to previous message
luca martino
Messages: 2
Registered: March 2004
Junior Member
Thanks.I'm agree with you, as oracle's manulas says the statment of my first mail should create a lock only if I declare "for update".

I'm using Oracle 8.1.7

But this snapshot creates an inespected lock.
I invoke refresh method on that snapshot during an execution of a java-based bach. The bach makes insert or update on a table whose value are taken by snapshot.
I'm sure that bach doesn't create any write-lock (transactions are managed by Weblogic application server) or read-lock (never I declare for update select).
I see, on Oracle system user, views v$lock and v$locked_object here appear Lock_type as 'JI' and lock_Mode as 'Exclusive'.
Snapshot's refresh and bach's execution never end.

Are there any constants to set invoking of refresh method?

Thank You.

Luca
Previous Topic: Partitions in materialized views
Next Topic: Materilized view group order
Goto Forum:
  


Current Time: Thu Mar 28 17:34:24 CDT 2024