This discussion is archived
11 Replies Latest reply: Feb 21, 2013 9:18 PM by Hemant K Chitale RSS

Materialized View - Aggregate Functions - Fast Refreshes

874144 Newbie
Currently Being Moderated
Hi All,

I've played with Materialized Views before, and created some rather complex views, but for the life of me, I just cannot manage to create something similar to this:

CREATE OR REPLACE FORCE VIEW "CURRENT_JOB_STATUS" ("JOB_STATUS_NO", "JOB_NO", "STATUS_DATE", "STATUS_ID", "ASSIGNED_TO", "COST_CENTRE_NO", "NOTE_NO", "MODIFIED_BY") AS
SELECT js.job_status_no,
js.job_no,
js.status_date,
js.status_id,
js.assigned_to,
js.cost_centre_no,
js.note_no,
js.modified_by
FROM job_statuses js
WHERE (js.job_no, js.job_status_no) IN
( SELECT job_no, MAX (job_status_no) job_status_no
FROM job_statuses
GROUP BY job_no)
/
‚Äč

The JOB_STATUSES table records all changes to a job by simply inserting a new record (when a change occurs), and the JOB_STATUS_NO (primary key) is created from a sequence ON INSERT. As can be seen, the record with the MAX(JOB_STATUS_NO) represents the most recent or latest status record for a given job.

I want to replace the above view, with a materialized view which is refreshed on commit of a record to the JOB_STATUSES table. I have created a materialized view log on JOB_STATUSES as:

CREATE MATERIALIZED VIEW LOG ON job_statuses
WITH SEQUENCE, PRIMARY KEY, ROWID (job_no, status_date, status_id, assigned_to, cost_centre_no, note_no, modified_by)
     INCLUDING NEW VALUES;

However, when I attempt to create the materialized view below, I get this error - ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

CREATE MATERIALIZED VIEW current_job_status_v2
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT js.job_status_no,
js.job_no,
js.status_date,
js.status_id,
js.assigned_to,
js.cost_centre_no,
js.note_no,
js.modified_by
FROM job_statuses js
WHERE (js.job_no, js.job_status_no) IN
( SELECT job_no, MAX (job_status_no) job_status_no
FROM job_statuses
GROUP BY job_no)
/

I have tried rewriting the query several different ways to achieve the same result, but have always encountered an error during the execution of the create command.

Can anyone suggest what I am doing wrong here please?


Thanks in Advance
Brad

