Re: Critical Qs on materialized views
- From: Jared Still <jkstill@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Sat, 27 Aug 2005 01:34:26 +0100
Hi Tim,
Interesting, I wish I had known that last week. :)
I'm in the midst of rebuilding a rather large table to claim
back ~200g of space from it.
The table in question has a unique index, but no PK.
The workaround was easy: add a primary key constraint to the table,
a 2 second operation with a unique index in place.
I think it would be interesting to rerun the prototype though without
the PK and with QUERY_REWRITE_INTEGRITY = STALE_TOLERATED
Thanks,
Jared
On 8/26/05, Tim Gorman <tim@xxxxxxxxx> wrote:
>
> 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@xxxxxxxxxxx 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@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Day
> > Sent: Thursday, August 25, 2005 10:10 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > 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.
> > --
> > //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***
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
