FAQ
What do you mean by "MV is never "stale" for query rewrite"?

Excuse my ignorance.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
On Behalf Of Tim Gorman
Sent: Friday, August 26, 2005 5:59 PM
To: Oracle-L
Subject: Re: Critical Qs on materialized views

ENFORCED constraints are used by MV refresh only for the purpose of
guarding
against "staleness" as specified by the default setting of the parameter
QUERY_REWRITE_INTEGRITY to the value of "ENFORCED".

Setting QUERY_REWRITE_INTEGRITY to the value of "STALE_TOLERATED" (i.e.
Functionality disabled) will do what you want. Just be sure to
understand
the implications of this setting (i.e. DBA becomes responsible for
ensuring
that MV is never "stale" for query rewrite).
on 8/26/05 5:17 AM, VIVEK_SHARMA at VIVEK_SHARMA_at_infosys.com wrote:


Thomas, folks

Is there some way of Avoiding creation of Constraint on Source/Master
table while using Materlialized view refresh?
NOTE - Production Database does NOT have any constraints & is of 600 GB
Creation of constraints on Source/Master DB Tables would be very time
consuming, if attempted.

Thanks indeed for the info.


-----Original Message-----
From: oracle-l-bounce_at_freelists.org
On Behalf Of Thomas Day
Sent: Thursday, August 25, 2005 10:10 PM
To: oracle-l_at_freelists.org
Subject: Re: Some Basic Qs on materialized views

It's not the size of the database that determines how often you
refresh the materialized views. Rather, it's the frequecy with which
the data changes on the source database and how important it is to the
business that that data is reflected in the target database in a
timely fashion.

We used 15 minutes for transaction oriented tables, 1 hour for tables
that were less volatile and 24 hours for look-up tables (e.g.,
country_codes) where the table's content was only changed via a
software change request.

We used dbms_jobs to schedule and fire the refreshes.
--
http://www.freelists.org/webpage/oracle-l

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended
recipient, please
notify the sender by e-mail and delete the original message. Further, you are
not to copy, disclose, or distribute this e-mail or its contents to any other
person and any such actions are unlawful. This e-mail may contain viruses.
Infosys has taken every reasonable precaution to minimize this risk, but is
not liable for any damage you may sustain as a result of any virus in this
e-mail. You should carry out your own virus checks before opening the e-mail
or attachment. Infosys reserves the right to monitor and review the
content of
all messages sent to or from this e-mail address. Messages sent to or from
this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • VIVEK_SHARMA at Aug 29, 2005 at 9:13 am
    Tim, Folks

    Even after setting QUERY_REWRITE_INTEGRITY=STALE_TOLERATED on Both
    Source(8i) & Target(9i) databases, creation of materialized view Failing
    with the following error:-

    SQL> create materialized view T1 on prebuilt table refresh complete
    2 as select * from T1_at_S829mig_teja;
    as select * from T1_at_S829mig_teja

    *
    ERROR at line 2:
    ORA-12014: table 'T1' does not contain a primary key constraint

    SQL> show parameters query (for both databases)

    NAME TYPE VALUE
    ------------------------------------ -----------
    --------------------------
    query_rewrite_enabled string false
    query_rewrite_integrity string STALE_TOLERATED

    NOTE

    Master/Source = Oracle 8.1.7.4 DB
    Slave/Target = 9.2.0 DB

    Am I missing something / related parameter?

    Thanks again

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Tim Gorman
    Sent: Friday, August 26, 2005 5:59 PM
    To: Oracle-L
    Subject: Re: Critical Qs on materialized views

    ENFORCED constraints are used by MV refresh only for the purpose of
    guarding
    against "staleness" as specified by the default setting of the parameter
    QUERY_REWRITE_INTEGRITY to the value of "ENFORCED".

    Setting QUERY_REWRITE_INTEGRITY to the value of "STALE_TOLERATED" (i.e.
    Functionality disabled) will do what you want. Just be sure to
    understand
    the implications of this setting (i.e. DBA becomes responsible for
    ensuring
    that MV is never "stale" for query rewrite).
    on 8/26/05 5:17 AM, VIVEK_SHARMA at VIVEK_SHARMA_at_infosys.com wrote:


    Thomas, folks

    Is there some way of Avoiding creation of Constraint on Source/Master
    table while using Materlialized view refresh?
    NOTE - Production Database does NOT have any constraints & is of 600 GB
    Creation of constraints on Source/Master DB Tables would be very time
    consuming, if attempted.

    Thanks indeed for the info.


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Thomas Day
    Sent: Thursday, August 25, 2005 10:10 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Some Basic Qs on materialized views

    It's not the size of the database that determines how often you
    refresh the materialized views. Rather, it's the frequecy with which
    the data changes on the source database and how important it is to the
    business that that data is reflected in the target database in a
    timely fashion.

    We used 15 minutes for transaction oriented tables, 1 hour for tables
    that were less volatile and 24 hours for look-up tables (e.g.,
    country_codes) where the table's content was only changed via a
    software change request.

    We used dbms_jobs to schedule and fire the refreshes.
    --
    http://www.freelists.org/webpage/oracle-l

    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
    for the use of the addressee(s). If you are not the intended
    recipient, please
    notify the sender by e-mail and delete the original message. Further, you are
    not to copy, disclose, or distribute this e-mail or its contents to any other
    person and any such actions are unlawful. This e-mail may contain viruses.
    Infosys has taken every reasonable precaution to minimize this risk, but is
    not liable for any damage you may sustain as a result of any virus in this
    e-mail. You should carry out your own virus checks before opening the e-mail
    or attachment. Infosys reserves the right to monitor and review the
    content of
    all messages sent to or from this e-mail address. Messages sent to or from
    this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 29, '05 at 3:59a
activeAug 29, '05 at 9:13a
posts2
users1
websiteoracle.com

1 user in discussion

VIVEK_SHARMA: 2 posts

People

Translate

site design / logo © 2022 Grokbase