Cuestiones sobre vistas materializadas de Oracle

Buena pregunta. En teoría la única manera de agregar columnas a una vista materializada es borrar la vista y volver a recrearla completamente con la nueva definición.

El problema es que no existe ningún comando tipo ALTER TABLE que se pueda aplicar a las vistas materializadas para agregar columnas, o simplemente para cambiar la definición de una columna.

Para vistas con pocos datos no hay ningún problema, la recreación puede ser rápida. El problema viene cuando hablamos de vistas materializadas grandes, de un Data Warehouse por ejemplo, donde la recreación puede ser muy costosa.

Pero siempre hay una solución para todo. En el Blog de Arup Nanda he encontrado un excelente post que explica cómo ingeniárselas para alterar la definición de una vista materializada sin recrearla completamente.

Se trata de utilizar la opción ON PREBUILT TABLE al crear la vista para que la vista se apoye internamente en una tabla. Cuando se ha de realizar alguna modificación se borra la vista, pero la tabla interna permanece. Se realiza un ALTER TABLE sobre esta tabla, y después se vuelve a crear la vista materializada a partir de la tabla. Como la tabla ya existe la creación de la vista es muy rápida.

Extraigo a continuación las sentencias que permitirían hacerlo, consultar el detalle en el post original:

SQL> create table mi_vista (contador number(10));

SQL> create materialized view mi_vista on prebuilt table never refresh as select cast(count (1) as number(10)) contador from t1;

SQL> DROP MATERIALIZED VIEW mi_vista;

SQL> alter table mi_vista modify (contador number(11));

SQL> create materialized view mi_vista on prebuilt table never refresh as select cast(count (1) as number(11)) cnt from t1;

Y ahora me dirás que tu ya tienes creada tu vista materializada y esta solución llega un poco tarde. Es cierto, pero para eso Arup Nanda también propone una solución para hacer una reconstrucción de la vista materializada con el mínimo impacto en el rendimiento de nuestra base de datos:

1. Crear una tabla con la opción nologging a partir de la Vista materializada
SQL> create table nueva_vista_materializada nologging as select * from mi_vista;
2. Recoger la definición de la Vista materializada del diccionario de datos
SQL> select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','MV1') from dual ;
3. Guardarla en un fichero que se ejecutará después
4. Editar el fichero añadiendo la opción ON PREBUILT TABLE.
CREATE MATERIALIZED VIEW "DATAPRIX"."MI_VISTA"  ("contador")ORGANIZATION HEAP ON PREBUILT TABLE PCTFREE 10
5.Hacer export de tipo Data Pump con la opción CONTENTS=METADATA_ONLY.
6. Eliminar la Vista materializada "mi_vista".
7. Renombrar la tabla nueva_vista_materializada a mi_vista.
8. Ejecutar el script creado anteriormente para recrear la Vista materializada
9. Importar el fichero de Dump exportado anteriormente.

Bueno, espero que esto te sirva, ya nos explicarás si lo has podido aplicar.