Materialized View Not Updating when committing through Application

1
votes

I am facing a major issue in a production environment where i had introduced a materialized view with a view log in order to refresh on commit to the master table. I tested this out with sql developer and it works perfectly.

But when the underlying table is updated using my application which is using iBatis to commit to the table, the materialized view is not getting updated with the new values.

Can anyone tell me what might be the issue here?

Appreciate your help in this regard

1
sqloracleplsqlmaterialized-views
First, look at Determining the Fast Refresh Capabilities of a Materialized View. That indicates what types of fast refresh are available. Then find the exact SQL statements used by both contexts, compare them and see if their difference are related to any of the POSSIBLE = 'N' rows in MV_CAPABILITIES_TABLE. Here's a wild guess: in SQL Developer you're using a single INSERT statement, but iBatis is using a combination of INSERT and UPDATE. REFRESH_FAST_AFTER_INSERT = Y, but REFRESH_FAST_AFTER_ANY_DML = N. – Jon Heller
@jonearles thank you for the suggestion. I trust you might be correct in this case. Let me investigate this in the path you have put forward. I wanted to know one more thing. Will a complete refresh slow down things? I am just wondering because if fast refresh will not be possible, then the only possibility it a complete refresh isn't it? – dinukadev
Yes, a complete refresh will be slower than an incremental refresh. Those are the only two possibilities. – Jon Heller
hi @jonearles. You were correct. Can you please post your comment as an answer and i will accept it. Thank you once again and sorry for the late update. – dinukadev

1 Answers

0
votes

First, look at Determining the Fast Refresh Capabilities of a Materialized View. That indicates what types of fast refresh are available.

Then find the exact SQL statements used by both contexts, compare them and see if their difference are related to any of the POSSIBLE = 'N' rows in MV_CAPABILITIES_TABLE.

Here's a wild guess: in SQL Developer you're using a single INSERT statement, but iBatis is using a combination of INSERT and UPDATE. REFRESH_FAST_AFTER_INSERT = Y, but REFRESH_FAST_AFTER_ANY_DML = N.