Oracle: crear vistas materializadas, particionadas por rango

Las vistas materializadas son un gran invento, hasta que ya no lo son más. Cuando la cantidad de datos es demasiada, la actualización de la vista puede convertirse en una pesadilla para el sistema. Podríamos usar una actualización tipo "FAST" pero con eso necesitarías de más almacenamiento para llevar un logs de cambios en las tablas que alimentan la vista. Lo que no siempre es posible.

Una forma alternativa sería particionar la vista, y al parecer es algo relativamente nuevo hacerlo por rangos de fecha.

Pongamos como ejemplo que se mantiene una tabla con transacciones, es típico el caso en que se almacenen con fecha y que las consultas a la tabla se hagan mensualmente: "ventas mensuales", "pagos mensuales", etc. Entonces podríamos generar particiones por cada mes.

Finalmente, luego de un poco de búsqueda en Internet, la sintaxis que terminé construyendo es la siguiente:

create materialized view <VM_NOMBRE>
    partition by range (<columna_de_fecha>)
    interval(NUMTOYMINTERVAL(1, 'MONTH'))
    (
        PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('01-02-2018', 'DD-MM-YYYY')),
        PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-03-2018', 'DD-MM-YYYY')),
        PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('01-04-2018', 'DD-MM-YYYY'))
    )
    refresh complete on demand
    as (<query_que_alimenta_la_vista>);

Algunas cosas notables:

• Los valores almacenados en cada partición son "menores" (o anteriores) a la fecha del parámetro. Es decir, en el ejemplo en cada partición estaría Enero, Febrero y Marzo del 2018

• A partir de marzo (en el ejemplo), las particiones tendrán un nombre automático tipo sys_pNumeroNumeroNumero

• No puede ser usado para tablas organizadas por índice

• Sólo puede usar una key para la partición, DATE o NUMBER

• No se pueden usar índices de dominio

• No se pueden usar a nivel de sub-partición

Por post anteriores, se sabe que no soy muy fan de Oracle. Por ejemplo, no le veo sentido crear manualmente cada partición si ya se le da como orden el Intervalo, y de todas formas luego creará sus propios nombres cuando lleguen datos con fechas mayores.

En fin, aunque tenga varias restricciones, le puede servir de ayuda a más de alguno.

ciao