MV refresh takes 4 hours, same statement in CTAS takes 40 seconds
Hi all,
we have a MV with a rather large select statement. We are currently moving this MV from Oracle 11.1 to another server running 12.1. A complete refresh on the 11.1 server takes two minutes tops, on the 12.1 server it takes about four hours. Running the MV's select statement as a CTAS statement on the 12.1 server takes about 40 seconds. Statistics are of course up to date.
When the MV refreshes, I see a lot of time spent on the CPU, doing milliions of logical reads, probably because the optimizer chose a nested loop somewhere. I do not see this doing the CTAS.