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!
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.
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.