Oracle-L: RE: RE: Suggestions on MV Implementation !!!!!!!

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 24 Jun 2002 06:53:27 -0800
Message-ID: <F001.00485621.20020624065327@fatcity.com>

General Restrictions on Fast Refresh<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

The materialized view's defining query is restricted as follows:

The FROM list must contain base tables only (that is, no views).
It cannot contain references to non-repeating expressions like SYSDATE and ROWNUM.
It cannot contain references to RAW or LONG RAW data types.
It cannot contain HAVING or CONNECT BY clauses.
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.
It cannot have subqueries, inline views, or set functions like UNION or MINUS.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Defining queries for materialized views with joins only and no aggregates have these restrictions on fast refresh:

All restrictions from "General <http://docs.oracle.com/cd_a87860/doc/server.817/a76994/mv.htm#40960> Restrictions on Fast Refresh" <http://docs.oracle.com/cd_a87860/doc/server.817/a76994/mv.htm#40960> .
They cannot have GROUP BY clauses or aggregates.
If the WHERE clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table.
Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
Materialized views from this category are FAST refreshable after DML or Direct Load to the base tables.

Restrictions on Fast Refresh on Materialized Views with Single-Table Aggregates

Defining queries for materialized views with single-table aggregates have these restrictions on fast refresh:

All restrictions from "General <http://docs.oracle.com/cd_a87860/doc/server.817/a76994/mv.htm#40960> Restrictions on Fast Refresh" <http://docs.oracle.com/cd_a87860/doc/server.817/a76994/mv.htm#40960> .
They can only have a single table.
The SELECT list must contain all GROUP BY columns.
Expressions are allowed in the GROUP BY and SELECT clauses provided they are the same.
They cannot have a WHERE clause.
COUNT(*) must be present.
They cannot have a MIN or MAX function.
For a materialized view with an aggregate with a single table, 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.
If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
If VARIANCE(expr) or STDDEV(expr) is specified, you must have COUNT(expr) and SUM(expr).

Restrictions on Fast Refresh on Materialized Views with Joins and Aggregates:

Defining queries for materialized views with joins and aggregates have these restrictions on fast refresh:

All restrictions from "General <http://docs.oracle.com/cd_a87860/doc/server.817/a76994/mv.htm#40960> Restrictions on Fast Refresh" <http://docs.oracle.com/cd_a87860/doc/server.817/a76994/mv.htm#40960> .
The WHERE clause can contain inner equi-joins only (that is, no outer joins)
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.
Materialized views from this category can have only the ON DEMAND option (that is, no ON COMMIT refresh option is allowed).

This is in 8i

Regards,

Waleed

-----Original Message-----
Sent: Friday, June 21, 2002 6:18 PM
To: Multiple recipients of list ORACLE-L

Yeah Waleed, share those notes because I'm not having problems with them. :)

Dick Goulet
Ignorance sometimes is bliss!!

____________________Reply Separator____________________
Author: "Reddy; Madhusudana" <Madhusudana.Reddy_at_bestbuy.com>
Date:       6/21/2002 1:39 PM

Would you share those TONS :)- or any link to show them ..

-----Original Message-----
Sent: Friday, June 21, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L

There are tons of restrictions for fast refresh!

Waleed

-----Original Message-----
Sent: Friday, June 21, 2002 3:33 PM
To: Multiple recipients of list ORACLE-L

Madhu,

Go tell that SrDBA to go read up on MV's. They do support a fast refresh,
but you have to have a log table associated with the base tables in the view.
You can also set it up so that when anyone makes a change to the base tables the
MV gets updated as part of their transaction.

Dick Goulet
Senior Oracle DBA
OCP 8i

____________________Reply Separator____________________
Author: "Reddy; Madhusudana" <Madhusudana.Reddy_at_bestbuy.com>
Date:       6/21/2002 10:58 AM

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
<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
<http://www.orafaq.com> 
--
Author:
  INET: dgoulet_at_vicr.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
<http://www.orafaq.com> 
--
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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
<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
<http://www.orafaq.com> 
--
Author:
  INET: dgoulet_at_vicr.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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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 Mon Jun 24 2002 - 09:53:27 CDT