1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Creating self-updating views with data selection

Discussion in 'SQL PL/SQL' started by nitrum, Aug 3, 2011.

  1. nitrum

    nitrum Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi!
    The topic probably does not say much of what I need so I'll try to explain:
    • I've got a Source Data in complex relational form
    • For reporting purposes, a simpler, less normalized data model is needed
    • There are two Target views from the Source Data:
      • one of them with full access to all data
      • the second one with access only to a subset of the data (same columns, but not all the records)
    • For both target groups, a separate schema shall be available, each containing only relevant data
    • Today, these schemas are physically located on the same DB instance and host as the source data
    • A daily refresh is sufficient
    • A later relocation of the reporting schemes to other DB instances shall be possible without major changes needed
    • Oracle 10g should be used
    I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem:
    when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.

    I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).

    If you had any solutions or even ideas - I'd definately appreciate!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Data Guard replicates an entire database either physically or logically so you'd get far more data than you'd need for this task. Streams/Golden Gate is a possible solution if you couple that with Virtual Private Database which allows you to restrict the data various users can see and allows you to tailor the data sets accordingly [one user can see all of the data, another can see only the first five columns and another user can see eight columns of data (as examples)]. Materialized views need to be carefully thought out as materialized view logs may need to be created for fast refresh operations and the refresh mechanism for MVs uses a database link where Streams uses log mining to transfer transactions to the destination which is a far more efficient method.
     
  3. nitrum

    nitrum Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    As far as I understand, when using a VPN there is a remote server with all data copied and VPN just restricts access to certain data for certain users. The problem is that this server will probably be in another country and even if the confidential information is not accessable it is still there physically - and that is what I want to avoid. It would be better to send the required information only, but still - it would have to be refreshed every day. For the Streams it is a real-time update and it takes some bandwidth all the time - even if it's more efficient, I think a complete refresh during night would be enough.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A complete refresh of two materialized views also takes bandwidth, uses a database link and is a bit less efficient in terms of transport. Had you provided the information from your last post in the original problem description some suggestions would not have been made; please make every attempt to supply complete information as our answers are only as good as your description of the situation. I have had MV refreshes fail requiring a complete rebuild of the MVs to restore functionality so make note of that.
     
  5. nitrum

    nitrum Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Would you mind if I posted my conversation with someone from another forum..? It is not too long but it's quite factual, with good problem description I think. They were not able to help me though.

    oracle-base.com/forums/viewtopic.php?f=1&t=12845&p=33488
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I know Tim and he's given you excellent information; why is this recommendation not usable? You want materialized views and he's provided the same information I would have had this conversation gone further. The fast refresh, if it is an option, can significantly reduce the network traffic for the refreshes however it does require materialized view logs based on either a primary key or ROWID for the base table or tables. These logs record changed record information so a fast refresh can be performed. Provided you create your MVs with explicit column lists rather than the lazy 'select * ...' if the base tables have columns added the refresh won't break; note however that if column definitions change or columns are dropped the MVs will need to be rebuilt.

    Provide more information so we can assist you since I can't understand why the recommendations you've recieved are not satisfactory. You're not providing all of the necessary information.
     
  7. nitrum

    nitrum Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I think you dispelled all my doubts. Thank you very much for your help!