FAQ
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

Search Discussions

  • Oracle-l-bounce_at_freelists.org at Aug 26, 2005 at 7:25 am
    We need to create the Materlialized view on PRE-Built Table WITHOUT
    adding a Constraint to the Master/source Table. This is needed because
    the Production (LIVE) Database(Master/Source) has NO existing
    constraints & creating the same on the large 600 GB production
    Database's tables would be nearly impossible.

    NOTE - We are getting the following Errors when attempting to create a
    Materlialized view on PRE-Built Table WITHOUT adding a Constraint to the

    Master/source Table:-

    SQL> create materialized view d1 BUILD IMMEDIATE
    2 refresh complete as select * from d1_at_S829mig_teja;
    refresh complete as select * from d1_at_S829mig_teja
    *
    ERROR at line 2:
    ORA-12014: table 'D1' does not contain a primary key constraint
    SQL> create materialized view d1 BUILD IMMEDIATE refresh complete with
    rowid
    2 as select * from d1_at_S829mig_teja;
    as select * from d1_at_S829mig_teja
    *
    ERROR at line 2:
    ORA-00955: name is already used by an existing object
    SQL> create materialized view d1 on prebuilt table refresh complete with
    rowid
    2 as select * from d1_at_S829mig_teja;
    as select * from d1_at_S829mig_teja
    *
    ERROR at line 2:
    ORA-12058: materialized view cannot use prebuilt table

    Give Command with respective syntax, if possible.

    Another basic question - When a constraint is created does it
    automatically create an internal underlying index on the respective
    table?

    Thanks indeed

    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***
  • Tim Gorman at Aug 26, 2005 at 7:30 am
    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
  • Jared Still at Aug 26, 2005 at 7:36 pm
    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 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
    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
  • Jason Heinrich at Aug 26, 2005 at 8:39 am
    When you create your materialized view use the "with rowid" phrase to base
    fast refreshes off of the master table's rowids rather than its primary key
    (which you've already stated doesn't exist):

    create materialized view mv
    refresh force with rowid as
    select * from master_table;

    Of course, to do fast refreshes you'll still need to create materialized
    view logs on the master tables to record changes. You'll need to specify
    "with rowid" when you create these as well:

    create materialized view log on master_table with rowid;

    I hope this is what you were asking for.
    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.
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
    (850) 478-8496 x2509
    jheinrich_at_pcci.edu
  • Jared Still at Aug 26, 2005 at 7:30 pm
    You cannot use 'WITH ROWID' on a MV based on a prebuilt table.

    The prebuilt table requirement was stated in a followup email.

    Jared
    On 8/26/05, Jason Heinrich wrote:

    When you create your materialized view use the "with rowid" phrase to base
    fast refreshes off of the master table's rowids rather than its primary
    key
    (which you've already stated doesn't exist):

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

    --
    http://www.freelists.org/webpage/oracle-l
  • VIVEK_SHARMA at Aug 29, 2005 at 3:05 am
    Jared, Folks

    Qs 1 Can the PK constraint be created on a PRE-Created (already existing) Unique index in ver 8i too?

    Qs 2 Like an index can be created in parallel with NOLOGGING, ONLINE Clauses can the Primary Key Constraint also be created with such clauses on a Table having NO indexes? If NOT then would it NOT be advantageous to 1st Precreate the unqiue index & then add the PK constraint?

    ops$tkyte_at_ORA9IR2> create table t ( x int, y int );

    Table created.

    ops$tkyte_at_ORA9IR2> create unique index t_idx on t(x) online;

    Index created.

    ops$tkyte_at_ORA9IR2> alter table t add constraint t_pk primary key(x) rely
    novalidate;

    Qs 3 What is the significance of the above "rely novalidate" clauses?

    Taken from the following GOOD Link on PK vs index:-

    http://asktom.oracle.com/pls/ask/f?p=4950:8:15663747642085018977::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4941517587762

    Thanks indeed

    From: oracle-l-bounce_at_freelists.org On Behalf Of Jared Still
    Sent: Saturday, August 27, 2005 6:04 AM
    To: tim_at_evdbt.com
    Cc: Oracle-L
    Subject: 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 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.
    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***
  • Jared Still at Aug 30, 2005 at 2:48 pm
    comments inline
    On 8/29/05, VIVEK_SHARMA wrote:



    Jared, Folks

    Qs 1 Can the PK constraint be created on a PRE-Created (already existing)
    Unique index in ver 8i too?
    Check the docs. I don't think this option existed in 8i. You won't know for
    sure until you check the docs
    and/or try it.

    Qs 2 Like an index can be created in parallel with NOLOGGING, ONLINE Clauses
    can the Primary Key Constraint also be created with such clauses on a Table
    having NO indexes? If NOT then would it NOT be advantageous to 1st Precreate
    the unqiue index & then add the PK constraint?
    You can build a PK by that method, but it doesn't seem to work for MV's.

    Here's the scenario I've been working with. I need to rebuild a rather large
    table with little or no downtime.

    The chosen method is via MV.

    Updates to the source table must be preserved so that they can later be
    applied to the MV table.

    You *could* pre-create the table, then create the PK, and finally create
    the MV on it with pre-built table.

    When you do this though, the rows in MLOG$_ will
    be deleted without being applied to the MV. Correcting this will require a
    complete
    refresh, which will take as long as building the table in the first place.

    The following test demonstrates that:

    12:41:33 SQL>create table m1( pk number(4) not null, name varchar2(20) not
    null);

    Table created.

    12:41:33 SQL>

    12:41:33 SQL>create index m1_idx on m1(pk) nologging;

    Index created.

    12:41:33 SQL>alter table m1 add constraint m1_pk primary key(pk);

    Table altered.

    12:41:33 SQL>

    12:41:33 SQL>insert into m1 values(1,'Row 1');

    1 row created.

    12:41:33 SQL>commit;

    Commit complete.

    12:41:33 SQL>
    12:41:33 SQL>create table m2
    12:41:33 2 nologging
    12:41:33 3 as
    12:41:33 4 select *
    12:41:33 5 from m1
    12:41:33 6 /

    Table created.

    12:41:33 SQL>

    12:41:33 SQL>create index m2_idx on m2(pk) nologging;

    Index created.

    12:41:33 SQL>alter table m2 add constraint m2_pk primary key(pk);

    Table altered.

    12:41:33 SQL>

    12:41:33 SQL>create materialized view log on m1;

    Materialized view log created.

    12:41:33 SQL>

    12:41:33 SQL>insert into m1 values(2,'Row 2');

    1 row created.

    12:41:33 SQL>commit;

    Commit complete.

    12:41:33 SQL>
    12:41:33 SQL>
    12:41:33 SQL>create materialized view m2
    12:41:33 2 on prebuilt table
    12:41:33 3 refresh fast
    12:41:33 4 as
    12:41:33 5 select *
    12:41:33 6 from m1
    12:41:33 7 /

    Materialized view created.

    12:41:33 SQL>

    12:41:33 SQL>exec dbms_mview.refresh('M2','FAST')

    PL/SQL procedure successfully completed.

    12:41:33 SQL>
    12:41:33 SQL>
    12:41:33 SQL>select * from m1;

    PK NAME

    ---------- --------------------

    1 Row 1
    2 Row 2

    2 rows selected.

    12:41:33 SQL>select * from m2;

    PK NAME

    ---------- --------------------

    1 Row 1

    1 row selected.

    12:41:33 SQL>

    12:41:33 SQL>select * from mlog$_m1;

    no rows selected

    12:41:33 SQL>

    12:41:33 SQL>set echo off

    I've found that something similar will happen if you create the materialized
    view with 'never refresh', alter it
    to 'refresh fast' and then try to do a fast refresh. The difference is an
    error is generated.

    12:46:30 SQL>create materialized view m2
    12:46:30 2 on prebuilt table
    12:46:30 3 never refresh
    12:46:30 4 as
    12:46:30 5 select *
    12:46:30 6 from m1
    12:46:30 7 /

    Materialized view created.

    12:46:30 SQL>

    12:46:30 SQL>alter materialized view m2 refresh fast;

    Materialized view altered.

    12:46:30 SQL>

    12:46:30 SQL>exec dbms_mview.refresh('M2','FAST')
    BEGIN dbms_mview.refresh('M2','FAST'); END;

    *
    ERROR at line 1:
    ORA-12057: materialized view "JS001292"."M2" is INVALID and must complete
    refresh

    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
    ORA-06512: at line 1

    In both cases a complete refresh is required.

    To work with what MV creation will allow (at least to the best of my
    knowledge at this time)
    the target table is created empty with a primary key constraint.

    A complete refresh is then done, and the MV altered to do periodic fast
    refreshes.

    Other indexes are then created as needed.

    I haven't yet tried any of Tim's suggestions, but may if I have time.

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Aug 31, 2005 at 9:44 am
    D'oh!

    Of course it works. I misunderstood that question. Reading too
    fast, too much going on.

    I saw 'PRE' and thought he was still talking about PREbuilt table.

    That has worked for as long as I can recall, sometime in Version 7, maybe
    from the beginning.

    Jared
    On 8/31/05, Phil Jones wrote:
    On 30/08/05, Jared Still wrote:
    comments inline

    On 8/29/05, VIVEK_SHARMA wrote:


    Jared, Folks

    Qs 1 Can the PK constraint be created on a PRE-Created (already
    existing)
    Unique index in ver 8i too?

    Check the docs. I don't think this option existed in 8i. You won't know
    for sure until you check the docs
    and/or try it.
    Looks like it works ok:

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - 64bit Production

    SQL> create table pktest ( i int,j int );

    Table created.

    SQL> create unique index pktest_pk on pktest ( i, j );

    Index created.

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 26, '05 at 6:52a
activeAug 31, '05 at 9:44a
posts9
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase