Home » Server Options » Replication » Issue with creation of a materialized view (merged)
Issue with creation of a materialized view (merged) [message #279503] Thu, 08 November 2007 18:51 Go to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
I have a SQL query that works fine.

But when I create a materialized view using the query, a few columns in the MV do not get populated at all.

What could be the issue here?

regards
Prem
Re: Issue with creation of a materialized view [message #279508 is a reply to message #279503] Thu, 08 November 2007 19:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated above.

My car engine starts. Car won't go.
What could be the problem?

I challenge you post question that is more vague & devoid of useful information than this one.
Re: Issue with creation of a materialized view [message #279518 is a reply to message #279508] Thu, 08 November 2007 19:18 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
hi,
My apologies. I thought this was a generic error and would have some general answers. Something like if my cars engine starts, but wont move...check if the brake is pressed..

Anyways, I am sorry!

here is the code. The underlying SQL works fine and gives the result for all the columns. But the materialized view doesnt have any value for 6-7 columns. (from milestone_KEY to oppty_end_date)

Any clues?

CREATE MATERIALIZED VIEW EDW.MV_OPPTY_ACTIVITY_AGG 
TABLESPACE EDW_DIM_DAT
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS 
select 
    OPPTY_ID OPPTY_ID,
    OPPORTUNITY_KEY OPPORTUNITY_KEY,
    oppty_number oppty_number , 
    SALES_CONSULTANT_HIST_KEY SALES_CONSULTANT_HIST_KEY  ,
    company_add_source_key  company_add_source_key, 
    SALES_CONSULTANT_ID SALES_CONSULTANT_ID,
    ACTIVITY_TYPE ACTIVITY_TYPE  ,
    activity_DATE activity_DATE ,
    LAST_MODIFIED_DATE LAST_MODIFIED_DATE ,
    CUSTOMER_FLAG   CUSTOMER_FLAG,
    SALES_EMPLOYEE_HIST_KEY,
    milestone_KEY,
    oppty_milestone_name_hist stage_name,
    oppty_milestone_id_hist stage_id, 
    milestone_prob prob_to_close,
    start_date oppty_start_date,
    oppty_close_date oppty_close_date,
    end_date oppty_end_date,
   
    sum(DURATION) TOTAL_DURATION,
--
   sum (CASE WHEN ACTIVITY_type IN ('CU14', 'CU15', 'CU16', 'CU17', 

                      'CU18', 'CU19', 'CU20', 'CU21') THEN DURATION ELSE 0 END ) CQM_DURATION, 

   sum  (CASE WHEN ACTIVITY_type = 'CU01' THEN DURATION ELSE 0 END ) TRADE_SEM_DURATION, 

   sum( CASE WHEN ACTIVITY_type = 'CU02' THEN DURATION ELSE 0 END ) RFP_RFI_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU03' THEN DURATION ELSE 0 END ) INFO_GATH_DURATION, 

   sum( CASE WHEN ACTIVITY_type = 'CU04' THEN DURATION ELSE 0 END ) PREP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU05' THEN DURATION ELSE 0 END ) PRES_DEMO_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU06' THEN DURATION ELSE 0 END ) POC_DURATION, 

   sum ( CASE WHEN ACTIVITY_type = 'CU07' THEN DURATION ELSE 0 END ) PRE_CUST_SUPP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU08' THEN DURATION ELSE 0 END ) POST_CUST_SUPP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU09' THEN DURATION ELSE 0 END ) PARTNER_SUPP_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU10' THEN DURATION ELSE 0 END ) TRAVEL_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU11' THEN DURATION ELSE 0 END ) KEYCODES_DURATION, 

   sum(  CASE WHEN ACTIVITY_type = 'CU12' THEN DURATION ELSE 0 END ) SI_SUPP_DURATION, 

   sum ( CASE WHEN ACTIVITY_type = 'CU13' THEN DURATION ELSE 0 END ) CHANNEL_SUPP_DURATION,

   sum(  CASE WHEN ACTIVITY_type = 'CU23' THEN DURATION ELSE 0 END ) MIGRATION_DURATION ,   


