sql - modificar - diferencia entre vista y vista materializada oracle

Oracle: cómo crear una vista materializada con FAST REFRESH y JOINS

Question

Así que estoy bastante seguro de que Oracle lo admite, así que no tengo idea de lo que estoy haciendo mal. Este código funciona:

CREATE MATERIALIZED VIEW MV_Test
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS
    SELECT V.* FROM TPM_PROJECTVERSION V;

Si agrego un JOIN, se rompe:

CREATE MATERIALIZED VIEW MV_Test
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS
    SELECT V.*, P.* FROM TPM_PROJECTVERSION V
    INNER JOIN TPM_PROJECT P ON P.PROJECTID = V.PROJECTID

Ahora me sale el error:

ORA-12054: no se puede establecer el atributo de actualización ON COMMIT para la vista materializada

Creé registros de vista materializados tanto en TPM_PROJECT como en TPM_PROJECTVERSION. TPM_PROJECT tiene una clave principal de PROJECTID y TPM_PROJECTVERSION tiene una clave primaria compuesta de (PROJECTID, VERSIONID). ¿Cuál es el truco para esto? He estado buscando en los manuales de Oracle en vano. ¡Gracias!

Answer 1

¿Lo has intentado sin la unión de ANSI?

CREATE MATERIALIZED VIEW MV_Test
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS
SELECT V.*, P.* FROM TPM_PROJECTVERSION V,TPM_PROJECT P 
WHERE  P.PROJECTID = V.PROJECTID

Answer 2

Las verificaciones de teclas para ACTUALIZAR RÁPIDAMENTE incluyen lo siguiente:

1) An Oracle materialized view log must be present for each base table.
2) The RowIDs of all the base tables must appear in the SELECT list of the MVIEW query definition.
3) If there are outer joins, unique constraints must be placed on the join columns of the inner table.

No 3 es fácil de perder y vale la pena destacar aquí

Answer 3

Obtendrá el error en REFRESH_FAST, si no crea registros de vista materializados para las tablas maestras a las que hace referencia la consulta. Si alguien no está familiarizado con las vistas materializadas o lo usa por primera vez, la mejor manera es usar Oracle sqldeveloper y poner gráficamente las opciones, y los errores también proporcionan mucho mejor sentido.

Answer 4

Para empezar, desde la Guía de Oracle Database Data Warehousing :

Restricciones en actualización rápida en vistas materializadas con combinaciones solo

...

• Los rowids de todas las tablas en la lista FROM deben aparecer en la lista SELECT de la consulta.

Esto significa que su afirmación deberá verse más o menos así:

CREATE MATERIALIZED VIEW MV_Test
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS
    SELECT V.*, P.*, V.ROWID as V_ROWID, P.ROWID as P_ROWID 
    FROM TPM_PROJECTVERSION V,
         TPM_PROJECT P 
    WHERE P.PROJECTID = V.PROJECTID

Otro aspecto clave a tener en cuenta es que sus registros de vista materializados deben crearse como with rowid .

A continuación se muestra un escenario de prueba funcional:

CREATE TABLE foo(foo NUMBER, CONSTRAINT foo_pk PRIMARY KEY(foo));

CREATE MATERIALIZED VIEW LOG ON foo WITH ROWID;

CREATE TABLE bar(foo NUMBER, bar NUMBER, CONSTRAINT bar_pk PRIMARY KEY(foo, bar));

CREATE MATERIALIZED VIEW LOG ON bar WITH ROWID;

CREATE MATERIALIZED VIEW foo_bar
  NOLOGGING
  CACHE
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT  AS SELECT foo.foo, 
                                    bar.bar, 
                                    foo.ROWID AS foo_rowid, 
                                    bar.ROWID AS bar_rowid 
                               FROM foo, bar
                              WHERE foo.foo = bar.foo;