Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Suggestions on MV Implementation !!!!!!!

RE: Suggestions on MV Implementation !!!!!!!

From: Reddy, Madhusudana <Madhusudana.Reddy_at_bestbuy.com>
Date: Fri, 21 Jun 2002 13:39:09 -0800
Message-ID: <F001.00484954.20020621133909@fatcity.com> 





Thanks Prasad,


I shall read and understand the whole thing



--Madhu



-----Original Message-----


[mailto:Prasada.Gunda1_at_hartfordlife.com]
Sent: Friday, June 21, 2002 2:33 PM


To: Multiple recipients of list ORACLE-L





Hi,


I got the following info on MViews from metalink. It is very good info and
hope it is useful to you.



Best regards,



Problem Description





  You are trying to create a materialized view which refreshes
automatically


  when the underlying table is updated.


  However, the create command fails with an ora-12051 or an ora-12054.
  The select statement that you are using could be any of the following:





  create materialized view log on emp


  with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  including new values


  /





  create materialized view mv_emp_1


  build immediate refresh fast on commit
  as


  select deptno, sum(sal), count(sal)


  from emp


  group by deptno


  /




  from emp


       *


  ERROR at line 5:


  ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view





  or




  create materialized view mv_emp_1


  build immediate refresh fast on commit
  as


  select deptno, sum(sal)


  from emp


  group by deptno


  /





  from emp


       *


  ERROR at line 5:


  ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view




  Solution Description





  The correct script for creation of this materialized view is as follows:




  create materialized view log on emp


  with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  including new values


  /





  create materialized view mv_emp_1


  build immediate refresh fast on commit
  as


  select count(*), deptno, sum(sal), count(sal)
  from emp


  group by deptno


  /







  Explanation





  One of the mandatory requirements for creation of an on-commit
materialized


  view has not been satisfied.



  ON-COMMIT :



  Refresh occurs automatically when a transaction that modified one of the
  materialized view's fact tables commits.
  Can be used with materialized views on single table aggregates
  and with materialized views containing joins only.




  As can be seen from above, an ON-COMMIT can be used only under specific
cases:


  These are:



    
  
  1.   The M.V. should have a single table aggregate or
  
  2.   the M.V. should have a join only.
  
  3.   count(*) must be present for Single-Table Aggregates (see example
above).
  
  4.   count(<col>) should be present.
  Here, <col> stands for the column which is being aggregated.
  Note: the only time that count(col)is not required is when
  the aggregate itself is a count(col).
  
  5.   It should be possible perform a fast refresh on the materialized view.





        Fast refresh by itself has a few restrictions.
        These are as follows:
        a)The FROM list must contain base tables only (that is, no views).
        b)It cannot contain references to non-repeating expressions like
          SYSDATE and ROWNUM.
        c)It cannot contain references to RAW or LONG RAW data types.
        d)It cannot contain HAVING or CONNECT BY clauses.
        e)The WHERE clause can contain only joins and they must be
equi-joins
          (inner or outer) and all join predicates must be connected with
          ANDs. No selection predicates on individual tables are allowed
        f)It cannot have subqueries, inline views, or set functions like
          UNION or MINUS.






  In addition for M.V.'s with Single-Table Aggregates and Materialized
Views


  with Joins and Aggregates, there are some more conditions on refresh
  to the ones mentioned above:


         Single Table Aggregates:
         =======================
           i) They can only have a single table.
          ii) The SELECT list must contain all GROUP BY columns.
         iii) Expressions are allowed in the GROUP BY and SELECT
              clauses provided they are the same.
          iv) They cannot have a WHERE clause.
           v) They cannot have a MIN or MAX function.
          vi) A materialized view log must exist on the table and must
contain all
              columns referenced in the materialized view. The log must
have been
              created with the INCLUDING NEW VALUES clause.
         vii) If AVG(expr) or SUM(expr) is specified, you must have
COUNT(expr).
        viii) If VARIANCE(expr) or STDDEV(expr) is specified,
              you must have COUNT(expr) and SUM(expr).

        Joins and Aggregates :
        =====================

         i)The WHERE clause can contain inner equi-joins only
           (that is, no outer joins)
        ii)Materialized views from this category are FAST refreshable after
           Direct Load to the base tables; they are not FAST refreshable
after
           conventional DML to the base tables.
       iii)Materialized views from this category can have only the
           ON DEMAND option (so, the on-commit cannot be used for this


category).



  References





  Oracle8i Data Warehousing Guide : A76994-01
  [BUG:888784]



  .


 


                    "Reddy, Madhusudana"

                    <Madhusudana.Reddy_at_be       To:     Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    stbuy.com>                  cc:

                    Sent by:                    Subject:     Suggestions on
MV Implementation !!!!!!!                     
                    root_at_fatcity.com

 

 

                    06/21/2002 02:58 PM

                    Please respond to

                    ORACLE-L

 

 









Hello All,



I have a set of Materialized views in my DB . we refresh ( COMPLETE) these
MVs, couple of times a day. Web server ( application ) will hit these MVs
to


show the data on web pages. But the complete Refresh of MVs are consuming
much time and , at this point of time , Application is not able to show
right data on web pages. This is like a down time. I need some suggestions
from you all, in order to minimize or zeroing this down time.



The first thing I can think of is , FAST refresh , but one of my Sr.DBA
told


me that the MV definition will not allow us for a FAST refresh( Are there
any limitations for FAST refresh ???? ). Here is a sample MV Definition :



CREATE MATERIALIZED VIEW GENRELOB



  NOLOGGING



  BUILD IMMEDIATE



  REFRESH COMPLETE ON DEMAND



  DISABLE QUERY REWRITE



AS SELECT DISTINCT



  '1' AS CLIP,



  LOB.LOB_ID,



  LOB.LOB_CD,


  GENRE.GENRE_ID,
  GENRE.GENRE_DESC,
  GENRE.GENRE_DESC AS INSTANCENAME




FROM



  GENRE,



  GENRE_LOB_XREF,



  LOB,



  GENRE_PRODUCT_XREF



WHERE



  GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND

  GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND

  GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND
  GENRE.DSPLY_IND = 'Y'



ORDER BY



  LOB_CD,



  GENRE_DESC



;




My Goal is to view the FRESH data on web pages all the time , irrespective
of MV Refresh. Would anybody suggest me some bright ideas , to have no or
less down time ???



Thanks in advance


Madhu V Reddy



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Prasada.Gunda1_at_hartfordlife.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Fri Jun 21 2002 - 16:39:09 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US