--    
    max(LICENSE_USD) LICENSE_USD ,
            MAX(LICENSE_LOCAL) LICENSE_LOCAL,
            MAX(maintAINANCE_usd) maintAINANCE_usd,
            MAX(maintAINANCE_LOCAL) maintAINANCE_LOCAL,
            MAX(TRAINING_USD) training_usd,
            MAX(TRAINING_LOCAL) training_LOCAL,
            MAX(CONSULTING_USD) consulting_usd,
            MAX(CONSULTING_LOCAL) consulting_LOCAL,
            MAX(RENEWALS_USD) renewals_usd ,
            MAX(RENEWAL_local) RENEWAL_local,
            max(LOBANALYTICS2_LOCAL) epm_local,   
            max(LOBDATAINTEGRATION2_LOCAL) eim_local,
            max(IDD_AMOUNT_LOCAL) idd_local,
            max(ONDEMAND_AMOUNT_LOCAL) ondemand_local,
            max(LOBANALYTICS2_usd) epm_usd,   
            max(LOBDATAINTEGRATION2_usd) eim_usd,
            max(IDD_AMOUNT_usd) idd_usd,
            max(ONDEMAND_AMOUNT_usd) ondemand_usd
  
FROM edw.sales_consulting_activity_fct t1 ,
     (
    select 
    o.oppty_id_number,
             o.oppty_id,
             O.OPPORTUNITY_KEY,

            LICENSE_USD LICENSE_USD ,
            LICENSE_LOCAL LICENSE_LOCAL,
            o.FYM_USD maintAINANCE_usd,
            o.FYM_LOCAL maintAINANCE_LOCAL,
            o.TRAINING_USD training_usd,
            o.TRAINING_LOCAL training_LOCAL,
            o.CONSULTING_USD consulting_usd,
            o.CONSULTING_LOCAL consulting_LOCAL,
            o.RENEWAL_USD renewals_usd ,
            o.RENEWAL_local RENEWAL_local  ,
            o.LOBANALYTICS2_LOCAL,            
            o.LOBANALYTICS2_usd,
            o.LOBDATAINTEGRATION2_LOCAL,
            o.IDD_AMOUNT_LOCAL,
            o.ONDEMAND_AMOUNT_LOCAL,
            o.LOBDATAINTEGRATION2_usd,
            o.IDD_AMOUNT_usd,
            o.ONDEMAND_AMOUNT_usd


            from edw.opportunity_hist_fact o 
            where  o.SOURCE_SYSTEM='SFDC' 
            and upper(status)='ACTIVE'
            and o.last_modified_date = (select max(last_modified_date )from edw.opportunity_hist_fact o2 where o.opportunity_key = o2.opportunity_key))T2,
             ( Select activity_key,OPPTY_ID_NUMBER, MIL.milestone_KEY,MIL.milestone_name oppty_milestone_name_hist,milestone_id 
                oppty_milestone_id_hist, milestone_prob,start_date,oppty_close_date,end_date FROM
                EDW.opportunity_hist_fact OPP,
                edw.milestone MIL,
                edw.sales_consulting_activity_fct act WHERE 
                ACTIVITY_DATE  >=start_date (+) and activity_date  <=opp.last_modified_date(+)  
                and OPP.OPPTY_ID_NUMBER (+) =act.OPPTY_NUMBER   
                and OPP.milestone_key =MIL.milestone_key(+) ) T3
             where t1.oppty_NUMBER (+) =T2.OPPTY_ID_NUMBER 
             and   t1.activity_key=t3.activity_key
             --and oppty_number ='279749'
