August 4th, 2011, 03:43 AM
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!