Skip to Main Content
  • Questions

  • Materialized View Fast Refresh from heterogeneous database via oracle gateway

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, veeru.

Asked: October 23, 2017 - 5:58 pm UTC

Last updated: October 27, 2017 - 3:46 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Ora Masters,
We pull a huge volume of data from source database (MySQL) to Oracle database (11.2.0.3) using Oracle gateway (12.2.0) through a remote database link.

But the issue is that we receive following error when the MV is tried to refresh

*) ORA-28511: lost RPC connection to heterogeneous remote agent using
*) [MySQL][ODBC 5.3(a) Driver]Lost connection to MySQL server during query

We keep retrying and sometimes it goes through.

Below the MV definition
===SELECT "pom"."id1"
,"pom"."ver"
,"pom"."coding"
,"pom"."eliment"
,"pom"."letter_heading"
,"pom"."_type"
,"pom"."stock"
,"pom"."is_split_pmo"
,"pom"."brand_type"
,"pom"."crediton"
,"pom"."paym_value"
,"pom"."venlist"
,"pom"."vendor_name"
,"pom"."approval_date"
,"pom"."approver"
,"pom"."created"
,"pom"."created_where"
,"pom"."last_on"
,"pom"."estime"
,"pom"."status" as status
,"pom_add_det"."value" as pin
from "pom"@myodbc33
LEFT OUTER JOIN "pom_contact"@MYODBC36
on ("pamin_address"."id"= "pom"."ven_addr" )
where 1=1
and ( "pom"."st" != 'VALUE'
OR "pom"."storigin" IS NULL)
AND ( "pom"."prition" != 'CONSUMABLES'
OR "pom"."prio_on" IS NULL)
AND "pom"."commer" != 'CONSUMABLES'
and exists (select 1 from xx_cor_v grnmv
where grnmv.po_id = "pom"."id")"
===================

1. As mentioned in query, we are joining two remote tables via database links and also the where predicate has a reference to local MV.
Is this suggested approach? Two remote database links pointing to different database are joined. It works as MV gets refreshed intermittently, however failed numbers are more

2. Can we create MV logs on source tables i.e. in this case on MySQL database tables.

3. Is there any better solution for this problem? i.e. other than using MV for incremental data extraction (to reduce the data volume)

Kindly suggest. Thanks!

-Vk

and Connor said...

In my experience, materialized views that *cross* database (heterogenous or not) is a recipe for performance challenges.

Could you explore the following:

mview1: select * from "pom"@myodbc33
mview2: select * from "pom_contact"@MYODBC36

So you would refresh those two simple mviews, and then your third mview becomes:

select ..
from mview1 left outer join mview2
...
and exists (select 1 from xx_cor_v grnmv
where grnmv.po_id = "pom"."id")"

which you could refresh after the refresh of the first 2.
So all the "heavy lifting" is done locally in a single database.

Rating

  (4 ratings)

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

Comments

veeru k, October 25, 2017 - 5:57 am UTC

Thanks Connor for your suggestion.
But the problem we see with creating MV for the entire remote table is huge data which isn't really required.

mview1: select * from "pom"@myodbc33
mview2: select * from "pom_contact"@MYODBC36

Also, it would perform complete MV refresh (not fast refresh) and data load would be significant to produce the network connection issues.

Regards,Vk.



Connor McDonald
October 25, 2017 - 7:25 am UTC

My idea was that you would incorporate as many simple predicates as possible

and ( "pom"."st" != 'VALUE' OR "pom"."storigin" IS NULL)
AND ( "pom"."prition" != 'CONSUMABLES' OR "pom"."prio_on" IS NULL)
AND "pom"."commer" != 'CONSUMABLES'

into the mview definitions without requiring the cross-database join.

This of course only makes if you intend to query your target mview (mview3 in my example) more often than you do the refresh.

Is fast Refresh possible?

A reader, October 26, 2017 - 11:10 am UTC

Thanks again Connor.

Also I understand that for Fast Refresh we would need to create MV Logs in source DB.
Does this work when our source DB is MySQL or does it only work when both source and target DB are Oracle DB.

Thanks,
Veeru
Connor McDonald
October 26, 2017 - 1:58 pm UTC

Well....Oracle has fast refresh mviews.

Other databases....well.... :-)

veeru k, October 26, 2017 - 2:13 pm UTC

Thanks for the quick response.

Did a quick search to understand that MySQL does have Flexviews (like mviews of oracle) which use FlexCDC (kind of mview logs) for fast refresh.

Does Oracle make use of these FlexCDC for it's mview fast refresh?

Thanks,Vk.
Connor McDonald
October 27, 2017 - 3:46 am UTC

Oracle uses materialized view logs (standard tables) for refresh.


veeru k, October 27, 2017 - 6:19 am UTC

Thanks much for clarifying on all our questions.

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

Design

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