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@(protected)> 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@(protected) 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@(protected) > > [mailto:oracle-l-bounce@(protected)] On Behalf Of Thomas Day > > Sent: Thursday, August 25, 2005 10:10 PM > > To: oracle-l@(protected) > > 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
Hi Tim,<br> <br> Interesting, I wish I had known that last week. :)<br> <br> I'm in the midst of rebuilding a rather large table to claim<br> back ~200g of space from it. <br> <br> The table in question has a unique index, but no PK.<br><br> The workaround was easy: add a primary key constraint to the table,<br> a 2 second operation with a unique index in place.<br> <br> I think it would be interesting to rerun the prototype though without<br> the PK and with QUERY_REWRITE_INTEGRITY = STALE_TOLERATED<br> <br> Thanks,<br> <br> Jared<br> <br><div><span class="gmail_quote">On 8/26/05, <b class="gmail_sendername">Tim Gorman</b> <<a href="mailto:tim@(protected)">tim@(protected)</a>> wrote:< /span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204 , 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> ENFORCED constraints are used by MV refresh only for the purpose of guarding<br >against "staleness" as specified by the default setting of the parameter<br>QUERY_REWRITE_INTEGRITY to the value of "ENFORCED". <br><br>Setting QUERY_REWRITE_INTEGRITY to the value of "STALE_TOLERATED " (i.e.<br>Functionality disabled) will do what you want. Just be sure to understand<br>the implications of this setting (i.e. DBA becomes responsible for ensuring <br>that MV is never "stale" for query rewrite).<br><br><br><br>on 8 /26/05 5:17 AM, VIVEK_SHARMA at <a href="mailto:VIVEK_SHARMA@(protected)">VIVEK _SHARMA@(protected)</a> wrote:<br><br>><br>> Thomas, folks <br>><br>> Is there some way of Avoiding creation of Constraint on Source /Master<br>> table while using Materlialized view refresh?<br>> NOTE - Production Database does NOT have any constraints & is of 600 GB <br>><br>> Creation of constraints on Source/Master DB Tables would be very time<br>> consuming, if attempted.<br>><br>> Thanks indeed for the info.<br>><br>><br>> -- --Original Message-- --<br>> From: <a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)</a ><br>> [mailto:<a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce @(protected)</a>] On Behalf Of Thomas Day<br>> Sent: Thursday, August 25, 2005 10:10 PM <br>> To: <a href="mailto:oracle-l@(protected)">oracle-l@(protected)</a> <br>> Subject: Re: Some Basic Qs on materialized views<br>><br>> It's not the size of the database that determines how often you<br>> refresh the materialized views. Rather, it's the frequecy with which <br>> the data changes on the source database and how important it is to the <br>> business that that data is reflected in the target database in a<br>> ; timely fashion.<br>><br>> We used 15 minutes for transaction oriented tables, 1 hour for tables <br>> that were less volatile and 24 hours for look-up tables (e.g.,<br>> country_codes) where the table's content was only changed via a<br>> software change request.<br>><br>> We used dbms_jobs to schedule and fire the refreshes. <br>> --<br>> <a href="http://www.freelists.org/webpage/oracle-l">http:/ /www.freelists.org/webpage/oracle-l</a><br>><br>> **************** CAUTION - Disclaimer *****************<br>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely <br>> for the use of the addressee(s). If you are not the intended recipient , please<br>> notify the sender by e-mail and delete the original message. Further, you are<br>> not to copy, disclose, or distribute this e-mail or its contents to any other <br>> person and any such actions are unlawful. This e-mail may contain viruses.<br>> Infosys has taken every reasonable precaution to minimize this risk, but is<br>> not liable for any damage you may sustain as a result of any virus in this <br>> e-mail. You should carry out your own virus checks before opening the e-mail<br>> or attachment. Infosys reserves the right to monitor and review the content of<br>> all messages sent to or from this e-mail address. Messages sent to or from <br>> this e-mail address may be stored on the Infosys e-mail system.<br>> ; ***INFOSYS******** End of Disclaimer ********INFOSYS***<br>> --<br>> <a href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org /webpage/oracle-l </a><br>><br><br>--<br><a href="http://www.freelists.org/webpage/oracle-l" >http://www.freelists.org/webpage/oracle-l</a><br></blockquote></div><br><br clear="all"><br>-- <br>Jared Still<br>Certifiable Oracle DBA and Part Time Perl Evangelist <br><br>
