Hi
I am trying to update an existing view but "CREATE or REPLACE" takes forever. When I try to see the results of that view, I can see the results quickly. Also, I am able to create/update new/existing views very fast.
CREATE OR REPLACE VIEW OBR.IFV_OFFC_MGR_INFO
AS
/* --------------------------------------------------------
* IFV_OFFC_MGR_INFO
* --------------------------------------------------------
*
* View to be used by those objects that require the office
* manager information pertinent to an office.
*
* This is designed to return all Office Manager Info from
* various tables that store the related information
* for an agent.
*
*
* --------------------------------------------------------
*/
SELECT
NVL(MD.OFFC_TERR_CD, NVL(OCM.OFFC_TERR_CD, NVL(TVP.OFFC_CD, COS.OFFC_CD))) OFFC_CD,
MD.IONS_ID MD_IONS_ID,
MD.ASSOC_NAME MD_NAME,
MD.ASSOC_EMAIL_ID MD_EMAIL_ID,
OCM.IONS_ID OCM_IONS_ID,
OCM.ASSOC_NAME OCM_NAME,
OCM.ASSOC_EMAIL_ID OCM_EMAIL_ID,
OCC.IONS_ID OCC_IONS_ID,
OCC.ASSOC_NAME OCC_NAME,
OCC.ASSOC_EMAIL_ID OCC_EMAIL_ID,
TVP.IONS_ID TVP_IONS_ID,
TVP.ASSOC_NAME TVP_NAME,
TVP.ASSOC_EMAIL_ID TVP_EMAIL_ID,
COS.IONS_ID TVP_COS_IONS_ID,
COS.ASSOC_NAME TVP_COS_NAME,
COS.ASSOC_EMAIL_ID TVP_COS_EMAIL_ID,
NVL(MD.TERR_DESC, NVL(OCM.TERR_DESC, NVL(TVP.TERR_DESC, COS.TERR_DESC))) TERR_NAME
FROM
( SELECT
DISTINCT OFFC_TERR_CD,
TC.TERR_DESC,
FMA.IONS_ID,
OTC.OFFC_CD,
FMA.ASSOC_NAME,
FMA.ASSOC_EMAIL_ID
FROM OBR.IFT_IONS_OFFC_TERR IOT,
OBR.IFT_OFFC_TERR_CD OTC,
OBR.IFT_FLD_MGMT_ASSOC FMA,
OBR.IFT_TERR_CD TC
WHERE IOT.OFFC_TERR_CD = OTC.TERR_CD
AND ROLE_CD='TVP'
AND IOT.OBR_ASSOC_ID = FMA.OBR_ASSOC_ID
AND TC.TERR_CD = OTC.TERR_CD) TVP
FULL OUTER JOIN
( SELECT
DISTINCT OFFC_TERR_CD,
TC.TERR_DESC,
FMA.IONS_ID,
OTC.OFFC_CD,
FMA.ASSOC_NAME,
FMA.ASSOC_EMAIL_ID
FROM OBR.IFT_IONS_OFFC_TERR IOT,
OBR.IFT_OFFC_TERR_CD OTC,
OBR.IFT_FLD_MGMT_ASSOC FMA,
OBR.IFT_TERR_CD TC
WHERE IOT.OFFC_TERR_CD = OTC.TERR_CD
AND ROLE_CD='COS'
AND IOT.OBR_ASSOC_ID = FMA.OBR_ASSOC_ID
AND TC.TERR_CD = OTC.TERR_CD) COS
ON TVP.OFFC_CD = COS.OFFC_CD
FULL OUTER JOIN
( SELECT
DISTINCT OFFC_TERR_CD,
TC.TERR_DESC,
FMA.IONS_ID,
FMA.ASSOC_NAME,
FMA.ASSOC_EMAIL_ID
FROM OBR.IFT_IONS_OFFC_TERR IOT,
OBR.IFT_OFFC_TERR_CD OTC,
OBR.IFT_FLD_MGMT_ASSOC FMA,
OBR.IFT_TERR_CD TC
WHERE IOT.OFFC_TERR_CD = OTC.OFFC_CD
AND ROLE_CD='OCC'
AND IOT.OBR_ASSOC_ID = FMA.OBR_ASSOC_ID
AND OTC.TERR_CD = TC.TERR_CD) OCC
ON TVP.OFFC_CD = OCC.OFFC_TERR_CD
FULL OUTER JOIN
( SELECT
DISTINCT OFFC_TERR_CD,
TC.TERR_DESC,
FMA.IONS_ID,
FMA.ASSOC_NAME,
FMA.ASSOC_EMAIL_ID
FROM OBR.IFT_IONS_OFFC_TERR IOT,
OBR.IFT_OFFC_TERR_CD OTC,
OBR.IFT_FLD_MGMT_ASSOC FMA,
OBR.IFT_TERR_CD TC
WHERE IOT.OFFC_TERR_CD = OTC.OFFC_CD
AND ROLE_CD='OCM'
AND IOT.OBR_ASSOC_ID = FMA.OBR_ASSOC_ID
AND OTC.TERR_CD = TC.TERR_CD) OCM
ON TVP.OFFC_CD = OCM.OFFC_TERR_CD
FULL OUTER JOIN
( SELECT
DISTINCT OFFC_TERR_CD,
TC.TERR_DESC,
FMA.IONS_ID,
FMA.ASSOC_NAME,
FMA.ASSOC_EMAIL_ID
FROM OBR.IFT_IONS_OFFC_TERR IOT,
OBR.IFT_OFFC_TERR_CD OTC,
OBR.IFT_FLD_MGMT_ASSOC FMA,
OBR.IFT_TERR_CD TC
WHERE IOT.OFFC_TERR_CD = OTC.OFFC_CD
AND ROLE_CD='MD'
AND IOT.OBR_ASSOC_ID = FMA.OBR_ASSOC_ID
AND OTC.TERR_CD = TC.TERR_CD) MD
ON TVP.OFFC_CD = MD.OFFC_TERR_CD
ORDER BY 2;
Little history: Earlier we had one table IFT_OFFC_MGR_INFO storing information of each office and the details of MD, OCM, TVP etc Every office has one row with name, email and id of MD, OCM, TVP and COS. Since it was not normalized so we normalized it for new expansions to office associates by creating multiple tables. Since we were using that unnormalized table IFT_OFFC_MGR_INFO in various places and to minimize the impact we created this view that would return result in similar format as it was in that table. We found that some of the existing views that was using IFT_OFFC_MGR_INFO and replacing that with new view IFV_OFFC_MGR_INFO was just hanging that view. I am not sure if there are any better approach to create this view so that it can return the result in similar format as unnormalized table. This table generally has only 50 odd rows. Please help me to resolve this production issue!!!