Diferencias entre una Vista y una Vista Materializada en Oracle


Una Vista es un objeto de Oracle que al consultarlo ejecuta por detrás una query y nos devuelve el resultado (donde la query puede acceder a tablas, otras vistas, utilizar funciones o procedimientos, etc.). Cada vez que accedemos a la vista, la query se ejecuta y nos devuelve la información que en ese momento exista en el origen.

Por contra, una Vista Materializada (Materialized Views) es otro tipo de objeto de Oracle que aunque técnicamente es lo mismo que una Vista (ejecuta una consulta sql), lo que hace es almacenar físicamente en caché el resultado de ejecutar una query en un determinado momento, de forma que cada vez que consultemos la Vista Materializada lo que vamos a recuperar es lo que había en el origen en el momento en el que se creó o se refrescó la VM.

Vista materializada y vista convencional en Bases de Datos Oracle

La sintaxis para crear una Vista materializada es:

CREATE MATERIALIZED VIEW nueva_vista_materializada
[TABLESPACE nuestro_tablespace]
[BUILD {IMMEDIATE | DEFERRED}]
[REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicial] NEXT intervalo_tiempo } |
{COMPLETE | FAST | FORCE | NEVER} ]
[{ENABLE|DISABLE} QUERY REWRITE]
AS SELECT tabla1.campo_a, tabla2.campo_b
FROM tabla1 , tabla2
WHERE tabla1.campo_a = tabla2.campo_a
AND …

Donde podemos especificar de qué forma queremos que se recuperen los datos: de forma inmediata o en otro momento:

  • BUILD IMMEDIATE: El resultado se almacena al ejecutar la query
  • BUILD DEFERRED: Sólo se crea la definición, el resultado se almacenará más adelante. Para ello podemos utilizar cuando queramos llenarla la función REFRESH del paquete de Oracle DBMS_MVIEW.

También podemos especificar cada cuánto tiempo o cuándo queremos que se actualicen los datos de la vista:

  • REFRESH ON COMMIT: los datos se actualizan cada vez que se haga COMMIT sobre los objetos fuente.
  • REFRESH ON DEMAND: sólo se actualizarán cuando se haga manualmente a través de las funciones REFRESH de Oracle (más adelante se explica cuáles son y cómo invocarlas).
  • REFRESH [START WITH fecha_inicial] NEXT intervalo_tiempo: con esta sentencia podemos establecer una actualización periódica de la vista. La fecha_inicial puede definirse como sysdate y el intervalo_tiempo cada cuanto tiempo queremos realizar la tarea (podemos incrementar numéricamente el sysdate)

Y especificar si permitimos a Oracle o no reescribir las queries cuando en una consulta se intente acceder a las tablas origen, de forma que por detrás el duende de Oracle ataque directamente a las vistas materializadas, cuyo acceso y recuperación de datos es más rápido, sin que el usuario necesite hacer nada. Esto sólo lo hará el Optimizador de Oracle si la consulta lo permite (que las tablas a las que va a buscar la información coinciden con las tablas origen de la vista materializada).

  • ENABLE QUERY REWRITE: Oracle puede reescribir las queries
  • DISABLE QUERY REWRITE: lo contrario

La ventaja de este tipo de objeto la encontramos cuando la query asociada es muy compleja, tiene numerosos joins y se utiliza con frecuencia, ya que nos permite mejorar el rendimiento de la SQL al tenerla almacenada en memoria y ejecutarse una sóla vez. Sin embargo, el principal hándicap es que nos vemos obligados a mantenerla actualizada de forma regular para no perder calidad en los datos. Para ello podemos definir en el script de creación de la Vista Materializada el tipo de actualización que tendrá al ejecutar los métodos propios de Oracle para refrescarla. Existen los siguientes tipos de actualización para una Vista materializada

  • FAST: Es la opción más recomendada por su rendimiento. Es una actualización incremental, es decir, sólo actualiza los registros que hayan cambiado. Para que esta opción funcione correctamente necesitamos generar estadísticas de la vista para que Oracle esté al tanto de los cambios. La forma de hacerlo es creando un log de la vista materializada sobre la PK de cada tabla origen.

    CREATE MATERIALIZED VIEW LOG ON tabla_origen
    WITH PRIMARY KEY
    INCLUDING NEW VALUES;

  • COMPLETE: regenera por completo el resultado al borrar y volver a ejecutar de nuevo la query.
  • FORCE: Es el valor por defecto. En caso de que se pueda, se ejecutará el FAST; si no, el COMPLETE.
  • NEVER: nunca se refresca la vista.

