Oracle - Materialized View alter structure so slow

Question

I have a huge materailized view that I have to adjust. It's a simple adjustment as I'm just adding an NVL function to the select statement.

I.e. Original...

Select this,
       that.....

I.e. Modified

Select NVL(this, orThat) as this,
       NVL(That, orThis) as that

The query takes 26 seconds to run, but due to the amount of rows retrieved (2.3 million) it is dead slow. It ran for almost 5 days straight and then I stopped it.

This is a problem, especially since I need to deliver this to a client, and they can't run a script for 5+ days to create a MV.

Question: Is there any way to speed up the altering/recreation of a MV? Would it be faster if I altered the MV or would it be around the same as dropping and recreating?

Oracle version = 10g

Answer 1

You can't alter the definition of the query for a materialized view - you have to drop and recreate it. That said, you can try this approach, it could be faster than recreating the entire MV:

1. Drop the materialized view, using PRESERVE TABLE.
2. Update the data in the table that used to be the MV to reflect the new column definitions.
3. Recreate the materialized view using the ON PREBUILT TABLE clause.

If you have indexes on the view, it may be helpful to disable and rebuild them.