Re: Critical Qs on materialized views

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_at_evdbt.com> 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_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
> > [mailto: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
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l