Problemas en Vistas Materializadas - Parte 2

Como pudimos ver en el post "Problemas en Vistas Materializadas - Parte 1", podemos identificar con el paquete DBMS_MVIEW (procedimiento EXPLAIN_MVIEW), los problemas que pueden haber en nuestra Vista Materializada e implementar las soluciones necesarias. La contra de ese procedimiento es que nos muestra los errores que tenemos en la Vista Materializada, pero no nos dice cómo solucionarlos.

En Oracle 10g se introduce el paquete DBMS_ADVISOR (procedimiento TUNE_MVIEW) que nos dice qué cambios debemos implementar en nuestra Vista Materializada para soportar las capacidades que necesitamos.

Veamos un ejemplo:

SQL_10gR2> CREATE TABLE test1 AS
  2  SELECT level id, level*level total
  3  FROM dual
  4  CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
  2  SELECT level id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 20 ;

Table created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT test2.id,
  2         test2.nom,
  3         SUM(test1.total) sum_total
  4  FROM test1,
  5       test2
  6  WHERE test1.id = test2.id(+)
  7  GROUP BY test2.id,
  8           test2.nom
  9  ORDER BY id ;

        ID NOM         SUM_TOTAL
---------- ---------- ----------
         1 nom_1               1
         2 nom_2               4
         3 nom_3               9
         4 nom_4              16
         5 nom_5              25
         6 nom_6              36
         7 nom_7              49
         8 nom_8              64
         9 nom_9              81
        10 nom_10            100

10 rows selected.

Bien, ya creé mi ambiente de prueba. Ahora voy a crear las tablas de Log necesarias para la capacidad de REFRESH FAST. Pero las voy a crear de forma errónea para demostrar el poder que nos brinda el paquete DBMS_ADVISOR.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
  2  WITH ROWID, SEQUENCE (id) ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
  2  WITH ROWID, SEQUENCE (id,nom) ;

Materialized view log created.

Procedemos a crear la Vista Materializada:

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;
FROM test1,
     *
ERROR at line 15:
ORA-32401: materialized view log on "CDW"."TEST2" does not have new values

Efectivamente como supusimos, tenemos errores en las tablas de Log que creamos.
Ahora vamos a ejecutar DBMS_ADVISOR.TUNE_MVIEW y ver los resultados en la tabla USER_TUNE_MVIEW...

SQL_10gR2> DECLARE
  2  l_nombre  VARCHAR2(100) := 'test_1' ;
  3  BEGIN
  4  DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
  5  NOCOMPRESS
  6  LOGGING
  7  BUILD IMMEDIATE
  8  USING INDEX
  9  REFRESH FAST ON DEMAND
 10  USING DEFAULT LOCAL ROLLBACK SEGMENT
 11  DISABLE QUERY REWRITE
 12  AS
 13  SELECT test2.id,
 14         test2.nom,
 15         SUM(test1.total) sum_total,
 16         COUNT(*) cnt,
 17         COUNT(test1.total) cnt_sum
 18  FROM test1,
 19       test2
 20  WHERE test1.id = test2.id(+)
 21  GROUP BY test2.id,
 22           test2.nom
 23  ORDER BY id') ;
 24  END ;
 25  /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
  2  FROM user_tune_mview
  3  WHERE task_name = 'test_1' ;

TASK_NAME        ACTION_ID SCRIPT_TYPE    STATEMENT
--------------- ---------- -------------- -----------------------------------
test_1                   1 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
                                          N "CDW"."TEST2" ADD ROWID, SEQUENCE
                                           ("ID","NOM")  INCLUDING NEW VALUES

test_1                   2 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
                                          N "CDW"."TEST1" ADD ROWID, SEQUENCE
                                           ("ID","TOTAL")  INCLUDING NEW VALU
                                          ES

test_1                   3 IMPLEMENTATION CREATE MATERIALIZED VIEW CDW.TEST_M
                                          V NOCOMPRESS
                                          LOGGING
                                          BUILD IMMEDIATE
                                          USING INDEX
                                           REFRESH FAST WITH ROWID DISABLE QU
                                          ERY REWRITE AS SELECT test2.id,
                                                 test2.nom,
                                                 SUM(test1.total) sum_total,
                                                 COUNT(*) cnt,
                                                 COUNT(test1.total) cnt_sum
                                          FROM test1,
                                               test2
                                          WHERE test1.id = test2.id(+)
                                          GROUP BY test2.id,
                                                   test2.nom
                                          ORDER BY id

test_1                   4 UNDO           DROP MATERIALIZED VIEW CDW.TEST_MV

4 rows selected.

La columna SCRIPT_TYPE nos muestra el tipo de recomendación. Deberíamos ver las columnas con el valor 'IMPLEMENTED' (la recomendación ya está implementada); pero en cambio, estamos viendo que aparecen como 'IMPLEMENTATION' (la recomendación NO está implementada).
El próximo paso es ejecutar los script recomendados...

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST2"
  2  ADD ROWID, SEQUENCE("ID","NOM")  INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST1"
  2  ADD ROWID, SEQUENCE("ID","TOTAL")  INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;

Materialized view created.

Ejecutando las recomendaciones de la tabla, pudimos solucionar rápidamente el problema que teníamos en la Vista Materializada.

Qué sucede si ejecutamos el DBMS_ADVISOR.TUNE_MVIEW nuevamente?

SQL_10gR2> DECLARE
  2  l_nombre  VARCHAR2(100) := 'test_1' ;
  3  BEGIN
  4  DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
  5  NOCOMPRESS
  6  LOGGING
  7  BUILD IMMEDIATE
  8  USING INDEX
  9  REFRESH FAST ON DEMAND
 10  USING DEFAULT LOCAL ROLLBACK SEGMENT
 11  DISABLE QUERY REWRITE
 12  AS
 13  SELECT test2.id,
 14         test2.nom,
 15         SUM(test1.total) sum_total,
 16         COUNT(*) cnt,
 17         COUNT(test1.total) cnt_sum
 18  FROM test1,
 19       test2
 20  WHERE test1.id = test2.id(+)
 21  GROUP BY test2.id,
 22           test2.nom
 23  ORDER BY id') ;
 24  END ;
 25  /
DECLARE
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1042
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 4

[TEST@TEST_10GR2 ~]$ oerr qsm 03116
03116, 00000, "The materialized view is already optimal and cannot be tuned any further"
// *Cause:   The materialized view has the capabilities that are specified in
//           the statement.
// *Action:  none

Oracle nos está diciendo que el script de creación de la Vista Materializada que nosotros ejecutamos, ya cuenta con todas las capacidades necesarias para su creación, y por lo tanto, ya se encuentra óptima y no se puede seguir tuneando.