Forum Stats

  • 3,741,519 Users
  • 2,248,442 Discussions
  • 7,861,846 Comments

Discussions

How to create a materialized view on a view and how to prevent it from going stale/needs compile

ASub
ASub Member Posts: 28 Blue Ribbon

I have the following view that is based on Oracle E-Business suite supplied tables. I want to create a materialized view in another schema that is based on this view. The materialized view will then be used in Oracle Application Express (APEX) app. I created the materialized view as BUILD IMMEDIATE, REFRESH COMPLETE, ON DEMAND and then created a refresh group to refresh once a day and assigned this materialized view to that refresh group. When the data was updated on one of the base tables, the materialized view became stale and the status said 'Needs compilation'. I can understand that it's due to the materialized view being out of sync with the base view since the data changed in the base view. The problem is, it made 4 other Oracle E-Business suite supplied materialized views invalid. I'm not sure why or how. I then dropped the refresh group, materialized view, and then created again with REFRESH START WITH SYSDATE NEXT SYSDATE+1. When I did that and then accessed that materialized view through the APEX application, it ran fine the first time, but then the query hung the second time. I checked and the MV went to a stale state (without any update to the base table data) and caused one of the Oracle EBS supplied MV to go invalid also. The query hung because the MV went to the "Needs compile" state. I am not sure what was causing the issue. I then thought I would create a fast refresh instead of complete. The examples I see for fast refreshes require materialized view logs based on the base table with a primary key or row id. I use a view that has multiple tables. I am not sure how to create the logs. Also, I believe I have to use ON DEMAND (and not ON COMMIT) since I am using a view with multiple tables. Also, how do I prevent the MV from going stale with the "needs compile" status? Any guidance on this is appreciated.


Here is the view statement.


CREATE OR REPLACE FORCE VIEW APPS.SIUPER_PE_FISCAL_OFFICER_V

(

  FISCAL_OFFICER_NAME,

  FISCAL_OFFICER_ID,

  FISCAL_OFFICER_EMAIL,

  EMPLOYEE_NAME,

  ASSIGNMENT_NUMBER,

  ASSIGNMENT_ID,

  EMPLOYEE_NUMBER,

  EMPLOYEE_ID

)

BEQUEATH DEFINER

AS

  SELECT DISTINCT

      INITCAP (SUBSTR (ffv.ATTRIBUTE4, 1, 50))   fiscal_officer_name,

      usr.employee_id               fiscal_officer_id,

      usr.email_address              fiscal_officer_email,

      e.full_name                 employee_name,

      a.assignment_number,

      a.assignment_id,

      e.employee_number,

      e.person_id                 employee_id

   FROM APPS.PAY_COST_ALLOCATIONS_F    c,

      APPS.PAY_COST_ALLOCATION_KEYFLEX pcak,

      APPS.FND_FLEX_VALUES       ffv,

      APPS.FND_FLEX_VALUES_TL      ffvt,

      apps.per_all_assignments_f    a,

      apps.per_all_people_f       e,

      apps.per_jobs           j,

      apps.per_job_definitions     jd,

      apps.siugl_officer_v       ov,

      apps.fnd_user           usr

   WHERE   a.person_id = e.person_id

      AND TRUNC (SYSDATE) BETWEEN e.effective_start_date

                  AND e.effective_end_date

      AND TRUNC (SYSDATE) BETWEEN a.effective_start_date

                  AND a.effective_end_date

      AND (  a.assignment_status_type_id = '1'

        OR a.assignment_status_type_id = '141' 

        OR a.assignment_status_type_id = '161' 

        OR a.assignment_status_type_id = '461') 

      AND a.job_id = j.job_id

      AND (j.date_to IS NULL OR j.date_to >= TRUNC (SYSDATE))

      AND SUBSTR (j.name, 1, 1) <> 'E'

      AND j.job_definition_id = jd.job_definition_id

      AND a.ASSIGNMENT_ID = c.ASSIGNMENT_ID

      AND TRUNC (SYSDATE) BETWEEN c.effective_start_date

                  AND c.effective_end_date

      AND c.proportion <> '0'

      AND pcak.COST_ALLOCATION_KEYFLEX_ID = c.COST_ALLOCATION_KEYFLEX_ID

      AND pcak.SEGMENT3 = ffv.FLEX_VALUE

      AND ffv.value_category = 'SIUGL_BP'

      AND ffv.FLEX_VALUE_ID = ffvt.FLEX_VALUE_ID

      AND ov.name = ffv.attribute4

      AND usr.user_name = ov.userid_id

      AND (usr.end_date IS NULL OR usr.end_date >= TRUNC (SYSDATE));


Here is the MV I tried first which made 4 other Oracle supplied MVs invalid and went to stale state and needed recompile/manual refresh.


CREATE MATERIALIZED VIEW APEX_EBS_EXTENSION.SIUPER_PE_FISCAL_OFFICER_MV

BUILD IMMEDIATE 

REFRESH COMPLETE

ON DEMAND

AS

SELECT * FROM APPS.SIUPER_PE_FISCAL_OFFICER_V

/

BEGIN

 DBMS_STATS.gather_table_stats(

  ownname => 'APEX_EBS_EXTENSION',

  tabname => 'SIUPER_PE_FISCAL_OFFICER_MV');

END;

/

BEGIN

  DBMS_REFRESH.make(

   name         => 'APEX_EBS_EXTENSION.SIUPER_PE_FO_REFRESH',

   list         => '',

   next_date      => SYSDATE,

   interval       => 'SYSDATE + 1',

   implicit_destroy   => FALSE,

   lax         => FALSE,

   job         => 0,

   rollback_seg     => NULL,

   push_deferred_rpc  => TRUE,

   refresh_after_errors => TRUE,

   purge_option     => NULL,

   parallelism     => NULL,

   heap_size      => NULL);

END;

/


BEGIN

  DBMS_REFRESH.add(

   name => 'APEX_EBS_EXTENSION.SIUPER_PE_FO_REFRESH',

   list => 'APEX_EBS_EXTENSION.SIUPER_PE_FISCAL_OFFICER_MV');

END;

/

Sign In or Register to comment.