11 Replies Latest reply: Feb 21, 2013 11:18 PM by Hemant K Chitale RSS

    Materialized View - Aggregate Functions - Fast Refreshes

    Brad
      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
          >
          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
            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
              Brad
              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
                Did you correct the colum order as I mentioned?
                • 5. Re: Materialized View - Aggregate Functions - Fast Refreshes
                  Brad
                  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
                    From the documentation :
                    COUNT(*) must be specified.
                    Have you included a count ?


                    Hemant K Chitale
                    • 7. Re: Materialized View - Aggregate Functions - Fast Refreshes
                      Brad
                      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
                        Brad
                        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
                          Brad
                          Solution found.
                          • 10. Re: Materialized View - Aggregate Functions - Fast Refreshes
                            user1983440
                            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
                              Good. A view on an MV -- like a Nested MV.


                              Hemant K Chitale