Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Radha.

Asked: April 26, 2011 - 1:19 am UTC

Last updated: January 17, 2013 - 8:21 am UTC

Version: 11.1.0

Viewed 1000+ times

You Asked

Hi Tom

a) Is it true that Materialized views can not be refreshed FAST ON COMMIT, if the base table is participating in Distributed transactions.

b) We created Materialized view which has outer joins on the columns participating in the where condition. It is observed that there are some warnings in alert log stating that columns participating in the outer joins have no unique constraints. Can this be ingored ? As, we can not create unique constraints on these columns, as they are composite primary keys in the child tables.

c) The materialized view could not be FAST refreshed on commit, as there were some transactions in doubt, which is captured in dba_2pc_pending views. These are not cleared using force rollback or commit. Is there way to clear these pending transactions.

Thanks in advance.

and Tom said...

a) correct
http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#sthref396

b) I don't see how B follows A from: "we can not create unique constraints on these columns, as they are composite primary keys in the child tables.". To me, you just made it sound like they are already unique. But, anyway...

It is ok, as long as you don't want a fast refresh.

http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#sthref396

"Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
"


c) I don't see the connection between a refresh fast on commit materialized view (it won't have anything to do with distributed) and pending transactions? What connection have you drawn and how did you draw it?

see

http://docs.oracle.com/cd/E11882_01/server.112/e17120/ds_txnman.htm#i1207516

for how to manage distributed transactions and manually resolve them.

Rating

  (5 ratings)

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

Comments

"Materialized views", version 11.1.0

Radha Tanuku, April 28, 2011 - 4:25 am UTC

Hi Tom,Thanks for your valuable response.

a)In our OLTP database, the base tables are participating in distributed transactions. As materialized view with refresh FAST on Commit is not an option, what is the other best alternative for on COMMIT refresh ( We have already explored the normal views which are proven to be slow when compared to materialized views).

c) As the base tables are participating in distributed transaction, the Materialized view(created on those tables with on commit refresh option) was not getting refreshed when there was an insert in one of the base tables. Because of this, the whole transaction is becoming indoubt/pending transaction in database. However, as per your suggestion in point a, we will refrain from using materialized view.

The pending transactions created in the database should be automatically handled/cleared by RECO process. I observed that it is not handled automatically. Has it some thing to do with above problem?
Tom Kyte
April 28, 2011 - 7:50 am UTC

a) there is none.

I cannot imagine an "OLTP" database whereby you are doing distributed transactions in "real time" - by the end users. That makes the entire system fragile beyond belief, not to mention the performance hit. Why not store and forward replication (actually, I should ask "why replication at all, in the year 2011???)

c) I'm not following you - do you have in doubt distributed transactions (can be resolved automagically) or a materialized view, that due to you using a distributed transaction, was unable to refresh (and hence will need your intervention).

MV

A reader, April 28, 2011 - 7:16 pm UTC


MVs and distributed transactions

Dominic Brooks, January 15, 2013 - 10:57 am UTC

> I cannot imagine an "OLTP" database whereby you are doing distributed transactions in "real time" - by the end users.

I'm not sure I understand this comment.
It's fairly normal for modern enterprise applications to be using distributed transactions via, for example, XA JDBC driver ( I assume this would fail for the same reasons above but I've not tested it). This would fit the pattern of an OLTP database doing distributed tranasctions in real time, wouldn't it? Do you see this approach as flawed?

Thanks

Dominic
Tom Kyte
January 15, 2013 - 2:27 pm UTC

truely distributed - relying on multiple databases in a distributed environment.


that the java/jdbc world for some reasons wishes to use an external resource manager so they have to do two phase commits against a single database isn't what I was talking about. when I've seen the XA stuff - there is typically *one* database involved and it makes everything really complex, hard to understand and slower than it needs to be.

that is just slow (doing the extra work outside of the database), it isn't decreasing hugely availability and reliability like a real time truely distributed application would.



because something is "normal" doesn't make it good either.

MVs and distributed transactions

Dominic Brooks, January 16, 2013 - 3:55 am UTC

> when I've seen the XA stuff - there is typically *one* database involved and it makes everything really complex, hard to understand and slower than it needs to be.

I get what you're saying and it rings some bells.

But also a lot of the time it does sound like a distributed transaction is required when combining taking stuff off queues, doing stuff in the db, and/or putting stuff on queues.

There almost certainly are applications with transactions which are justifiably using XA drivers and I've seen several situations where fast refresh on commit MVs would make mixed workload reporting a heck of a lot more performant.

So, if that is the real world, what it the technical issue behind the incompatibility between MVs and distributed transactions?


Tom Kyte
January 16, 2013 - 12:13 pm UTC

But also a lot of the time it does sound like a distributed transaction is
required when combining taking stuff off queues, doing stuff in the db, and/or
putting stuff on queues.


but that wouldn't be an end user transaction - it would be a background thing, something that if it doesn't work for the next ten minutes it isn't the end of the world.


an end user would
a) queue a message
b) commit;

a background process would

a) dequeue a message
b) process a transaction elsewhere
c) commit a & b


So, if that is the real world, what it the technical issue behind the
incompatibility between MVs and distributed transactions?


it is more a practicability thing.

Materialized views used to be called snapshots - they were for distributed processing. they were invented to make the maintenance of distributed replicas *loosely* coupled (non distributed from the end user perspective). You make a change on database A and only database A is involved. Then a background process picks up the change from A, propagates it to B, does a change on B, deletes the change from A and commits (A&B) (just like above).


On commit refreshes were designed from a practicability perspective to be for end user transactions. Making anything in an end user transaction distributed like that is "a really bad idea" (tm). so we don't do it, we don't even want to consider doing it. Having end user transactions relying on multiple data sources has huge negative performance aspects (huge, as in really really really big) and huge availability impacts (the odds of one or the other data source being unavailable at any point in time is a lot larger than just "your" data source being unavailable).



If you have a true need for this data to be maintained in two places in real time - I am of the strong (really strong, as in I won't even think about anything else) opinion that you really wanted *a single system* - no replication.


MVs and distributed transactions

Dominic Brooks, January 16, 2013 - 3:02 pm UTC

> no replication

Hmmm. But we're not talking about replication.

I know you've mentioned snapshots and the original purpose of MVs but things have moved on from there haven't they?

MVs are often promoted and used as a means of improving performance within a single database. For example for presenting an aggregated view of the data or for precalculating expensive joins, etc. In the same single database.

If, for whatever reason, the transaction coming in with updates the underlying data is in an XA transaction then a fast refresh on commit MVs for the aggregation or the some other "complex" view of the data is a non-starter.


Tom Kyte
January 17, 2013 - 8:21 am UTC

the entire goal of MV's is to make things *non distributed*, no two phase commits, no two things to be resource managed.


you in fact talking about replication - MV's are replication. A single instance MV is a special case that involves no distributed two phase commit.

they just do not support distributed on commit fast refresh - and I doubt they ever will.


You'd have to queue the update to the base table as part of the XA transaction and very soon after it commits - perform that update in a loosely coupled fashion so the update would update the base table and maintain the materialized view. In fact, in any sort of high end OLTP system - this is the way you should be doing it since the MV refresh is going to cause a high degree of serialization - meaning you won't be a high end OLTP system if you use this technique in an end user transaction...