1 2 Previous Next 16 Replies Latest reply: Dec 31, 2012 4:08 AM by jeneesh RSS

    Materialized view and Table

    kumar73
      Dear Friends,

      One quick question ..

      If the table name and materialized view name is same does truncating the data from table truncates materialized view too ?

      Thanks/
        • 1. Re: Materialized view and Table
          Purvesh K
          kumar73 wrote:
          Dear Friends,

          One quick question ..

          If the table name and materialized view name is same does truncating the data from table truncates materialized view too ?

          Thanks/
          If the Materialized View is built on the table, and the Table is truncated the data will continue to reside in the MView as long as it is not refreshed.

          Once you refresh the MView, the data shall vanish.

          Having MView and Table Names same, does not affect.
          • 2. Re: Materialized view and Table
            ranit B
            kumar73 wrote:
            Dear Friends,

            One quick question ..

            If the table name and materialized view name is same does truncating the data from table truncates materialized view too ?

            Thanks/
            Table and Materialized View are 2 different objects in database.
            Both have their separate data storage areas, so truncating the table alone_ wont affect the MView data.

            But, when you refresh the MView the latest data from table is pulled into it.

            And Yes , you can have have a common name for both a MView and its base table.
            If you check DBA_OBJECTS properly, there are 2 separate columns - OBJECT_NAME and OBJECT_TYPE

            The latter col distinguishes between them, even though the former col is same.

            Hope this Helps.
            Ranit B.

            Edited by: ranit B on Dec 31, 2012 11:38 AM
            • 3. Re: Materialized view and Table
              Manik
              I m just curious to know, any specific reason that you have same name for table and MV?

              Cheers,
              Manik.
              • 4. Re: Materialized view and Table
                jeneesh
                kumar73 wrote:
                Dear Friends,

                One quick question ..

                If the table name and materialized view name is same does truncating the data from table truncates materialized view too ?
                Are you able to create MVIEW in same name as a table in same schema?
                create materialized view emp as 
                select *
                from emp;
                
                SQL Error: ORA-00955: name is already used by an existing object
                00955. 00000 -  "name is already used by an existing object"
                *Cause:    
                *Action:
                • 5. Re: Materialized view and Table
                  kumar73
                  So, how we can avoid truncating the data from MV if the mv is based on table.

                  Becoz when I issued the command

                  TRUNCATE TABLE ABC ;

                  It also truncated the MV XYZ which is created based on base table ABC . How to truncate the table without truncating the data from MV in this case ?

                  thanks
                  • 6. Re: Materialized view and Table
                    kumar73
                    Yes I do .
                    • 7. Re: Materialized view and Table
                      ranit B
                      kumar73 wrote:
                      So, how we can avoid truncating the data from MV if the mv is based on table.

                      Becoz when I issued the command

                      TRUNCATE TABLE ABC ;

                      It also truncated the MV XYZ which is created based on base table ABC . How to truncate the table without truncating the data from MV in this case ?
                      I guess_ , this is not true. There must be something more to it.
                      Please check your MView query, it might have Automatic Refresh enabled on Commit.

                      Since Truncate is a DDL and accompanied by implicit Commit , the MView would have got refreshed showing the updated table data.

                      Please post your MView query.
                      • 8. Re: Materialized view and Table
                        Manik
                        Post your DDL
                        SELECT * FROM emp;
                        
                        
                        EID     ENAME     DID
                        1     B     2
                        1     B     2
                        1     B     2
                        1     B     2
                        1     B     2
                        1     B     2
                        3     C     2
                        
                        CREATE MATERIALIZED VIEW mv_emp AS SELECT * FROM emp;
                        
                        TRUNCATE TABLE emp;
                        
                        SELECT * FROM mv_emp;
                        
                        EID     ENAME     DID
                        1     B     2
                        1     B     2
                        1     B     2
                        1     B     2
                        1     B     2
                        1     B     2
                        3     C     2
                        For me the data still exists in MV.

                        May be you have automatic refresh.


                        Cheers,
                        Manik.
                        • 9. Re: Materialized view and Table
                          jeneesh
                          kumar73 wrote:
                          It also truncated the MV XYZ which is created based on base table ABC . How to truncate the table without truncating the data from MV in this case ?
                          Post your MVIEW creation script..

                          help us to help you..
                          • 10. Re: Materialized view and Table
                            kumar73
                            CREATE TABLE "ABBREVIATION"
                            (
                            "ABBREVIATIONID" VARCHAR2(36 BYTE),
                            "ABBREVIATIONCODE" VARCHAR2(30 BYTE),
                            "ABBREVIATIONTEXT" VARCHAR2(128 BYTE),
                            "CREATIONDATE" DATE,
                            "CREATEDBYLOGINID" VARCHAR2(36 BYTE),
                            "LASTMODIFIEDDATE" DATE,
                            "LASTMODIFIEDBYLOGINID" VARCHAR2(36 BYTE),
                            CONSTRAINT "PK_ABBREVIATION1" PRIMARY KEY ("ABBREVIATIONID") TABLESPACE "KUMAR" ENABLE
                            )

                            CREATE MATERIALIZED VIEW "ABBREVIATION" ("ABBREVIATIONID", "ABBREVIATIONCODE", "ABBREVIATIONTEXT", "CREATIONDATE", "CREATEDBYLOGINID", "LASTMODIFIEDDATE", "LASTMODIFIEDBYLOGINID") TABLESPACE "KUMAR" NEVER REFRESH
                            AS
                            SELECT "ABBREVIATION"."ABBREVIATIONID" "ABBREVIATIONID",
                            "ABBREVIATION"."ABBREVIATIONCODE" "ABBREVIATIONCODE",
                            "ABBREVIATION"."ABBREVIATIONTEXT" "ABBREVIATIONTEXT",
                            "ABBREVIATION"."CREATIONDATE" "CREATIONDATE",
                            "ABBREVIATION"."CREATEDBYLOGINID" "CREATEDBYLOGINID",
                            "ABBREVIATION"."LASTMODIFIEDDATE" "LASTMODIFIEDDATE",
                            "ABBREVIATION"."LASTMODIFIEDBYLOGINID" "LASTMODIFIEDBYLOGINID"
                            FROM "ABBREVIATION" "ABBREVIATION";


                            If I create a mv with a differnent name than that of a table does the truncation of data in table results in truncation of MV too ?
                            • 11. Re: Materialized view and Table
                              ranit B
                              Hey...

                              Your MView has got 'NEVER REFRESH' which doesn't allow Explicit Refresh (will raise ORA Error).

                              So, then who is updating?? Is there any Trigger present?

                              refer - http://www.sqlsnippets.com/en/topic-12888.html

                              Please post your Database version. (Just a guess, are you on 11g database? )
                              • 12. Re: Materialized view and Table
                                Purvesh K
                                With Never Refresh included in your MView creation, I do not think you should have any problem of data getting refreshed, unless someone, unknowingly, converting the MView to Refresh.

                                NEVER REFRESH Clause

                                Specify NEVER REFRESH to prevent the materialized view from being refreshed with any Oracle Database refresh mechanism or packaged procedure. Oracle Database will ignore any REFRESH statement on the materialized view issued from such a procedure. To reverse this clause, you must issue an ALTER MATERIALIZED VIEW ... REFRESH statement


                                Additionally, you can also check the results of query, and post it here:
                                select owner, mview_name, refresh_mode, refresh_method
                                  from all_mviews
                                where mview_name = 'ABBREVIATION';
                                Edited by: Purvesh K on Dec 31, 2012 12:34 PM
                                Added query for verification
                                • 13. Re: Materialized view and Table
                                  Manik
                                  You have to understand its nothing to do with name there.
                                  Until and unless you refresh Mat view the data wont be truncated in your scenario.(after table truncation).

                                  Cheers,
                                  Manik.
                                  • 14. Re: Materialized view and Table
                                    Oracle Maniac
                                    Hi ,


                                    Is it the namespace that distinguishes the names of the objects which can be similar or the object type of dba objects ? I suppose its the namespaces and its the same for tables ,views and MVs . Just a thought as i have never worked with Materialized Views .
                                    1 2 Previous Next