BTW - I am using Oracle 11gR2 on OE Linux.
  • 1. Re: Materialized View - Aggregate Functions - Fast Refreshes
    rp0428 Guru
    Currently Being Moderated
    >
    Can anyone suggest what I am doing wrong here please?
    >
    Your MV definition doesn't match the projection of your query.
    WITH SEQUENCE, PRIMARY KEY, ROWID (job_no, status_date, status_id, assigned_to, cost_centre_no, note_no, modified_by)
    . . .
    SELECT js.job_status_no,
    js.job_no,
    js.status_date,
    js.status_id,
    js.assigned_to,
    js.cost_centre_no,
    js.note_no,
    js.modified_by
    The first column in the def is 'job_no' but the first column in the select is 'job_status_no'.

    And you are trying to create a FAST REFRESH MV that uses an aggregate function in the WHERE clause.

    See the Restrictions section in the doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#sthref205
  • 2. Re: Materialized View - Aggregate Functions - Fast Refreshes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Have you reviewed "Restrictions on Fast Refresh on Materialized Views with Aggregates"

    at http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007028



    Hemant K Chitale
  • 3. Re: Materialized View - Aggregate Functions - Fast Refreshes
    874144 Newbie
    Currently Being Moderated
    According to the documentation (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007028) it would appear that fast refreshes and aggregate functions are able to work together?

    Perhaps I should rephrase my question - Given the above problem - Does anyone have a solution, and if so, would you mind sharing it please?
  • 4. Re: Materialized View - Aggregate Functions - Fast Refreshes
    rp0428 Guru
    Currently Being Moderated
    Did you correct the colum order as I mentioned?
  • 5. Re: Materialized View - Aggregate Functions - Fast Refreshes
    874144 Newbie
    Currently Being Moderated
    Executing:

    CREATE MATERIALIZED VIEW LOG ON job_statuses
    WITH SEQUENCE, PRIMARY KEY, ROWID (job_status_no, job_no, status_date, status_id, assigned_to, cost_centre_no, note_no, modified_by)
         INCLUDING NEW VALUES;

    Results in error - ORA-12026: invalid filter column detected

    Removing the 'PRIMARY KEY' clause results in successful creation of the MV_LOG.

    Regardless - creation of the main MV still fails. As I have said, I have tried writing the query several different ways and cannot see that I am breaking any of the specified regulations with regards to aggregate functions & materialized views. If someone could be so kind as to highlight the specific rule, or even better, provide a suggested alternate solution - it would be much appreciated.

    Cheers
    Brad
  • 6. Re: Materialized View - Aggregate Functions - Fast Refreshes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    From the documentation :
    COUNT(*) must be specified.
    Have you included a count ?


    Hemant K Chitale
  • 7. Re: Materialized View - Aggregate Functions - Fast Refreshes
    874144 Newbie
    Currently Being Moderated
    Where should COUNT(*) be specified? I have tried using it in the main SELECT clause, and in the sub-query - both variants make no difference.

    I have tried creating a MV on just the JOB_NO and JOB_STATUS_NO, and then incorporating this MV into the main MV - doing an inner join on these columns. Still results in an error.

    Surely I'm not the only person to have encountered this problem? What are other people doing to ascertain the last record in a series, and incorporate that into an MV?
  • 8. Re: Materialized View - Aggregate Functions - Fast Refreshes
    874144 Newbie
    Currently Being Moderated
    I am closing this thread, as I have found an acceptable solution to my problem. Thanks for everyone's input. For those who may be interested, the solution I have followed is:

    CREATE MATERIALIZED VIEW LOG ON job_statuses
    WITH SEQUENCE, ROWID (job_status_no, job_no)
         INCLUDING NEW VALUES;

    CREATE MATERIALIZED VIEW max_job_statuses
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS
    SELECT job_no, max(job_status_no) job_status_no
    FROM job_statuses
    GROUP BY job_no;

    CREATE OR REPLACE FORCE VIEW "CURRENT_JOB_STATUS" ("JOB_STATUS_NO", "JOB_NO", "STATUS_DATE", "STATUS_ID", "ASSIGNED_TO", "COST_CENTRE_NO", "NOTE_NO", "MODIFIED_BY") AS
    SELECT /*+ RULE */
    js.job_status_no,
    js.job_no,
    js.status_date,
    js.status_id,
    js.assigned_to,
    js.cost_centre_no,
    js.note_no,
    js.modified_by
    FROM job_statuses js,
    max_job_statuses x
    WHERE js.job_no = x.job_no
    AND js.job_status_no= x.job_status_no;

    The performance is comparable - which was the primary motive behind exploring the MV option, and the fast refresh is still incorporated.

    Thanks All.
    Brad
  • 9. Re: Materialized View - Aggregate Functions - Fast Refreshes
    874144 Newbie
    Currently Being Moderated
    Solution found.
  • 10. Re: Materialized View - Aggregate Functions - Fast Refreshes
    user1983440 Explorer
    Currently Being Moderated
    For what it's worth...I was about to post something similar.
    CREATE OR REPLACE FORCE VIEW "CURRENT_JOB_STATUS" ("JOB_STATUS_NO", "JOB_NO", "STATUS_DATE", "STATUS_ID", "ASSIGNED_TO", "COST_CENTRE_NO", "NOTE_NO", "MODIFIED_BY") AS 
    WITH x AS (
      SELECT job_no,
             MAX(job_status_no) max_job_status_no
        FROM job_status
        GROUP BY job_no
    )
    SELECT 
    js.job_status_no,
    js.job_no,
    js.status_date,
    js.status_id,
    js.assigned_to,
    js.cost_centre_no,
    js.note_no,
    js.modified_by
    FROM job_statuses js
    WHERE js.job_no = x.job_no
    AND js.job_status_no = x.max_job_status_no
    /
    Only potential advantage I can see over the solution you posted is that it doesn't require the 2nd mview. That being said, your solution may yield better performance. You could also try using the /*+MATERIALIZE*/ hint w/the SELECT in the WITH clause to help performance.
    WITH x AS (
      SELECT /*+MATERIALIZE*/ job_no,
             MAX(job_status_no) max_job_status_no
        FROM job_status
        GROUP BY job_no
    )
  • 11. Re: Materialized View - Aggregate Functions - Fast Refreshes
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Good. A view on an MV -- like a Nested MV.


    Hemant K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points