Fast Refresh using two non-primary key tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Fast Refresh using two non-primary key tables

  1. #1
    Join Date
    Jun 2002
    Posts
    16

    Fast Refresh using two non-primary key tables

    Hi,

    I have a materialized view based on two tables with an outer join clause. Both the tables do not have a primary key so I had created materialized view log with row-id on each of them but still I am not able to bring out the fast refresh option for the materialized view. My question is can I have a fast refresh option for materialized view built from two tables without primary keys and having an outer join clause????. If possible please send me some sample scripts for quicker understanding.

    Thanks and Regards,
    Sudhakar

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    I believe that the fast refresh option relies on having a primary key.
    Why don't you have a primary key on those tables?
    this space intentionally left blank

  3. #3
    Join Date
    Jun 2002
    Posts
    16
    I cannot have primary keys on the tables since those are proprietary tables of another application. Are they any workarounds?

    Rgds,
    Sudhakar

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by rksudhakar
    I have a materialized view based on two tables with an outer join clause
    "If the WHERE clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table" (1)

    RTFM

    (1) Metalink; Note:222843.1
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by rksudhakar
    I cannot have primary keys on the tables since those are proprietary tables of another application. Are they any workarounds?
    W/o PK or Unique Key means they are temporary tables. Am I right?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by tamilselvan
    W/o PK or Unique Key means they are temporary tables. Am I right?

    Heh heh heh ... troll
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by slimdave
    Heh heh heh ... troll
    Hmm.. I am not surprised by your answer. May be I need to write the fundamental definition of normalization process.

    The precursor definition in the normalization process is, all entities are uniquely identifable by a combination of attributes and / or relationships.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width