Skip to Main Content
  • Questions
  • Using Materialized View as ETL option

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajneesh.

Asked: April 03, 2020 - 12:33 pm UTC

Answered by: Connor McDonald - Last updated: April 06, 2020 - 12:37 am UTC

Category: Database Development - Version: 19 c

Viewed 100+ times

You Asked

Hello TOM,

Any comment regarding Using Materialized View as ETL option to populate data from OLTP to OLAP?
Pros and Cons will be useful.

Thanks,

Rajneesh


and we said...

You need to elaborate on what you mean by this.

I assume you mean an MVIEW on your OLAP system which reads from source tables on your OLTP system.

If this is the case, then it is most likely either

a) complete refresh at regular intervals, or
b) partial refresh using mview logs on the source tables

If in (a) the full refresh does not place excessive load on your OLTP system (eg, you can run it at quiet times at night), or in (b) the transactional overhead of mview logs is not a significant performance concern, then these methods would be OK.

As always, testing is essential to ensure your OLTP system is not adversely affected.

and you rated our response

  (1 rating)

Reviews

Thanks for your help

April 06, 2020 - 5:37 am UTC

Reviewer: A reader

Thank you, it helps

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.