GROUP BY   OPPTY_ID,
     oppty_number,
     OPPORTUNITY_KEY,
     SALES_CONSULTANT_HIST_KEY  ,
     company_add_source_key,
     SALES_CONSULTANT_ID,
     SALES_EMPLOYEE_HIST_KEY,
     ACTIVITY_TYPE  ,
     ACTIVITY_DATE ,
     LAST_MODIFIED_DATE,
     CUSTOMER_FLAG,
     milestone_KEY,
    oppty_milestone_name_hist,
    oppty_milestone_id_hist, 
    milestone_prob,
    start_date,
    oppty_close_date,
    end_date;


Does anyone know why?

Thanks
prem
Re: Issue with creation of a materialized view [message #279523 is a reply to message #279503] Thu, 08 November 2007 19:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What Operating System name & version?
What version Oracle to 4 decimal places?
What happens if you try something like the following just as a test?
select 
    OPPTY_ID OPPTY_ID,
    milestone_KEY,
    oppty_milestone_name_hist stage_name,
    oppty_milestone_id_hist stage_id, 
    milestone_prob prob_to_close,
    start_date oppty_start_date,
    oppty_close_date oppty_close_date,
    end_date oppty_end_date,
    OPPORTUNITY_KEY OPPORTUNITY_KEY,
    oppty_number oppty_number , 
    SALES_CONSULTANT_HIST_KEY SALES_CONSULTANT_HIST_KEY  ,
    company_add_source_key  company_add_source_key, 
    SALES_CONSULTANT_ID SALES_CONSULTANT_ID,
    ACTIVITY_TYPE ACTIVITY_TYPE  ,
    activity_DATE activity_DATE ,
    LAST_MODIFIED_DATE LAST_MODIFIED_DATE ,
    CUSTOMER_FLAG   CUSTOMER_FLAG,
    SALES_EMPLOYEE_HIST_KEY,
   

Does the lack of data follow the named columns or do different columns not get populated or something else happens?

[Updated on: Thu, 08 November 2007 19:37] by Moderator

Report message to a moderator

Re: Issue with creation of a materialized view [message #279526 is a reply to message #279523] Thu, 08 November 2007 20:00 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Thanks for the reply.
The OS is Windows XP.
Oracle 10g is used.

Yes, the data follows the same columns if I change the column positions. The same columns remain unpopulated in the materialized view.

The SQL query works fine though.
regards
Prem
Re: Issue with creation of a materialized view [message #279556 is a reply to message #279503] Fri, 09 November 2007 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In a normal context that we assume you have as you don't specify anything about your environment, this is not possible.

Regards
Michel
Re: Issue with creation of a materialized view [message #279559 is a reply to message #279526] Fri, 09 November 2007 00:54 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
What method do you use to populate it?
Reason I ask is that the reference says about refresh force in relation to fast refresh:
"If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met."
Which seems to imply that only a part gets updated, which is exactly what you describe.

Haven't come across this before though, just investigating possibilities Wink
Re: Issue with creation of a materialized view [message #279564 is a reply to message #279503] Fri, 09 November 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And don't multipost.

Regards
Michel
Re: Issue with creation of a materialized view [message #279572 is a reply to message #279564] Fri, 09 November 2007 01:44 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
Sorry the repetition was unintended. I found I had posted in wrong forum so posted it again here.

The refresh used is COMPLETE ON DEMAND. So I guess it should refresh using the underlying query. But it is not doing so.

any clues ?

Thanks a million for all the replies.

Regards
Prem
Re: Issue with creation of a materialized view [message #279779 is a reply to message #279572] Sat, 10 November 2007 03:49 Go to previous message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
I would try to narrow it down, like: create a materialized view with only the columns that are not populated now, does that new MV get populated?

(edit: typo)

[Updated on: Sat, 10 November 2007 03:49]

Report message to a moderator

Previous Topic: Oracle 9i Multi-master Replication
Next Topic: Repication through Trigger
Goto Forum:
  


Current Time: Fri Mar 29 07:14:54 CDT 2024