Si modificamos el origen y queremos que éste se refleje en la Vista materializada, tenemos que invocar a una función del paquete DBMS_MVIEW de Oracle que se encarga de actualizar los datos. Tenemos dos a nuestra disposición:

  • DBMS_MVIEW.REFRESH: con este método pasamos por parámetro la Vista Materializada para que se actualice:

    begin

    DBMS_MVIEW.REFRESH(‘TABLA_EMPLEADOS’);

    end;

  • DBMS_MVIEW.REFRESH_DEPENDENT: mientras que con este lo que le pasamos por parámetro son todas las tablas origen de las que depende la vista:

    begin

    DBMS_MVIEW.REFRESH_DEPENDENT(‘TABLA_ORIGEN_1’, ‘TABLA_ORIGEN_2’, ‘TABLA_ORIGEN_3’);

    end;

6 Comentarios

Vistas de metadatos de Oracle, cómo buscar objetos de la base de datos


Vistas de metadatos de Oracle, cómo bucear por los objetos de la base de datos

En alguna ocasión hemos tenido que hacer una consulta en Oracle sobre una tabla cuyo nombre no recordamos con exactitud y nos hemos tenido que pegar con la documentación del proyecto (si existe, claro) hasta encontrarla. Con el IDE TOAD for Oracle podemos usar el Schema Browser para encontrar rápidamente a través de los filtros de metadatos la tabla en cuestión, sin embargo, si estamos con cualquier otra herramienta que no disponga de un buscador para el diccionario de datos de  la base de datos, la única forma de buscar entre todos los objetos del sistema es utilizando alguna de las vistas de metadatos propias de ORACLE del esquema SYSTEM (SYS), que almacenan toda la información sobre los objetos de la BBDD.

Existe una vista por cada nivel de acceso en función del usuario con el que nos hemos conectado, por ejemplo, las vistas que agrupan toda la información sobre los índices serían DBA_INDEXES, ALL_INDEXES y USER_INDEXES.

  • DBA_: contiene todos los objetos de la instancia a la que nos hemos conectado. Es necesario tener los privilegios suficientes.
  • ALL_: contiene todos los objetos accesibles por el usuario con el que nos hemos conectado, tanto los propios como sobre los que se tiene acceso (ya sean de otros usuarios o esquemas)
  • USER_: sólo contiene los objetos que pertenecen al usuario.

Estas vistas de metadatos nos permiten buscar rápidamente cualquier objeto de la base de datos.

  • SYS.DBA_TABLES ó SYS.DBA_ALL_TABLES (Tablas)
  • SYS.DBA_VIEWS (Vistas)
  • SYS.DBA_INDEXES (Índices)
  • SYS.DBA_TABLESPACES (Tablespaces)
  • SYS.DBA_USERS (Usuarios)
  • SYS.DBA_PROCEDURES (Procedures)
  • SYS.DBA_SEQUENCES (Secuencias)
  • SYS.DBA_TRIGGERS (Triggers o Disparadores)





Una consulta de ejemplo con la que obtendríamos el nombre completo de una tabla sería:

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM SYS.DBA_TABLES
WHERE TABLE_NAME LIKE ‘%EMPLEADOS%’;

En relación a las tablas DBA_TABLES y DBA_ALL_TABLES, mencionar que la diferencia radica en que la primera sólo contiene información de las tablas, mientras que la segunda incluye también metadatos de los Object Tables, que en Oracle son tipos de datos definidos por el propio usuario (son estructuras o arrays que pueden incorporarse como columnas de una tabla o incluso permiten crear nuevas tablas instanciando estos objetos; para crearlos se utiliza la sentencia CREATE TYPE < nombre > AS OBJECT).

 

SYS.ALL_TAB_COLS: buscar tablas por sus columnas en Oracle

Otra tabla del system que nos puede ayudar a la hora de buscar información en los metadatos de Oracle es  SYS.DBA_TAB_COLS. Esta tabla contiene las columnas de cada objeto table de nuestra base de datos, de forma que buscando por el campo COLUMN_NAME podremos saber en qué tablas se encuentra (útil cuando sabemos el campo de destino y queremos saber saber de donde viene o por donde pasa).

Por ejemplo, para obtener todas las tablas del sistema que incluyen una columna en concreto podríamos utilizar esta query:

SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM SYS.ALL_TAB_COLS
WHERE COLUMN_NAME = ‘FECHA_ALTA’;

 

SYS.DBA_TAB_PRIVS: Ver los permisos o privilegios de una tabla en Oracle

La forma más rápida de ver los permisos de una o varias tablas es utilizando la vista SYS.DBA_TAB_PRIVS. Filtrando por el nombre de la tabla recuperaremos una tupla con cada permiso otorgado (select, alter, insert, update, delete, execute, etc.), el rol al que se le ha asignado y el usuario propietario. Aunque la nomenclatura de la vista incluye _TAB, se pueden consultar los permisos de cualquier objeto de la BBDD: tablas, vistas, secuencias, etc.

SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
FROM SYS.DBA_TAB_PRIVS
WHERE TABLE_NAME LIKE ‘%EMPLEADOS%’

Sin Comentarios