I have materialized view (it uses joins, WITH, partition by; query returns about 42 mln rows) with 2 simple indexes on it. Only full refresh is used.
The first refresh works fine (it takes ~100 minutes) but second refresh works several days and failed to complete.
Also I dropped indexes and re-run test. It works fine.
Here is all results (time and redo entries from session statistics):
1) Without indexes, first run
time: 72 min
redo: 42 mln (it is close to row number)
2) Without indexes, second run
time: 106 min
redo: 84 mln (42 mln to delete all and 42 mln to insert new)
3) With 2 indexes, first run
time: 99 min
redo: 126 mln (42 mln for rows and 42 mln for each index)
4) With 2 indexes, second run
time: failed after 48 hours
redo: 453 mln when failed (I have no idea why it's so huge)
Oracle version: 11g Enterprise Edition Release 220.127.116.11.0
The issue was reproduced on different instances&servers. I have no server where it works correctly. I think that it is some kind of bug but can't find anything similar