9 Replies Latest reply: Aug 13, 2007 1:31 PM by 488972 RSS

    Materialized View:  change select statement

    488972
      Is it possible to change the where-clause of a select statement, in a Materialized View, without dropping & recreating the MV ?

      create materialized view myview
      as
      select *
      from mytable
      where mycol in ('a','b','c')

      Now I want more values to be included, eg.

      where mycol in ('a','b','c','d','f')
        • 1. Re: Materialized View:  change select statement
          577207
          CREATE MATERIALIZED VIEW emp_data 
             PCTFREE 5 PCTUSED 60
             TABLESPACE example
             STORAGE (INITIAL 50K NEXT 50K)
             REFRESH FAST NEXT sysdate + 7
             AS SELECT * FROM employees;
          ALTER MATERIALIZED VIEW emp_data
             REFRESH COMPLETE  
             START WITH TRUNC(SYSDATE+1) + 9/24 
             NEXT SYSDATE+7;
          Additionally you can visit here:

          ALTER MATERIALIZED VIEW
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2001.htm#CCHECCJB

          Adith
          • 2. Re: Materialized View:  change select statement
            488972
            From what I understand from this page, it does not look like it is possible to change the "select" part of the view - unless I understand it wrong -

            "Use the ALTER MATERIALIZED VIEW statement to modify an existing materialized view in one or more of the following ways:

            * To change its storage characteristics
            * To change its refresh method, mode, or time
            * To alter its structure so that it is a different type of materialized view
            * To enable or disable query rewrite"
            • 3. Re: Materialized View:  change select statement
              465815
              The OP asked for something else:
              in future, you can use the PREBUILT option - just create the same table before it:
              Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
              SQL>
              SQL> rem exmple is the data source:
              SQL> drop table exmple;

              Table dropped

              SQL> create table exmple as select * from all_objects;

              Table created

              SQL> drop table PREBUILT_TEST;

              Table dropped

              SQL> CREATE TABLE PREBUILT_TEST AS SELECT object_name, object_id FROM exmple WHERE ROWNUM <=10;

              Table created

              SQL> CREATE SNAPSHOT PREBUILT_TEST ON PREBUILT TABLE AS SELECT object_name, object_id FROM exmple WHERE ROWNUM <=10;

              Materialized view created

              SQL> rem show the dependent object wont go invalid
              SQL> CREATE OR REPLACE FUNCTION F_PREBUILT_TEST RETURN SYS_REFCURSOR IS
                2  CUR SYS_REFCURSOR;
                3  BEGIN
                4       OPEN CUR FOR SELECT * FROM PREBUILT_TEST;
                5  RETURN  CUR;
                6  END;
                7  /

              Function created

              SQL> select status from all_objects where object_name in ('F_PREBUILT_TEST');

              STATUS
              -------
              VALID

              SQL> drop SNAPSHOT PREBUILT_TEST;

              Materialized view dropped

              SQL> drop table exmple2;

              Table dropped

              SQL> rem exmple2 is some, other datasource:
              SQL> rem change the from and the where clause for the snapshot:
              SQL> create table exmple2 as select object_name, object_id from exmple;

              Table created

              SQL> CREATE SNAPSHOT PREBUILT_TEST ON PREBUILT TABLE AS SELECT object_name, object_id FROM exmple2 WHERE ROWNUM <=100 and object_id > 100;

              Materialized view created

              SQL> select status  from all_objects where object_name in ('F_PREBUILT_TEST');

              STATUS
              -------
              VALID

              SQL>
              p.s.
              I have changed the from part and the where part.
              the column definition must be the same.
              Amiel Davis
              • 4. Re: Materialized View:  change select statement
                577207
                Thank you Amiel D. I realized.

                Adith
                • 5. Re: Materialized View:  change select statement
                  465815
                  It worked for me on 9.2.0.6 and 10.2.0.1.0
                  it will only work if you allready have a table with the same columns as the snapshot.
                  Amiel Davis

                  Message was edited by:
                  Amiel D.
                  • 6. Re: Materialized View:  change select statement
                    3096
                    It sounds as if you need those values in a different table, and the MV query should join the two of them.
                    • 7. Re: Materialized View:  change select statement
                      488972
                      No, not a different table. The source (table), and target (mview), stay the same. But I want to change the where-clause of the select statement, so that from that moment on, it has a different where-clause.
                      The person who requested this, wants to keep the old data that is currently in the materialized view, which means he doesn't want me to drop & recreate the MV.
                      • 8. Re: Materialized View:  change select statement
                        3096
                        Sorry Dirk, I was being sloppy in who I replied. to. I meant to address the comment to the OP.
                        • 9. Re: Materialized View:  change select statement
                          488972
                          No problem