Oracle-物化视图更改结构如此缓慢 [英] Oracle - Materialized View alter structure so slow

问题描述

我有一个必须调整的宏大观点.这是一个简单的调整，因为我只是在select语句中添加了NVL函数.

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.

即原始...

Select this,
       that.....

即修改

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

查询需要26秒钟才能运行，但是由于检索到的行数(230万)，它的速度非常慢.它连续运行了将近5天，然后我停了下来.

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.

这是一个问题，尤其是因为我需要将其交付给客户端，并且他们不能运行脚本超过5天来创建MV.

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.

问题:是否有任何方法可以加快MV的更改/娱乐?如果我更改了MV，速度会更快吗?或者与删除并重新创建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版本= 10g

Oracle version = 10g

推荐答案

您不能更改实例化视图的查询定义-您必须删除并重新创建它.也就是说，您可以尝试这种方法，它可能比重新创建整个MV更快:

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. 使用PRESERVE TABLE删除实例化视图.
2. 更新表中曾经是MV的数据，以反映新的列定义.
3. 使用ON PREBUILT TABLE子句重新创建实例化视图.

如果视图上有索引，则禁用并重建索引可能会有所帮助.

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

这篇关于Oracle-物化视图更改结构如此缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！