General Restrictions on
Fast Refresh
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
Restrictions on Fast Refresh".
- 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
Restrictions on Fast Refresh".
- 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
Restrictions on Fast Refresh".
- 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-----
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday,
June 21, 2002 6:18 PM
To: Multiple recipients of list ORACLE-L
Subject:
Re:RE: Suggestions on MV Implementation !!!!!!!
Yeah Waleed, share
those notes because I'm not having problems with them.
:)
Dick
Goulet
Ignorance sometimes is bliss!!
____________________Reply
Separator____________________
Author: "Reddy; Madhusudana"
<[EMAIL PROTECTED]>
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"
<[EMAIL PROTECTED]>
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
--
Author: Reddy,
Madhusudana
INET: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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).
