Cuestiones sobre vistas materializadas de Oracle

Abro este tema a propósito del artículo Vistas materializadas de Oracle para optimizar un Datawarehouse para que comentemos aquí cualquier cuestión relacionada con la creación, funcionamiento, consejos, errores, etc. de Vistas Materializadas de Oracle.

Enlazo para empezar la consulta que ha hecho Elena en el tema Cuestiones sobre los dblinks de Oracle sobre la creación de vistas materializadas a partir de tablas remotas (enlazadas por database links).

Tengo una duda, estoy haciendo un replica entre dos bases de datos Oracle 10g mediante vistas materializadas.

Todo bien hasta ahi, el problema es que se estan agregando columnas a la tabla principal pero estos cambios no los puedo reflejar en la tabla de replica. Mi duda es ¿Como modifico el Query que consulta la tabla principal para agregarle dentro de esa sentencia las nuevas columnas?

 

saludos

En respuesta a por OMARE (no verificado)

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.

Hola tengo una interrogante, estoy haciendo un replica entre dos bases de datos en Oracle 10g Xpress Edition usando vistas materializadas, pero como puedo saber que datos son los que se han actualizado, se trabaja con muchos registros y necesito consultar solo los que se han actualizado, las vistas se actualizan diariamente, si me podrían ayudar con esto sería genial es muy importante para mí. Saludos :)

En respuesta a por DavidC

Vistas materializadas con una Express Edition? Si no me equivoco sólo puedes utilizar vistas materializadas con bases de datos Enterprise Edition. No estarás utilizando vistas normales?

Si son vistas 'de toda la vida' es lógico que no puedas consultar información sobre cambios, los datos de la vista se seleccionan dinámicamente cada vez que la llamas.

Lo habitual en estos casos es crearte campos de control de cambios, y actualizarlos con los procesos que puedan realizar esos cambios, o con triggers que informen el campo con la fecha actual después de cada update.

Eso sí, cuidado con los triggers, porque según la frecuencia de updates y el volumen que tengas podrían efectar negativamente al rendimiento de la base de datos.

En respuesta a por Carlos

Hola si es una Express Edition 10g, sí hay el objeto de vistas materializadas usando esa versión, una opción era usando triggers pero está del rendimiento de la base de datos y dado el volumen de datos, la otra que estaba revisando es con los logs de la vistas materializadas, muchas gracias por responder :)

Pues yo estaba convencido de que la versión XE no permitía utilizar vistas materializadas, incluso consultando la comparativa de Oracle de opciones de las diferentes 'Editions' da la sensación de que en esta versión no se incluyan, pero si las estás usando está claro que sí.

Consultando la información sobre licenciamiento de Oracle Database Express Edition, parece que lo que no está incluído es el Query Rewrite con vistas materializadas (Materialized View Query Rewrite), pero sí una replicación básica con vistas materializadas de sólo lectura y actualización, que supongo que es lo que tú estás utilizando:

Basic Replication

Yes (read-only and updateable materialized view site only)

 

 

En cualquier caso espero que nos puedas explicar si sacas algo en claro con los logs, poder utilizar un método sencillo de replicación con BBDD Oracle XE seguro que va a interesar a mucha gente.

queria preguntarle si no tienen un tutorial de como realizar replicacion con vistas materializadas y con la que version de oracle es la adecuada soy novata en el tema y me seria de mucha ayuda solo es como proyecto de pruva entre dos o tre maquinas

 Hola a todos!

 

Me gustaria crear una vista materializada por medio de una select con join la cual va a tener una actualización mensual automatica. He hecho lo siguiente:

 

CREATE MATERIALED VIEW nombre_vista

 

BUILD IMMEDIATE

REFRESH  START WITH sysdate NEXT +1 MONTH FORCE

ENABLE QUERY REWRITE

AS

 

SELECT * FROM 

(SELECT created, billed, global_user_actives, optin, optout, new_users, dif, 

global_user_actives - SUM(new_users) OVER (ORDER BY created DESC) as actives_S_M, 

global_user_actives - SUM(new_users) OVER (ORDER BY created DESC) + new_users as actives_E_M, 

(CASE WHEN (global_user_actives - SUM(new_users) OVER (ORDER BY created DESC) + new_users) =0 THEN 0 

      ELSE (billed/(global_user_actives - SUM(new_users) OVER (ORDER BY created DESC) + new_users)) * service_mult END) arpu, service

 

FROM 

( select to_char("created_at", 'yyyymm') "CREATED", 

SUM( CASE WHEN "id_event" IN ('1', '5', '3') and "billed" = '1' THEN 1 WHEN "id_event" IN ('6', '4') and "billed" = '1' THEN "percent_billed"/100 ELSE 0 END) AS BILLED, 

(select count("id") from "subscriber" join "subscriber_status" on "subscriber"."status" = "subscriber_status"."id_status" where "subscriber"."status" = 1 and "subscriber"."id_service" = '8') as global_user_actives, 

SUM( CASE WHEN "id_event" IN ('1') THEN 1 ELSE 0 END) AS optin, SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END) AS optout, (SUM( CASE WHEN "id_event" IN ('1') THEN 1 ELSE 0 END) - SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END)) as new_users, 

((select count("id") from "subscriber" join "subscriber_status" on "subscriber"."status" = "subscriber_status"."id_status" where "subscriber"."status" = 1 and "subscriber"."id_service" = '8') - (SUM( CASE WHEN "id_event" IN ('1') THEN 1 ELSE 0 END) - SUM( CASE WHEN "id_event" IN ('2') THEN 1 ELSE 0 END))) as dif, 

 

(COALESCE( AVG("service"."price")*AVG("service"."revenue")/100 , 0)) as service_mult, AVG("service"."id") service

 

from "subscriber_events" 

JOIN "service" ON "subscriber_events"."id_service" = "service"."id" 

where "id_event" IN ('1', '2', '3', '4', '5', '6') 

 

 

 

Por lo que me he podido informar ... creo que la vista estaria bien creada de este modo. La duda la tengo con la creacion de los logs. Mi idea es crear varios log (uno para cada tabla de la query) los cuales serían algo así:

 

CREATE MATERIALED VIEW LOG ON  "tabla"

 

WITH PRIMARY KEY, ROWID

INCLUDING NEW VALUES;

 

No consigo entender muy bien los atributos correctos que se le deberia darle al log y por eso os pido vuestra opinion para ver que os parece el proceso que he realizado y si es posible mejorarlo en algo.

 

Un saludo y gracias

 

Mario

Como crear una vista que le permita insertar, Editar y Eliminar registros desde una tabla x?

Hola, tengo una pregunta...

Necesito modificar la defnicion del select de la vista, se puede usar un ALTER MATERIALIZED VIEW en este caso?

y su se puede me dicen como es la sentencia porque no me funciona, lo escribi asi:

 

ALTER MATERIALIZED VIEW mi_vista

as SELECT.... ;

 

muchas gracias

CREATE MATERIALIZED VIEW SALUD_REPO REFRESH START WITH SYSDATE + 10/(24 * 60 * 54) NEXT SYSDATE + 10/(24 * 60 * 54) AS select * from eps@BDSALUD;

Si se puede lo que no se debe usar es, la intruccion FAST. ya que no está soportada por la ficha tecnica.

saludos