Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011

    Unanswered: Creating self-updating views with data selection


    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. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Materialized view was my first thought.

    However, not "SELECT *" but "SELECT id, name, address, salary".

    Source tables might change, occasionally. Not too often. Because, if they do, your data model is kind of defective. Therefore, naming all columns in materialized view's SELECT statement should not be a major problem.

  3. #3
    Join Date
    Aug 2011
    But this is the very problem! It is not up to me whether the Source tables columns change or not, which is why I try to find a solution to how to keep them updated. This is why I can not just type all the columns names in SELECT.

Posting Permissions

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