Skip to Main Content
  • Questions

  • Materialized View or dblink - which one is better and why

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Yeshwant.

Asked: October 23, 2017 - 2:56 pm UTC

Last updated: October 24, 2017 - 1:58 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

We have an ETL process that has to get data from remote db (Oracle 12c). For some reasons, we cannot give direct access on the remote tables to the ETL process.
We are considering two options:
Option 1. On local db, create dblink to remote db, create views using dblink and give select privilege to ETL process on these views.
Option 2. On local db create materialized views from which the ETL process will source data.

ETL run time calculation:
Option 1: ETL run time (ETL fetch data from views using dblink to remote db)
Option 2: Time to refresh materialized views + ETL run time (we cannot use fast refresh as it needs logs on source and we will not be able/allowed to create logs on source)

Which of the two options will be faster? Why?

Thanks for your response.


and Chris said...

There's only one real answer to this question:

¯\_(ツ)_/¯

Which will come out faster depends on your exact queries, data, how it's all coded, etc.

But assuming that:

- You're complete refreshing the materialized views
- They return "all" the data from the source
- The queries only access tables from the remote database

I would expect option 1 to be faster. Because option 2 has to do the same work to query the source and send the data over the network. Then on top of that build the local MV and then run another set of queries against this.

But if the MVs only select a subset of the data, I guess it's possible this could come out better in some cases.

So stop guessing! Test.

Build prototypes of the two methods. Then profile/trace them to see which comes out better.

Of course, if you are able to get permission to build fast refreshable MVs, I would expect option 2 to be faster. This is because you can schedule regular refreshes separate from your ETL process. This will keep the local MV up-to-date, taking the network out of the equation.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yeshwant Rajwade, October 23, 2017 - 5:54 pm UTC

Thanks Chris for the very quick response.
From your response, I would infer that getting same amount of data from a remote db via dblink and via MV takes same time and resources.
I was told dblinks are slower and take more resources as compared to MV refresh. From your response it looks like that is not the case. Is my inference correct?
Connor McDonald
October 24, 2017 - 1:58 am UTC

More resources no.

More time - perhaps.

If your network is not fast enough to handle the flow of data, the use of a db link might hurt. If the network is not a bottleneck, then a dblink is basically like a client SQL to a database server.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database