Oracle streams and denormalization

Question

I intend to use Oracle Streams for replication from Source to Target. The Target will be used mainly to run Reports. Earlier, all the reports used to run on the Source itself. Therefore, this arrangement gives better performance as all report queries are directed to a dedicated Target.

I would also like to denormalize the tables on the Target to achieve better reports performance. Can denormalization be done in conjunction with Streams replication ? I know that Oracle Streams allows us to write our own dequeue process. But is there a simple "GUI"-based way to achieve de-normalization on the fly ... as and when Streams replicated the data ? Any pointers would be very helpful.

Answer 1

I think the cleanest way to denormalize would be to leave the Streams replication intact (with 1->1 mappings of the tables) and create materialized views on the target tables that handle the transformations you need.

I think GUI interfaces to these types of transformations get cumbersome quickly as the logic gets more complicated, but if you really want a GUI solution you can look at Oracle Warehouse Builder. Once the GUI-driven design is complete within OWB, you can generate PL/SQL packages to perform the ETL.