Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Could the problem be that multiple MVs are created on the same tables/fields? I couldn't see how that could have anything to do with it, but I'm merely a developer getting his dba feet wet at the moment.

Second Edit:

It appears the order of the coalescing statement matters in my particular case. If I reverse the order of the fields in the coalesce statement, it appears to work correctly. It definitely works if I remove the coalesce altogether.

Is there some kind of race condition that may be the cause of this? I find it extremely strange.

2 Answers
2

Just a guess, but since you're doing a fast refresh, you have an MV Log, which is what I would presume MAS$3 is. Perhaps the column user_data_20 has been added recently, and is not in the MV Log? You might have to rebuild your MV log on one or both tables.

It's not a fast refresh though. I rebuilt all of the MV logs yesterday along with all of the existing MV's. Any other thoughts? I'll check on the MV log when I get in to work just incase.
–
Josh SmeatonJul 21 '11 at 23:29

It is a fast refresh: REFRESH ON COMMIT (from the docs): "Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table...".
–
DCookieJul 22 '11 at 22:31

I was under the impression REFRESH ON COMMIT does a complete refresh. REFRESH FAST ON COMMIT will do it incrementally. I had to remove the FAST clause since it is a intra-day table that is truncated daily, and hence isn't fast refreshable after truncation. Please correct me if I'm misunderstanding though.
–
Josh SmeatonJul 23 '11 at 5:35

Unless I'm reading the docs wrong, it's pretty clear that it's a fast refresh that's done. Would you really want a complete refresh of your MV on every commit on the master? That would really slow down any commits. I just don't see how that would work.
–
DCookieJul 23 '11 at 18:33

the complete refresh is necessary on these intra-day tables since they are truncated once a day. A fast refresh fails on a truncated table. The complete refresh happens once every 15 minutes (when the table is filled), and is ok, since it is only one days worth of data. At the end of each day, the data is moved into a historical table, which IS fast refreshed. I'm certain that this is not a FAST refresh. I appreciate your help though.
–
Josh SmeatonJul 24 '11 at 22:21

I don't believe the column is being dropped/renamed, due to another MV across the same columns working correctly. Please see my edit for more information. Everything is happening in the same schema, yes. I don't know if I'm able to get a trace. I'll look into that now. It is a 3rd party java application that does the ETL from staging, to these warehouse tables I'm creating the MVs on.
–
Josh SmeatonJul 22 '11 at 1:12